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

131–140 of 151 posts

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

#131

Post by sgarland »

SigmundA wrote: post #107
andybak wrote: post #57

A friend of mine is a Microsoft DBA for mid-sized companies and was proclaiming how you can't do anything serious with Postgres. He said he was shocked to discover it didn't even have a query planner. Leaving mocking him to one side for a moment - is there any plausibility to his broader claim that MSSQL can handle things at a scale where Postgres would be a poor choice? My gut instinct is that this is nonsense but I…

I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take…

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL

You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it

> Interesting SQLite is the opposite tables always have clustered index whether you make one or not

AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite.

> usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection.

The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot.

This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation.

[0]: https://www.postgresql.org/docs/current/sql-cluster.html

[1]: https://www.sqlite.org/withoutrowid.html

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

#132

Post by SigmundA »

sgarland wrote: post #131
SigmundA wrote: post #107

Earlier quoted context omitted.

I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take…

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Cluste…

>You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it

That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index.

This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index.

>AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite.

The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes.

>The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot.

Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.

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

#133

Post by sgarland »

SigmundA wrote: post #132
sgarland wrote: post #131

Earlier quoted context omitted.

> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Cluste…

>You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obvio…

> That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree.

Fair point.

The speed increase is still decent [0], although if you don't need most of the tuples (and ignoring TOAST-ed columns), you could achieve similar results with a simple REINDEX.

> Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.

Agreed. I keep trying to convince Postgres fanboys that it matters, to relatively little success. I think the tide is slowly turning as "thought leaders" have begun publishing blog posts on this.

[0]: https://gist.github.com/stephanGarland/0ba0d6348ca0dedae8b4c...

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

#134

Post by davidrowley »

rst wrote: post #119
davidrowley wrote: post #98

Earlier quoted context omitted.

You have to remember that because the query has an ORDER BY, it does not mean the rows come out in a deterministic order. There'd need to be at least an ORDER BY column that provably contains unique values. Of course, you could check for that, but then I don't think that's the end of the complexity. Things like SKIP LOCKED skip over rows which we can't immediately lock. If the first time we couldn't lock the lowest o…

However, at least some ORDER BY queries will have a final sorting step before the client sees the first row. (If you're sorting on a column with an index, it may be able to produce the data in sorted order; if not, though, the final sort seems unavoidable.) Which means that the capacity to buffer up data until it's all produced is present. But it might still be awkward to make it the default.

I think the first step to making improvements in this area is to have the planner err on the side of caution more often. Today it's quite happy to join using a Nested Loop when it thinks the outer side of the join contains a single row. We have various means in the planner on how certain we might be that the 1 row thing will hold true during execution. An equality condition on a column with a unique index, is, for example, a way we could be certain of getting <= 1 row. If for example, the selectivity estimate concludes 1 row will match for some WHERE clause containing several columns with independent statistics, then the certainty level goes down. It seems silly not to swap the join order and switch to a hash join for this. Best case, we have to build a hash table to store 1 row. Probing that won't be very expensive and could even be optimised further to skip hashing if we continually probe the same bucket for N probes. The cost of additional rows over the estimated 1 row scales much more linearly than the quadratic scaling we'd have gotten with Nested Loop. I imagine a setting which controls how much risk the planner is willing to take would allow users to maintain the status quo of the current costing model. I'd imagine not many would want that, however.

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

#135

Post by davidrowley »

londons_explore wrote: post #66
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…

For many queries, even setting X=1 would probably have big benefits. If it takes far longer than expected to find the first result, it's probably time for a new plan. Implementing only the X=1 case would also dramatically simplify the design of such a feature. Limiting it to read only queries would also make everything much simpler.

I agree. The primary area where bad estimates bite us is estimating some path will return 1 row. When we join that Nested Loop looks like a great option. What could be faster to join to 1 row?! It just does not go well when 1 row turns into more than 1. We'd realise that the 1-row estimate we wrong by the time we got to row 2, so queuing 1 row would likely cover the majority of cases.

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

#136

Post by davidrowley »

TylerE wrote: post #118
davidrowley wrote: post #84

Earlier quoted context omitted.

I've considered things like this before but not had time to take it much beyond that. The idea was that the planner could run with all expensive optimisations disabled on first pass, then re-run if the estimated total cost of the plan was above some threshold with more expensive optimisations enabled. It does seem pretty silly to worry about producing a plan in a millisecond for say, an OLAP query that's going to tak…

I don't think it's sop muhch a matter of "expensive" as "unknowable". For instance, if you have a left join (and let's say it can't use an index for whatever reason) - the optimal plan will probably be different if almost every row has a matching row(s) in the join table than if only a few do.

I think the join search would remain at the same level of exhaustiveness for all levels of optimisation. I imagined we'd maybe want to disable optimisations that apply more rarely or are most expensive to discover when the planner "optimisation level" was set to lower settings. I suppose that would be things like LEFT JOIN removals and self-join removals. However, PostgreSQL does not have very many expensive optimisations that rarely help, so having an optimisation level might be more of a way of introducing more new optimisations that help fewer queries. Because we don't have a plan cache, there's been a focus on keeping the planner lean and having it not go to too much effort to optimise queries that are poorly written, for example.

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

#137

Post by davidrowley »

codeflo wrote: post #92
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…

If the sort order isn't fully determined by a query, can the query plan influence the result order? If so, what you're suggesting might be nearly impossible. The new query wouldn't be able to just skip the first N results, it would have to match each individual row against a dictionary of previously sent ones.

> If the sort order isn't fully determined by a query, can the query plan influence the result order?

Yes. When running a query, PostgreSQL won't make any effort to provide a stable order of rows beyond what's specified in the ORDER BY.

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

#138

Post by davidrowley »

darksaints wrote: post #129
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…

What I think could potentially be done is allow threshold-based alternate plans. For a pseudo example, “if subquery A returns 8 records or fewer, use this plan for subquery B, else use that plan.” It’s an explicit admission that the query planner doesn’t have enough information to make a good decision up front, but can easily be made at a later point in time while in the middle of execution.

Yes, I think so too. There is some element of this idea in the current version of PostgreSQL. However, it does not go as far as deferring the decision until execution. It's for choosing the cheapest version of a subplan once the plan has been generated for the next query level up. See fix_alternative_subplan() in setrefs.c. Likely it would be possible to expand that and have the finish plan contain the alternative and switch between them accordingly to which one is cheaper for the number of rows that previous executions have seen. Maybe tagging on some additional details about how many rows is the crossover point where one becomes cheaper than the other so that the executor can choose without having to think too hard about it would be a good idea.

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

#139

Post by davidrowley »

mike_hock wrote: post #97
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…

Alternatively, associate some confidence value with the statistics and make conservative choices when confidence is low and/or the expected difference is small. Sometimes a sequential scan is faster than index lookups for each item. But the sequential scan is a risky gamble, whereas the index lookups have robust performance characteristics. It's not always clear which choice is the conservative one, but often it is.

Yeah, this is similar to some semi-baked ideas I was talking about in https://www.postgresql.org/message-id/CAApHDvo2sMPF9m=i+YPPU...

I think it should always be clear which open would scale better for additional rows over what the estimated row count is. We should always know this because we already cost for N rows, so it's possible to cost for N+1 rows and use the additional costs to calculate how the plan choice will scale when faced with more rows than expected.

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

#140

Post by davidrowley »

Sesse__ wrote: post #110
davidrowley wrote: post #82

Earlier quoted context omitted.

Perhaps, but it might be harsh to say it was the wrong decision when it was made as partitioned tables are far more optimised than when JIT was first worked on. It seems to me, most of the people that have issues with slow JIT times are having these issues with partitioned tables and JIT is slow due to having to compile large numbers of expressions. However, maybe this is the place for me to find out that's not alway…

FWIW, I've seen planning+query times triple with JIT on, with no partitioned tables involved. It just takes forever to JIT sometimes. (This was with Postgres 13 and 14, IIRC.) Update: I checked some old IRC logs, and found a query that took 1476 ms without JIT and 8754 ms with JIT on. And that is execution time, not planning time!

Was there an indication of the number of functions compiled? There is work ongoing in this area, so feedback on this topic is very welcome on the PostgreSQL mailing lists.
Post reply on HN