3 ms·

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 a

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.

throwaway140126 7mo ago
PostgreSQL is Turing complete, so I guess they do what ever they want?
martinald 7mo ago
Anything jsonb in my experience is quickly CPU bound...
- jjice 7mo ago
  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 7mo ago
    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 7mo ago
  I've shaved off 30s of queries by transforming json columns into a string after the first CTE is done with it
deleted 7mo ago
[deleted]
wreath 7mo ago
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.
vladich 7mo ago
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.

throwaway140126 7mo ago

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

martinald 7mo ago

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

jjice 7mo ago
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 7mo ago
  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 7mo ago
I've shaved off 30s of queries by transforming json columns into a string after the first CTE is done with it

deleted 7mo ago

[deleted]

wreath 7mo ago

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.

vladich 7mo ago

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.