3 ms·

For me, it’d be more control over the query planner, ideally down to the level of submitting my own physical query plan. I was looking at a query yesterday tha

by jitl 3y ago

For me, it’d be more control over the query planner, ideally down to the level of submitting my own physical query plan. I was looking at a query yesterday that joined 3 tables and we were unable to convince Postgres to use the optimal plan, which was to scan an index on Table1 that matched its ORDER BY order, and filter by joining against the other tables. With a CTE for Table1 that had a limit on it, Postgres would pick the optimal plan, but then may underflow the desired limit after filtering. However the query plan is optimal and it will finish in 3ms. Without the limited CTE, Postgres would run the Table2 and Table3 join in parallel with Table1, and then try to hash join like 3,0000,000 rows to produce the final tuple set. This query plan takes 40 seconds. It’s just so frustrating knowing the system has the capability to serve a query in fractions of a second but you can’t extract that performance directly. Instead you need to write some truly bizarre code you hope the query planner will like, and then live the rest of your life in fear the query planner changes its mind.

lebski88 3y ago
Yes this is completely my experience too. I spent most of yesterday battling with the query planner. Sometimes you can't express with table statistics something that you know to be true for the exact query that you're making. There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performance. What worries me is that the table stats cross a threshold at 3am and suddenly the query planner chooses something crazy. I've also wondered if an approach of trying a bunch of query plans could be fun. Get it to pick the top 10 possibilities and just run them all and record stats on which was fastest. Doubly so if it ever decides to run a seq scan where there is an index. Please. Just try the index! That said yesterday I was definitely at the point where I just wanted to express the exact query plan myself.
- jauntywundrkind 3y ago
  Very memorable post about this happening at Bluesky, about being utterly unable to trust Postgres to keep doing what it's doing. > Bluesky scaling lesson: Postgres is not a great choice if your data can be irregular. > The query planner will switch over to a new plan that consumes 100% CPU in the middle of the night whenever its table stats flip the heuristics the wrong way. > Postgres badly needs query hints like MySQL has! https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l
Izkata 3y ago
A few days late here (joys of opening multiple tabs then only getting to them when bored), here's hoping you look back and see this: The difference between using a LIMIT and not hints this is happening because of a low correlation statistic between that index and the order on disk. Postgres is avoiding random-access lookups in what looks to you like the optimal plan, because this blows away the disk cache (paging cache) and would result in a much slower overall time than you think it will, given other configuration values. The CLUSTER command will reorder the table data to match a chosen index, after which postgres will use that index with an index scan the way you want it to, even if you retrieved the whole table and have a slow disk, because now with a high correlation statistic the query planner knows it'll be working with the disk cache instead of against it. If you're on an SSD or are otherwise sure it can all be held in memory, check out the "random_page_cost" setting on https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... which may allow the index scan without having to run CLUSTER.

by jitl 3y ago

lebski88 3y ago

Yes this is completely my experience too. I spent most of yesterday battling with the query planner. Sometimes you can't express with table statistics something that you know to be true for the exact query that you're making. There are a lot of arguments about query hints etc becoming stale and the performance changing as the table grows but I'm less worried about that - it would be a gradual degradation of performance. What worries me is that the table stats cross a threshold at 3am and suddenly the query planner chooses something crazy. I've also wondered if an approach of trying a bunch of query plans could be fun. Get it to pick the top 10 possibilities and just run them all and record stats on which was fastest. Doubly so if it ever decides to run a seq scan where there is an index. Please. Just try the index! That said yesterday I was definitely at the point where I just wanted to express the exact query plan myself.

jauntywundrkind 3y ago
Very memorable post about this happening at Bluesky, about being utterly unable to trust Postgres to keep doing what it's doing. > Bluesky scaling lesson: Postgres is not a great choice if your data can be irregular. > The query planner will switch over to a new plan that consumes 100% CPU in the middle of the night whenever its table stats flip the heuristics the wrong way. > Postgres badly needs query hints like MySQL has! https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l https://bsky.app/profile/jacob.gold/post/3kn4v67wyjt2l

Izkata 3y ago

A few days late here (joys of opening multiple tabs then only getting to them when bored), here's hoping you look back and see this: The difference between using a LIMIT and not hints this is happening because of a low correlation statistic between that index and the order on disk. Postgres is avoiding random-access lookups in what looks to you like the optimal plan, because this blows away the disk cache (paging cache) and would result in a much slower overall time than you think it will, given other configuration values. The CLUSTER command will reorder the table data to match a chosen index, after which postgres will use that index with an index scan the way you want it to, even if you retrieved the whole table and have a slow disk, because now with a high correlation statistic the query planner knows it'll be working with the disk cache instead of against it. If you're on an SSD or are otherwise sure it can all be held in memory, check out the "random_page_cost" setting on https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... which may allow the index scan without having to run CLUSTER.