[the_biot]

What sort of things are people doing in their SQL queries that make them CPU bound? Admittedly I'm a meat-and-potatoes guy, but I like mine I/O bound.

Really amazed to see not one but several generic JIT frameworks though, no idea that was a thing.

[vladich]

Most databases in practice are sub-terabyte and even sub-100Gb, their active dataset is almost fully cached. For most databases I worked with, cache hit rate is above 95% and for almost all of them it's above 90%. In that situation, most queries are CPU-bound. It's completely different from typical OLAP in this sense.

[martinald]

Anything jsonb in my experience is quickly CPU bound...

[jjice]

Definitely. If you're doing regular queries with filters on jsonb columns, having the index directly on the JSON paths is really powerful. If I have a jsonb filter in the codebase at all, it probably needs an index, unless I know the result set is already very small.

[martinald]

Yeah, the other problem is I've really struggled to have postgres use multiple threads/cores on one query. Often maxes out one CPU thread while dozens go unused. I constantly have to fight loads of defaults to get this to change and even then I never feel like I can get it working quite right (probably operator error to some extent).

This compares to clickhouse where it constantly uses the whole hardware. Obviously it's easier to do that on a columnar database but it seems that postgres is actively designed to _not_ saturate multiple cores, which may be a good assumption in the past but definitely isn't a good one now IMO.

[d0100]

I've shaved off 30s of queries by transforming json columns into a string after the first CTE is done with it

[wreath]

I think reading queries that are always served from cache are CPU bound because it also involves locking the buffers etc and there is no I/O involved.

[throwaway140126]

PostgreSQL is Turing complete, so I guess they do what ever they want?