4 ms·

(Postgres committer and blog author here) Personally, I don't have any objection to hints. The resolution of any statistics is never going to be high enough to

(Postgres committer and blog author here) Personally, I don't have any objection to hints. The resolution of any statistics is never going to be high enough to always be accurate enough for all cases. I think it would be good to give DBAs a better way to coax the planner into making or not making a certain decision. It would also be nice if the planner was a little more risk-averse. Currently, it's happy to do things like Nested Loop join because it thinks some complex WHERE clause will only match 1 row. Nested Loop works best for that, but if there are 2 rows, then generally, any other join type is better, especially so when the inner side of the join is expensive.

tanelpoder 3y ago
One way to look at this is that the most accurate way to "estimate" how fast a certain plan would run, is to actually run it on the full dataset. But that obviously doesn't make sense, as the optimizer is expected to come up with a plan in matter of milliseconds (or less for simple queries) and you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time. I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. If the optimizer knows that some column never has NULLs or is guaranteed to be unique, all kinds of transformation & shortcuts become possible. (There are plenty of large big-vendor ERP/CRM/etc apps out there that do not use DB constraints for the sake of "portability"... not fun to work with these).
- davidrowley 3y ago
  > you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time. This is very true. PostgreSQL does not do any proactive plan caching, so it's important that the planner remains fast. It is possible to adjust the number of stats targets to control the size of the histograms and most common values list. Upping that can be useful for OLAP-type workloads. > I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Yes. Foreign key constraints are used to assist with join selectivity estimations. PG17 (when released) should be able to make more use of NOT NULL constraints to improve plans.
- Izkata 3y ago
  > I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Here's one that surprised me when I found out about it years ago, because I'd never really given it thought: There's a correlation statistic on columns for how well the values in that column match the row order on disk, which can influence a few different things. In my case a query that retrieved a ton of data with an ORDER BY was using a sort and taking like two hours to run (a data source for an ETL process) - turned out because of a really bad correlation postgres was refusing to use the index, because the random access would be even slower, so it did a table scan then sort. After figuring this out and discovering the CLUSTER command (reorders the data on disk to match an index), it did an index scan and didn't need to sort at the end, was able to start streaming results immediately, and finished the entire query in like ten minutes. Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better.
  - emmettmoore 3y ago
    > Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better. I come to the opposite conclusion. Clustering a table results in an access exclusive lock, and due to MVCC the ordering isn’t permanent. Here, you as the engineer know you’d like to use a sorted index to stream results out even if the overall query end to end is slower due to the I/O cost. In my opinion there should be a way to express this within the query.
    - Izkata 3y ago
      I did test that and like I said, it was strictly worse - went from something like a 2 hour runtime to 5+ hours. Random disk access and not being able to take advantage of the disk cache really is that bad. Streaming results doesn't mean anything if the total runtime is that much worse, it just means the overall system will take hours longer to complete. And the good version doesn't need to continuously run CLUSTER, the correlation just has to be high enough for it to be the better choice, so we settled on running it once a week - only takes like 2 minutes to run the CLUSTER. You should be thinking of it the other way around: my final 10 minute result was the ideal situation I wanted, but when circumstances were bad for it, the postgres query planner was smart enough to tell it wouldn't work and switch to the 2-hour plan instead of blindly following the original plan and taking 5 hours.
clhodapp 3y ago
It would be neat if you could at least provide expressions (that can't hit any actual tables) to compute bounds for how many rows are expected to come back from any particular row source.
hans_castorp 3y ago
I think Oracle style hints are not a good thing to have - especially because you have to change the query itself which sometimes isn't possible in a production environment. Additionally, for me they quite frequently made things worse after minor Oracle upgrades. I would prefer having "externally attached" hints for a query (e.g. identified by it's queryid) like Oracle's stored outlines.
Sesse__ 3y ago
I do wonder if one could eventually just turn off nestloops in such a case (e.g. inner side contains a seqscan), like the JOB paper recommended. Yes, it will have marginally higher estimated cost, but the upside is _much_ safer query plans when the statistics are off.
- davidrowley 3y ago
  That could be useful if there was a way to just disable non-parameterized nested loop, however enable_nestloop=0 also disables parameterized nested loops. Parameterized nested loops are useful to avoid sorting or hashing some large relation when only a small subset of that relation is likely to have a join partner. This is even more true when you consider that since PG14, Memoize exists to act as a cache between Nested Loop and its inner subnode to cache previously looked-up values. It's also important to consider that with enable_nestloop=0, when Nested Loop must be used (e.g for a CROSS JOIN) that the cost penalty that's added to reduce the chances of Nested Loop being used can dilute the costs so much that the query planner can then go on to make poor subsequent choices later in planning due to the costs for each method of implementing the subsequent operation being so relatively close to each other than the slightly cheaper one might not even be considered. See add_path() and STD_FUZZ_FACTOR. So, running enable_nestloop=0 in production is not without risk.
  - Sesse__ 3y ago
    Yeah, nestloop with a cheap inner path (e.g. a lookup into a unique index) should be just fine, so I don't think nestloops as a whole should be banned. (Also, I believe Postgres is pretty much the only place I've seen the concept of a parameterized path described; it's not talked much about in academia, although it is probably really hard to make an index-aware System R planner without it.) I wondered whether it would be possible just to add a fixed fuzz to every row estimate, say five rows. It would essentially mean you can never get this issue of a small undercount causing a plan disaster. Overestimating slightly is basically never a big issue as far as I know. (I should perhaps have considered this when I was actually making a query planner in a previous life, but there were more than enough other things to worry about :-) )

tanelpoder 3y ago

One way to look at this is that the most accurate way to "estimate" how fast a certain plan would run, is to actually run it on the full dataset. But that obviously doesn't make sense, as the optimizer is expected to come up with a plan in matter of milliseconds (or less for simple queries) and you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time. I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. If the optimizer knows that some column never has NULLs or is guaranteed to be unique, all kinds of transformation & shortcuts become possible. (There are plenty of large big-vendor ERP/CRM/etc apps out there that do not use DB constraints for the sake of "portability"... not fun to work with these).

davidrowley 3y ago
> you don't want your "optimizer stats" to be as big as the whole dataset itself. So optimizer has limited information, by design, and it has to come up with _something_ in a very short amount of time. This is very true. PostgreSQL does not do any proactive plan caching, so it's important that the planner remains fast. It is possible to adjust the number of stats targets to control the size of the histograms and most common values list. Upping that can be useful for OLAP-type workloads. > I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Yes. Foreign key constraints are used to assist with join selectivity estimations. PG17 (when released) should be able to make more use of NOT NULL constraints to improve plans.
Izkata 3y ago
> I don't know much about Postgres optimizer, but I imagine that in addition to table/column stats, is also uses structural info as its inputs, like existence of (enabled & valid) constraints for example. Here's one that surprised me when I found out about it years ago, because I'd never really given it thought: There's a correlation statistic on columns for how well the values in that column match the row order on disk, which can influence a few different things. In my case a query that retrieved a ton of data with an ORDER BY was using a sort and taking like two hours to run (a data source for an ETL process) - turned out because of a really bad correlation postgres was refusing to use the index, because the random access would be even slower, so it did a table scan then sort. After figuring this out and discovering the CLUSTER command (reorders the data on disk to match an index), it did an index scan and didn't need to sort at the end, was able to start streaming results immediately, and finished the entire query in like ten minutes. Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better.
- emmettmoore 3y ago
  > Just a nice example of where the obvious "use query hints to make it use the index" would have been the worst option, instead figuring out why postgres didn't want to use it and fixing that resulted in something much better. I come to the opposite conclusion. Clustering a table results in an access exclusive lock, and due to MVCC the ordering isn’t permanent. Here, you as the engineer know you’d like to use a sorted index to stream results out even if the overall query end to end is slower due to the I/O cost. In my opinion there should be a way to express this within the query.
  - Izkata 3y ago
    I did test that and like I said, it was strictly worse - went from something like a 2 hour runtime to 5+ hours. Random disk access and not being able to take advantage of the disk cache really is that bad. Streaming results doesn't mean anything if the total runtime is that much worse, it just means the overall system will take hours longer to complete. And the good version doesn't need to continuously run CLUSTER, the correlation just has to be high enough for it to be the better choice, so we settled on running it once a week - only takes like 2 minutes to run the CLUSTER. You should be thinking of it the other way around: my final 10 minute result was the ideal situation I wanted, but when circumstances were bad for it, the postgres query planner was smart enough to tell it wouldn't work and switch to the 2-hour plan instead of blindly following the original plan and taking 5 hours.

clhodapp 3y ago

It would be neat if you could at least provide expressions (that can't hit any actual tables) to compute bounds for how many rows are expected to come back from any particular row source.

hans_castorp 3y ago

I think Oracle style hints are not a good thing to have - especially because you have to change the query itself which sometimes isn't possible in a production environment. Additionally, for me they quite frequently made things worse after minor Oracle upgrades. I would prefer having "externally attached" hints for a query (e.g. identified by it's queryid) like Oracle's stored outlines.

Sesse__ 3y ago

I do wonder if one could eventually just turn off nestloops in such a case (e.g. inner side contains a seqscan), like the JOB paper recommended. Yes, it will have marginally higher estimated cost, but the upside is _much_ safer query plans when the statistics are off.

davidrowley 3y ago
That could be useful if there was a way to just disable non-parameterized nested loop, however enable_nestloop=0 also disables parameterized nested loops. Parameterized nested loops are useful to avoid sorting or hashing some large relation when only a small subset of that relation is likely to have a join partner. This is even more true when you consider that since PG14, Memoize exists to act as a cache between Nested Loop and its inner subnode to cache previously looked-up values. It's also important to consider that with enable_nestloop=0, when Nested Loop must be used (e.g for a CROSS JOIN) that the cost penalty that's added to reduce the chances of Nested Loop being used can dilute the costs so much that the query planner can then go on to make poor subsequent choices later in planning due to the costs for each method of implementing the subsequent operation being so relatively close to each other than the slightly cheaper one might not even be considered. See add_path() and STD_FUZZ_FACTOR. So, running enable_nestloop=0 in production is not without risk.
- Sesse__ 3y ago
  Yeah, nestloop with a cheap inner path (e.g. a lookup into a unique index) should be just fine, so I don't think nestloops as a whole should be banned. (Also, I believe Postgres is pretty much the only place I've seen the concept of a parameterized path described; it's not talked much about in academia, although it is probably really hard to make an index-aware System R planner without it.) I wondered whether it would be possible just to add a fixed fuzz to every row estimate, say five rows. It would essentially mean you can never get this issue of a small undercount causing a plan disaster. Overestimating slightly is basically never a big issue as far as I know. (I should perhaps have considered this when I was actually making a query planner in a previous life, but there were more than enough other things to worry about :-) )