Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

What's new in the Postgres 16 query planner

citusdata.com

Post reply on HN
Print view View original

71–80 of 151 posts

Re: What's new in the Postgres 16 query planner

#71

Post by londons_explore »

fuy wrote: post #68
davidrowley wrote: post #64

Earlier quoted context omitted.

(blog author and Postgres committer here) I personally think this would be nice to have. However, I think the part about sending of tuples to the client is even more tricky than you've implied above. It's worse because a new plan does not even guarantee that the same tuples are returned. e.g. if you wrote: SELECT * FROM table LIMIT 10, there's no ORDER BY so which tuples are returned is non-deterministic. It may be e…

Or it could work in a way that the Planner has access to data about previous runs of each query, and it can use this data to change plans that were proven bad during execution. This way, the first execution would be slow, but Planner could self-learn and better next time. SQL Server has a bunch of similar features in its query optimizer https://learn.microsoft.com/en-us/sql/relational-databases/p... . I'm not sure Po…

Also, many queries might be so slow they never complete, and therefore never populate the cache. (think those queries run by a data scientist with 50 JOIN's)

Re: What's new in the Postgres 16 query planner

#72

Post by Someone »

londons_explore wrote: post #61

I really wish the postgres query planner would gain the ability to replan a query mid way through execution... Frequently the most pathological queries (ie. the dreadfully slow ones) are because the query planner didn't have some knowledge required of the data distribution and couldn't accurately estimate the cost of some approach to planning the query. This can easily have a 1000x impact on execution time (ie. 1s ra…

> and if progress isn't as fast as the planner expects, feed current progress info back to the planner (pages scanned, tuples matching), and replan with that new data.

That would require keeping track of those stats in every query execution. That has a price that may or may not be worth it.

And yes, you could make that behavior an option, but, for better or for worse, PostgreSQL tends to be opposed to having queries indicate how it should do its work.

Re: What's new in the Postgres 16 query planner

#73

Post by dur-randir »

fabian2k wrote: post #31

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what you want ;-). One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. I've seen this for a rather typical ORM-generated query that is pretty straightforward, but pulls in…

I disabled JIT after it became default for our installation (~1TB data). Nice try, useful sometimes, but as a default? No, thanks.

Re: What's new in the Postgres 16 query planner

#74

Post by sega_sai »

londons_explore wrote: post #61

I really wish the postgres query planner would gain the ability to replan a query mid way through execution... Frequently the most pathological queries (ie. the dreadfully slow ones) are because the query planner didn't have some knowledge required of the data distribution and couldn't accurately estimate the cost of some approach to planning the query. This can easily have a 1000x impact on execution time (ie. 1s ra…

I think another way to think about it is to allow 'long planning' queries. I.e. where it is allowed to spend a second, or maybe a few seconds choosing the best plan. That may involve collecting more statistics or running a query for a little bit.

Re: What's new in the Postgres 16 query planner

#75

Post by dur-randir »

davidrowley wrote: post #55
fabian2k wrote: post #31

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what you want ;-). One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. I've seen this for a rather typical ORM-generated query that is pretty straightforward, but pulls in…

> One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. (Author of the blog here and Postgres committer). I very much agree that the code to decide if JIT should be used or not needs work. For PG16, it only takes into account the estimated total cost of the plan and does not take into account how many expressions need to be com…

If it's not ready for everyone, probably it shouldn't have been made a default, don't you think?

Re: What's new in the Postgres 16 query planner

#76

Post by dur-randir »

czl wrote: post #37
fabian2k wrote: post #31

Query planner improvements are always welcome, it's a very important part of the DB. Though of course most of the time you notice it is when it's not doing what you want ;-). One part of this I found rather frustrating is the JIT in newer Postgres versions. The heuristics on when to use appear not robust at all to me. I've seen this for a rather typical ORM-generated query that is pretty straightforward, but pulls in…

Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?

There's a separate can of worms with prepared statements. Two main are:

- parameters are opaque to planner, so it prefers (or is even forced to?) to choose generic vs specific plans

- it doesn't play nice with pg_bouncer in transaction mode

Re: What's new in the Postgres 16 query planner

#77

Post by barrkel »

dagss wrote: post #33
davidrowley wrote: post #3

Earlier quoted context omitted.

There is a pg_hint_plan extension. I think the danger with hints is that they might only be correct when written. If the table sizes or data skew changes, they might make things worse. I don't have a link to hand, but last time I recall a discussion on hints there was no general objection to them, providing the implementation could be done in a way that didn't force the planner's hand too strongly and still allowed i…

I feel the best abstraction for hints would be to declare on tables how large you expect them to be -- and even throw errors if query plans with a good scaling cannot be found. Say I could declare "assume this table will grow very large", "assume this table will be a small enum table". And then it would use that information instead of actual table size to guide planning AND throw an error for any query doing a full t…

Table row count is a small part of it, what matters is cardinality and fanout from joins after predicates have been pushed down as far as they can. (Assuming there are sane indexes.)

If the database can't see that your predicates will restrict the set of rows at a certain point in the join graph, it is likely to decide to join too much too early with huge table scans.

Bad join order and join strategy is at the heart of most bad plans once you already have indexes in place that cover the expected joins and lookups.

Re: What's new in the Postgres 16 query planner

#78

Post by derhuerst »

Defman wrote: post #20
uudecoded wrote: post #18

Earlier quoted context omitted.

I read your profile and see that you are a CTO of a fintech. Given that, by what method do you navigate that tool's [explain.dalibo.com] assertion of "It is recommended not to send any critical or sensitive information"? Is there an explain plan sanitizer that is helpful for this situation?

You can download the whole visualizer as a simple html file and use it this way. No need to obfuscate or sanitize anything at all. https://github.com/dalibo/pev2

I do the same, but because it became tedious to insert the query & query plan manually, I wrote pev2-cli [1].

[1] https://github.com/derhuerst/pev2-cli

Re: What's new in the Postgres 16 query planner

#79

Post by [deleted] »

londons_explore wrote: post #61

I really wish the postgres query planner would gain the ability to replan a query mid way through execution... Frequently the most pathological queries (ie. the dreadfully slow ones) are because the query planner didn't have some knowledge required of the data distribution and couldn't accurately estimate the cost of some approach to planning the query. This can easily have a 1000x impact on execution time (ie. 1s ra…

[deleted]

Re: What's new in the Postgres 16 query planner

#80

Post by indymike »

londons_explore wrote: post #71
fuy wrote: post #68

Earlier quoted context omitted.

Or it could work in a way that the Planner has access to data about previous runs of each query, and it can use this data to change plans that were proven bad during execution. This way, the first execution would be slow, but Planner could self-learn and better next time. SQL Server has a bunch of similar features in its query optimizer https://learn.microsoft.com/en-us/sql/relational-databases/p... . I'm not sure Po…

Also, many queries might be so slow they never complete, and therefore never populate the cache. (think those queries run by a data scientist with 50 JOIN's)

> 50 JOINS

And no indexes.

Post reply on HN