21 ms·

What's new in the Postgres 16 query planner

265 points by clairegiordano 3y ago

riku_iki 3y ago
Why wouldnt they implement hints..
- davidrowley 3y ago
  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 it to adapt to the underlying data changing. For example, indicating there's a correlation between two columns, rather than specifying a given predicate matches 10 rows.
  - riku_iki 3y ago
    > 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. they will work in prod in the way engineer is expecting. Current planner also can change its mood in unpredictable way and often generates sub-optimal plans for complex queries, because can't reason about what specific subquery will return exactly, and you learn about it when queries start work very slow in production in the middle of the night.
    - davidrowley 3y ago
      (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.
    - tanelpoder 3y ago
      Indeed, hints are super-useful (essential!) for applying quick fixes when something unexpected suddenly happens in the optimizer's magic. Or you just want to instruct/nudge the optimizer towards doing the right thing, if you know the shape of your data and optimizer can't see it or doesn't act on it correctly for some reason. The downside is that people who don't really know what exactly they want to achieve, will start applying incomplete sets of hints in random locations, based on Internet searches. And sometimes you'd even get lucky, that single index hint makes the problem go away - for a while! And few people tend to remove hints after DB version upgrades, where the optimizer magic (or your table stats) have improved. Now you're limiting optimizer's choices. That's been a problem (by now) for decades in the Oracle world with lots of legacy SQL code full of random hints where some of them aren't even valid anymore, but others still are - and limit optimizer's choices. At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I do regularly use hints in SQL tuning and troubleshooting experiments, just to verify and prove that a better plan is theoretically and physically possible - and if yes, then go from there. When the (deliberately placed) hints make the query faster, the next step is to check the hinted, faster plan's optimizer cost estimate and drill down from there: Why did optimizer think that the other plan was cheaper or why did the optimizer think that the faster plan was more expensive. Then you end up with measuring row-count misestimates, etc... But yes, plenty of people (including myself) have made legacy Oracle apps run much more efficiently and faster just by globally telling the DB to stop paying attention to all the old random hints lingering on and gathering object stats using the modern settings & defaults (doesn't always work though). [1] https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_IGNORE_HINTS.html#GUID-D62CA6D8-D0D8-4A20-93EA-EEB4B3144347 https://docs.oracle.com/en/database/oracle/oracle-database/1...
      - davidrowley 3y ago
        > At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.
  - hibikir 3y ago
    The one I'd love to tell the planner is that a table holds transactions in time, and that it should not expect that today's data is empty because it was empty 10 hours ago. It's an extremely common pattern, it makes any statistics gathering based on percentage of data changed dubious pretty quickly, and harms a whole lot of real queries, because in data like this, people care the most about the recent data. There are ways to organize data to minimize the issue, but it'd be so much nicer if we could just teach the optimizer that this is the way the data is shaped.
    - davidrowley 3y ago
      > The one I'd love to tell the planner is that a table holds transactions in time, and that it should not expect that today's data is empty because it was empty 10 hours ago. It's an extremely common pattern, it makes any statistics gathering based on percentage of data changed dubious pretty quickly, and harms a whole lot of real queries, because in data like this, people care the most about the recent data. It's not a hint, but PostgreSQL does have something that can help with cases like that. In some cases, to obtain selectivity estimates, the planner will probe a btree index to find the actual lower and/or upper bound. For this to apply, a btree index must exist and you have to be using indexes >, >=, < or <= operator. The planner will probe the index if the query is comparing the indexed column to a value that's known the planner if that value falls on the first or last histogram bucket. This can help when your statistics are slightly out of date and you're querying for some column which stores a monotonically increasing or decreasing value.
  - cryptonector 3y ago
    IMO hints need to be provided out of band, that is, not in the SQL query itself. To do this it is necessary to have a way to address every table source, in every sub-query, then one can have hints as a pile of {table source, hint}. Not that this solves the problem of hints rotting, but being able to separate them from the text of the query at least keeps the query clean, and makes is possible to have different sets of hints for different contexts and different RDBMS versions.
  - viraptor 3y ago
    >I think the danger with hints is that they might only be correct when written. Not "correct when written", but "scaling as written". That means if you force the execution that scales linearly or quadratically, that's what you get all the time. If the row number increases, you know what will happen. You can monitor that ahead of time and plan for the increase. On the other hand without the hints, you don't know when and how the plan will change without testing. At some random point postgres can decide to do something terribly stupid and at that point you get to figure out what happened and how to fix that in an emergency mode. Do you know how to adjust the right statistics? Do you need to change the indexes? Do you know how long that will take?
    - appplication 3y ago
      I had this happen for the first time to some prod jobs the other day in spark. We made a pretty normal update to a join with an additional condition, our integration tests which run local Spark succeeded. But something about it running on the cluster… it was generating a completely different query plan than it ran locally. We eventually had to rewrite the whole query to work around it because it was trying to broadcast a 3TB table and couldn’t be talked out of it.
      - davidrowley 3y ago
        There certainly are valid reasons for this. For example, adding a join condition with an OR clause. The only join operator that supports non-equi joins is Nested Loop. If you went from a Hash or Merge join to that, then you'd likely notice some performance degradation. If you have a link to anywhere you've asked for help on this, then I'd be interested to see more details.
        appplication 3y ago
        I see you’re definitely familiar with the space. I think the condition was using ‘or array_contains’ in the join. This was really the only resource I found acknowledging it. It sounds like it has do with presumption of nulls (e.g. spark can’t assume they won’t be there) but it would be great to be able to say “don’t worry spark I promise there are no nulls/if there are just disregard”) https://kb.databricks.com/sql/disable-broadcast-when-broadcastnestedloopjoin https://kb.databricks.com/sql/disable-broadcast-when-broadca... The way we go around this feels so brutish. Literally just did two separate joins and then unioned the results. The recommendation to use ‘not exists’ couldn’t be applied as array_contains must be using ‘in’ under the hood and couldn’t be changed.
  - pmontra 3y ago
    I guess that the solution to this problem can be automated. The DB or an extension to the DB or application code can run the query without hints sometimes and compare the result with the version with hints. If the hinted version is still faster, good. If it is slower, it's time to tell the DBA. Or switch to the unhinted query automatically if it's faster for a large enough number of times.
  - dagss 3y ago
    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 table scan on a declared-to-be-large table -- so that missing indices can be detected instantly, not after running in prod for some days/weeks. Google Data Store has this property and it is a joy to work with for a backend developer. What I am usually after is NOT the fastest plan, but the most consistent and robust plan across test and prod environments.
    - Semaphor 3y ago
      Oh, that sounds really cool. I like declaring expected size, but "throw on certain behaviors" would be something I’d love in MS SQL.
    - barrkel 3y ago
      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.
  - somat 3y ago
    I suspect the ideological problem with hints is that if the planner is producing a poor query, then the correct place to fix that is in the planner. While I agree with this viewpoint, The problem is that most people don't want to be a Postgress dev, To actually enable people to fix the planner it would have to be exposed as a runtime service. And unless there was a lot of diligence the planner script would quickly degrade into an unmaintainable mess(low blow: just like most schemas.)
    - williamdclt 3y ago
      I don't think anybody disagrees that the correct place to fix that is the planner, people just want an escape hatch so that when poor queries happen they can do _something_ instead of waiting for a fix to be written, a release to include it, and upgrading their database. It's a pretty reasonable ask, I think!
- sa46 3y ago
  Related discussion Why PostgreSQL doesn't have query hints https://news.ycombinator.com/item?id=2179433 https://news.ycombinator.com/item?id=2179433 (60 comments, 2011) The official stance from the Postgres wiki https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion: > We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. > Problems with existing Hint systems: Poor application code maintainability, Interference with upgrades, Encouraging bad DBA habits, Does not scale with data size I don't fault their stance, but it's frustrating when Postgres picks a stupid plan and can't be convinced to do something reasonable.
  - s08148692 3y ago
    > when Postgres picks a stupid plan and can't be convinced to do something reasonable. In my experience it can always be convinced to make a reasonable plan, but it's not always trivial. Sometimes it's just adding an index, sometimes it's entirely rewriting a query
    - CubsFan1060 3y ago
      Agreed. I've found this setting to be difficult: https://postgresqlco.nf/doc/en/param/default_statistics_target/ https://postgresqlco.nf/doc/en/param/default_statistics_targ... Too low == bad query plan. Oddly, too high == bad query plan.
    - Izkata 3y ago
      There's also "the query plan you want won't do what you expect, so I'm giving you the next best thing until you tell me the right way to store the data": https://news.ycombinator.com/item?id=39311536 https://news.ycombinator.com/item?id=39311536
wiradikusuma 3y ago
I use this tool to visualize my queries: https://explain.dalibo.com/ https://explain.dalibo.com/ (there's also https://www.pgexplain.dev/ https://www.pgexplain.dev/, last time the output was less nice, but now both look the same)
- klysm 3y ago
  The tool is great and I use it, but I don't really have a deep enough understanding to know how to fix issues in my approach from what looks bad in the plan.
  - davidrowley 3y ago
    It's pretty hard to tell if a plan is good or bad from EXPLAIN without using the ANALYZE option. With EXPLAIN ANALYZE you can see where the time is being spent, so can you get an idea of which part of the plan you should focus on. To know if it's a bad plan, it does take quite a bit of knowledge as you need to know what alternative plans could have been used instead. It takes quite a bit of time to learn that stuff. You need to know what PostgreSQL is capable of. Some computer science knowledge helps here as you'll know, for example, when a hash join is a good way to join a table vs a nested loop. As for fixing plan you've identified as bad, that also takes quite a bit of experience. If you understand the EXPLAIN ANALYZE output well, that's a good start. Looking for places where the estimated rows differ from the actual can be key. Having an understanding of how Postgres performed the row estimations helps. That's not something that comes easily without looking at the source code, unfortunately. Understanding tools that you have to change the plan is useful. Perhaps that's CREATE STATISTICS, or adjusting the stats targets on existing single column stats. Or maybe creating a new index. Having a test environment that allows you to experiment is very useful too.
    - cnity 3y ago
      To be honest, you can get quite a lot of mileage out of EXPLAIN by focusing on which indices the plan selected (or did not select). For this reason it's really worth understanding your indices and what value they add (or remove!).
fabian2k 3y ago
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 a lot of other tables via joins. It runs in a few milliseconds without the JIT, but the JIT spent 1-1.5 seconds doing its thing on top of that and makes it incredibly slow for tiny amounts of data. I know now to just disable the JIT, but this feature can give a pretty terrible impression to users that don't know enough yet to figure out why it's slow. I like Postgres a lot, but enabling the JIT just seems far too dangerous as a default setting to me.
- aidos 3y ago
  We hit a curios bug recently on production with the JIT. I had apt updated a couple of packages and then all of a sudden a bigger query we run every 5 minutes was failing. Or rather, Postgres just started silently hanging up the connection mid query execution with even putting anything in the logs. Took me a while of running manually in explain to see that the variations of the query that ended up using the JIT broke while those that didn’t were ok. Disabled the JIT and everything was ok again.
- czl 3y ago
  Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?
  - fabian2k 3y ago
    As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.
    - trollied 3y ago
      Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).
jhoechtl 3y ago
Why is this released by citusdata instead on postgresql.org? Is this a paid feature only or an open source addition?
- Sesse__ 3y ago
  Because the poster (who also wrote some of the optimizations in question) works for Citus Data.
  - davidrowley 3y ago
    Just to clarify. I'm the author of the blog. I work for Microsoft in the Postgres open-source team. All the work mentioned in the blog is in PostgreSQL 16, which is open-source.
    - deleted 3y ago
      [deleted]
    - jhoechtl 3y ago
      Answers it nicely, thank you!
    - evntdrvn 3y ago
      thanks for the work that your team does!
  - jhoechtl 3y ago
    There was no subtone in my question, I just wanted to know if this is a paid only feature.
twic 3y ago
I'd be interested to know how often these changes have an effect in real queries. The "Use Limit instead of Unique to implement DISTINCT, when possible" change in particular feels like it would only apply to very silly queries. Do the PostgreSQL developers have any source of information about this?
- joachimma 3y ago
  Where I used to work we allowed duplicate email addresses in the user table for legacy reasons, but we did not want any new entered in the db, so we ran a "select distinct email from users where email = ?" query before creation of new users. I don't think we had more than a 100 rows with the same email though. Most of the duplicates were test users which could have been removed, but I digress.
- Sesse__ 3y ago
  The problem is that ORMs have a habit of making very silly queries, and developers insist they cannot write SQL to fix that, because it is somehow impure :-) I doubt this is a very _common_ issue, but I'm not surprised if it shows up every now and then.
  - 946789987649 3y ago
    > developers insist they cannot write SQL to fix that, because it is somehow impure :-) Because then they lose many of the benefits of why they used an ORM in the first place. Though I am a big fanboy of JOOQ for exactly this reason.
- davidrowley 3y ago
  (Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.
  - 3y ago
Linda231 3y ago
[flagged]
andybak 3y ago
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'm not a DBA by a long stretch.
- fabian2k 3y ago
  Postgres has a query planner, I mean this entire post is about improvements to it. So I think there either was some miscommunication or your friend doesn't know anything about Postgres. There are very large Postges databases that seem to work fine, so Postgres can certainly scale. But SQL Server also has some features that Postgres doesn't, and if those are important for you it might work better for your use case. They are in the end different databases with different strengths and weaknesses.
- danpalmer 3y ago
  Yes there is. If what you need is a database that will do pretty much anything well enough, then MSSQL and Oracle are going to manage it. They solve this by throwing money and hardware (more money) at the problem until it works. There's some clever stuff happening in there of course, but fundamentally they've just had much more engineering work over a long time. They can scale out more than Postgres can reasonably do. That said, Postgres is catching up, and arguably MySQL/MariaDB has always had a good story here. Scale-out options are improving all the time. The landscape has also changed in other ways too, now you can easily have a multi-terabyte Postgres cluster on a small number of machines serving large traffic volumes, and then put your "big data" into a more specialist database. The old world of shoving everything on MSSQL/Oracle may be a bit old-school.
- PartiallyTyped 3y ago
  AWS' Aurora seems to be handling things pretty well tbh and is meant as a drop-in replacement for Postgresql and MySQL.
londons_explore 3y ago
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 rather than 1ms). You will never have 100% accurate table stats - there is always some odd joint distribution you will not capture. So instead, allow the query to start, 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. If the updated plan shows it's quicker to discard current results and restart with a new approach, do that. Unfortunately, postgres does streaming queries (ie. the first results of a query are sent back to the client before the query is done), which means that significant infrastructural changes would be needed to allow a midway change of plan. Any new plan would need to keep track of which results had already been sent to the client so they aren't resent. Postgres also allows a client to, midway through a query, request that the query reverse direction and re-return previous results in reverse order. That adds a lot of complexity.
- davidrowley 3y ago
  (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 easier to do something like queue up X tuples and just start sending those out when the queue is full. When the queue is full, we can say that it's too late to replan and we're locked into the current plan. People can set X to what they want to increase the time that the plan can change at the expense of using more memory and higher latency to get the first tuple.
  - londons_explore 3y ago
    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.
Eransbens 3y ago
[flagged]
devit 3y ago
I think it would be really nice to have a "strict mode" (for app testing), where PostgreSQL returns an error if an index would improve the query asymptotically and it doesn't exist (only based on the query itself, not statistics). And a "CREATE INDICES FOR <sql>" command to create the indices (for app upgrades), plus an automatic index creation mode (for interactive and development use). In general, the system should be architected so that asymptotically suboptimal execution never happens.
Diggsey 3y ago
But when is it going to be able to use indexes to speed up `IS NOT DISTINCT FROM` queries? ;)

Why wouldnt they implement hints..

davidrowley 3y ago
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 it to adapt to the underlying data changing. For example, indicating there's a correlation between two columns, rather than specifying a given predicate matches 10 rows.
- riku_iki 3y ago
  > 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. they will work in prod in the way engineer is expecting. Current planner also can change its mood in unpredictable way and often generates sub-optimal plans for complex queries, because can't reason about what specific subquery will return exactly, and you learn about it when queries start work very slow in production in the middle of the night.
  - davidrowley 3y ago
    (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.
  - tanelpoder 3y ago
    Indeed, hints are super-useful (essential!) for applying quick fixes when something unexpected suddenly happens in the optimizer's magic. Or you just want to instruct/nudge the optimizer towards doing the right thing, if you know the shape of your data and optimizer can't see it or doesn't act on it correctly for some reason. The downside is that people who don't really know what exactly they want to achieve, will start applying incomplete sets of hints in random locations, based on Internet searches. And sometimes you'd even get lucky, that single index hint makes the problem go away - for a while! And few people tend to remove hints after DB version upgrades, where the optimizer magic (or your table stats) have improved. Now you're limiting optimizer's choices. That's been a problem (by now) for decades in the Oracle world with lots of legacy SQL code full of random hints where some of them aren't even valid anymore, but others still are - and limit optimizer's choices. At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I do regularly use hints in SQL tuning and troubleshooting experiments, just to verify and prove that a better plan is theoretically and physically possible - and if yes, then go from there. When the (deliberately placed) hints make the query faster, the next step is to check the hinted, faster plan's optimizer cost estimate and drill down from there: Why did optimizer think that the other plan was cheaper or why did the optimizer think that the faster plan was more expensive. Then you end up with measuring row-count misestimates, etc... But yes, plenty of people (including myself) have made legacy Oracle apps run much more efficiently and faster just by globally telling the DB to stop paying attention to all the old random hints lingering on and gathering object stats using the modern settings & defaults (doesn't always work though). [1] https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/OPTIMIZER_IGNORE_HINTS.html#GUID-D62CA6D8-D0D8-4A20-93EA-EEB4B3144347 https://docs.oracle.com/en/database/oracle/oracle-database/1...
    - davidrowley 3y ago
      > At least Oracle folks had enough at some point and introduced an "optimizer_ignore_hints" parameter [1], so all legacy hints that were added 20 years ago just get ignored - and the modern optimizer does a much better job getting things right. I think the general attitude in the Postgres community is been from a purist point of view. When you have a codebase around 40 years old, you do have to think carefully about what you put into it, as it might not be that easy to take it out again. However, yes, I do think hints would be useful for Postgres, providing they're done well. It would be good to at least have something to assist with selectivity estimations. Those are at least not directly forcing the planner into a single choice. New planner/executor smarts, such as something like Memoize added in PG14 could still be considered after upgrading an older pre-PG14 application with such hints, but perhaps not if the hint told the planner that it must nested loop join these two tables.
- hibikir 3y ago
  The one I'd love to tell the planner is that a table holds transactions in time, and that it should not expect that today's data is empty because it was empty 10 hours ago. It's an extremely common pattern, it makes any statistics gathering based on percentage of data changed dubious pretty quickly, and harms a whole lot of real queries, because in data like this, people care the most about the recent data. There are ways to organize data to minimize the issue, but it'd be so much nicer if we could just teach the optimizer that this is the way the data is shaped.
  - davidrowley 3y ago
    > The one I'd love to tell the planner is that a table holds transactions in time, and that it should not expect that today's data is empty because it was empty 10 hours ago. It's an extremely common pattern, it makes any statistics gathering based on percentage of data changed dubious pretty quickly, and harms a whole lot of real queries, because in data like this, people care the most about the recent data. It's not a hint, but PostgreSQL does have something that can help with cases like that. In some cases, to obtain selectivity estimates, the planner will probe a btree index to find the actual lower and/or upper bound. For this to apply, a btree index must exist and you have to be using indexes >, >=, < or <= operator. The planner will probe the index if the query is comparing the indexed column to a value that's known the planner if that value falls on the first or last histogram bucket. This can help when your statistics are slightly out of date and you're querying for some column which stores a monotonically increasing or decreasing value.
- cryptonector 3y ago
  IMO hints need to be provided out of band, that is, not in the SQL query itself. To do this it is necessary to have a way to address every table source, in every sub-query, then one can have hints as a pile of {table source, hint}. Not that this solves the problem of hints rotting, but being able to separate them from the text of the query at least keeps the query clean, and makes is possible to have different sets of hints for different contexts and different RDBMS versions.
- viraptor 3y ago
  >I think the danger with hints is that they might only be correct when written. Not "correct when written", but "scaling as written". That means if you force the execution that scales linearly or quadratically, that's what you get all the time. If the row number increases, you know what will happen. You can monitor that ahead of time and plan for the increase. On the other hand without the hints, you don't know when and how the plan will change without testing. At some random point postgres can decide to do something terribly stupid and at that point you get to figure out what happened and how to fix that in an emergency mode. Do you know how to adjust the right statistics? Do you need to change the indexes? Do you know how long that will take?
  - appplication 3y ago
    I had this happen for the first time to some prod jobs the other day in spark. We made a pretty normal update to a join with an additional condition, our integration tests which run local Spark succeeded. But something about it running on the cluster… it was generating a completely different query plan than it ran locally. We eventually had to rewrite the whole query to work around it because it was trying to broadcast a 3TB table and couldn’t be talked out of it.
    - davidrowley 3y ago
      There certainly are valid reasons for this. For example, adding a join condition with an OR clause. The only join operator that supports non-equi joins is Nested Loop. If you went from a Hash or Merge join to that, then you'd likely notice some performance degradation. If you have a link to anywhere you've asked for help on this, then I'd be interested to see more details.
      - appplication 3y ago
        I see you’re definitely familiar with the space. I think the condition was using ‘or array_contains’ in the join. This was really the only resource I found acknowledging it. It sounds like it has do with presumption of nulls (e.g. spark can’t assume they won’t be there) but it would be great to be able to say “don’t worry spark I promise there are no nulls/if there are just disregard”) https://kb.databricks.com/sql/disable-broadcast-when-broadcastnestedloopjoin https://kb.databricks.com/sql/disable-broadcast-when-broadca... The way we go around this feels so brutish. Literally just did two separate joins and then unioned the results. The recommendation to use ‘not exists’ couldn’t be applied as array_contains must be using ‘in’ under the hood and couldn’t be changed.
- pmontra 3y ago
  I guess that the solution to this problem can be automated. The DB or an extension to the DB or application code can run the query without hints sometimes and compare the result with the version with hints. If the hinted version is still faster, good. If it is slower, it's time to tell the DBA. Or switch to the unhinted query automatically if it's faster for a large enough number of times.
- dagss 3y ago
  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 table scan on a declared-to-be-large table -- so that missing indices can be detected instantly, not after running in prod for some days/weeks. Google Data Store has this property and it is a joy to work with for a backend developer. What I am usually after is NOT the fastest plan, but the most consistent and robust plan across test and prod environments.
  - Semaphor 3y ago
    Oh, that sounds really cool. I like declaring expected size, but "throw on certain behaviors" would be something I’d love in MS SQL.
  - barrkel 3y ago
    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.
- somat 3y ago
  I suspect the ideological problem with hints is that if the planner is producing a poor query, then the correct place to fix that is in the planner. While I agree with this viewpoint, The problem is that most people don't want to be a Postgress dev, To actually enable people to fix the planner it would have to be exposed as a runtime service. And unless there was a lot of diligence the planner script would quickly degrade into an unmaintainable mess(low blow: just like most schemas.)
  - williamdclt 3y ago
    I don't think anybody disagrees that the correct place to fix that is the planner, people just want an escape hatch so that when poor queries happen they can do _something_ instead of waiting for a fix to be written, a release to include it, and upgrading their database. It's a pretty reasonable ask, I think!
sa46 3y ago
Related discussion Why PostgreSQL doesn't have query hints https://news.ycombinator.com/item?id=2179433 https://news.ycombinator.com/item?id=2179433 (60 comments, 2011) The official stance from the Postgres wiki https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion: > We are not interested in implementing hints in the exact ways they are commonly implemented on other databases. > Problems with existing Hint systems: Poor application code maintainability, Interference with upgrades, Encouraging bad DBA habits, Does not scale with data size I don't fault their stance, but it's frustrating when Postgres picks a stupid plan and can't be convinced to do something reasonable.
- s08148692 3y ago
  > when Postgres picks a stupid plan and can't be convinced to do something reasonable. In my experience it can always be convinced to make a reasonable plan, but it's not always trivial. Sometimes it's just adding an index, sometimes it's entirely rewriting a query
  - CubsFan1060 3y ago
    Agreed. I've found this setting to be difficult: https://postgresqlco.nf/doc/en/param/default_statistics_target/ https://postgresqlco.nf/doc/en/param/default_statistics_targ... Too low == bad query plan. Oddly, too high == bad query plan.
  - Izkata 3y ago
    There's also "the query plan you want won't do what you expect, so I'm giving you the next best thing until you tell me the right way to store the data": https://news.ycombinator.com/item?id=39311536 https://news.ycombinator.com/item?id=39311536

I use this tool to visualize my queries: https://explain.dalibo.com/ https://explain.dalibo.com/ (there's also https://www.pgexplain.dev/ https://www.pgexplain.dev/, last time the output was less nice, but now both look the same)

klysm 3y ago
The tool is great and I use it, but I don't really have a deep enough understanding to know how to fix issues in my approach from what looks bad in the plan.
- davidrowley 3y ago
  It's pretty hard to tell if a plan is good or bad from EXPLAIN without using the ANALYZE option. With EXPLAIN ANALYZE you can see where the time is being spent, so can you get an idea of which part of the plan you should focus on. To know if it's a bad plan, it does take quite a bit of knowledge as you need to know what alternative plans could have been used instead. It takes quite a bit of time to learn that stuff. You need to know what PostgreSQL is capable of. Some computer science knowledge helps here as you'll know, for example, when a hash join is a good way to join a table vs a nested loop. As for fixing plan you've identified as bad, that also takes quite a bit of experience. If you understand the EXPLAIN ANALYZE output well, that's a good start. Looking for places where the estimated rows differ from the actual can be key. Having an understanding of how Postgres performed the row estimations helps. That's not something that comes easily without looking at the source code, unfortunately. Understanding tools that you have to change the plan is useful. Perhaps that's CREATE STATISTICS, or adjusting the stats targets on existing single column stats. Or maybe creating a new index. Having a test environment that allows you to experiment is very useful too.
  - cnity 3y ago
    To be honest, you can get quite a lot of mileage out of EXPLAIN by focusing on which indices the plan selected (or did not select). For this reason it's really worth understanding your indices and what value they add (or remove!).

fabian2k 3y ago

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 a lot of other tables via joins. It runs in a few milliseconds without the JIT, but the JIT spent 1-1.5 seconds doing its thing on top of that and makes it incredibly slow for tiny amounts of data. I know now to just disable the JIT, but this feature can give a pretty terrible impression to users that don't know enough yet to figure out why it's slow. I like Postgres a lot, but enabling the JIT just seems far too dangerous as a default setting to me.

aidos 3y ago
We hit a curios bug recently on production with the JIT. I had apt updated a couple of packages and then all of a sudden a bigger query we run every 5 minutes was failing. Or rather, Postgres just started silently hanging up the connection mid query execution with even putting anything in the logs. Took me a while of running manually in explain to see that the variations of the query that ended up using the JIT broke while those that didn’t were ok. Disabled the JIT and everything was ok again.
czl 3y ago
Did you try using prepared statement so the compilation is done once and compiled results are reused each time that query is run?
- fabian2k 3y ago
  As far as I understand prepared statements don't help here as the JIT output is not saved but generated for each execution. In this case I'm also using an ORM (EF Core) which doesn't expose the ability to prepare statements.
  - trollied 3y ago
    Most optimisers cache the execution plan based on a hash of the query, hence reuse when using prepared statements vs not. Oracle has an option to detect literals (CURSOR_SHARING) and essentially replace them with binds internally, in order to increase performance and stop the query pool getting filled up with the same statement(s).

jhoechtl 3y ago

Why is this released by citusdata instead on postgresql.org? Is this a paid feature only or an open source addition?

Sesse__ 3y ago
Because the poster (who also wrote some of the optimizations in question) works for Citus Data.
- davidrowley 3y ago
  Just to clarify. I'm the author of the blog. I work for Microsoft in the Postgres open-source team. All the work mentioned in the blog is in PostgreSQL 16, which is open-source.
  - deleted 3y ago
    [deleted]
  - jhoechtl 3y ago
    Answers it nicely, thank you!
  - evntdrvn 3y ago
    thanks for the work that your team does!
- jhoechtl 3y ago
  There was no subtone in my question, I just wanted to know if this is a paid only feature.

twic 3y ago

I'd be interested to know how often these changes have an effect in real queries. The "Use Limit instead of Unique to implement DISTINCT, when possible" change in particular feels like it would only apply to very silly queries. Do the PostgreSQL developers have any source of information about this?

joachimma 3y ago
Where I used to work we allowed duplicate email addresses in the user table for legacy reasons, but we did not want any new entered in the db, so we ran a "select distinct email from users where email = ?" query before creation of new users. I don't think we had more than a 100 rows with the same email though. Most of the duplicates were test users which could have been removed, but I digress.
Sesse__ 3y ago
The problem is that ORMs have a habit of making very silly queries, and developers insist they cannot write SQL to fix that, because it is somehow impure :-) I doubt this is a very _common_ issue, but I'm not surprised if it shows up every now and then.
- 946789987649 3y ago
  > developers insist they cannot write SQL to fix that, because it is somehow impure :-) Because then they lose many of the benefits of why they used an ORM in the first place. Though I am a big fanboy of JOOQ for exactly this reason.
davidrowley 3y ago
(Author of the blog and that feature here) This one did crop up on the pgsql-hackers mailing list. I very much agree that it's unlikely to apply very often, but the good thing was that detecting when it's possible is as simple as checking if a pointer is NULL. So, it's very simple to detect, most likely does not apply very often, but can provide significant performance increases when it can be applied.
- 3y ago

Linda231 3y ago

[flagged]

andybak 3y ago

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'm not a DBA by a long stretch.

fabian2k 3y ago
Postgres has a query planner, I mean this entire post is about improvements to it. So I think there either was some miscommunication or your friend doesn't know anything about Postgres. There are very large Postges databases that seem to work fine, so Postgres can certainly scale. But SQL Server also has some features that Postgres doesn't, and if those are important for you it might work better for your use case. They are in the end different databases with different strengths and weaknesses.
danpalmer 3y ago
Yes there is. If what you need is a database that will do pretty much anything well enough, then MSSQL and Oracle are going to manage it. They solve this by throwing money and hardware (more money) at the problem until it works. There's some clever stuff happening in there of course, but fundamentally they've just had much more engineering work over a long time. They can scale out more than Postgres can reasonably do. That said, Postgres is catching up, and arguably MySQL/MariaDB has always had a good story here. Scale-out options are improving all the time. The landscape has also changed in other ways too, now you can easily have a multi-terabyte Postgres cluster on a small number of machines serving large traffic volumes, and then put your "big data" into a more specialist database. The old world of shoving everything on MSSQL/Oracle may be a bit old-school.
PartiallyTyped 3y ago
AWS' Aurora seems to be handling things pretty well tbh and is meant as a drop-in replacement for Postgresql and MySQL.

londons_explore 3y ago

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 rather than 1ms). You will never have 100% accurate table stats - there is always some odd joint distribution you will not capture. So instead, allow the query to start, 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. If the updated plan shows it's quicker to discard current results and restart with a new approach, do that. Unfortunately, postgres does streaming queries (ie. the first results of a query are sent back to the client before the query is done), which means that significant infrastructural changes would be needed to allow a midway change of plan. Any new plan would need to keep track of which results had already been sent to the client so they aren't resent. Postgres also allows a client to, midway through a query, request that the query reverse direction and re-return previous results in reverse order. That adds a lot of complexity.

davidrowley 3y ago
(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 easier to do something like queue up X tuples and just start sending those out when the queue is full. When the queue is full, we can say that it's too late to replan and we're locked into the current plan. People can set X to what they want to increase the time that the plan can change at the expense of using more memory and higher latency to get the first tuple.
- londons_explore 3y ago
  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.

Eransbens 3y ago

[flagged]

devit 3y ago

I think it would be really nice to have a "strict mode" (for app testing), where PostgreSQL returns an error if an index would improve the query asymptotically and it doesn't exist (only based on the query itself, not statistics). And a "CREATE INDICES FOR <sql>" command to create the indices (for app upgrades), plus an automatic index creation mode (for interactive and development use). In general, the system should be architected so that asymptotically suboptimal execution never happens.

Diggsey 3y ago

But when is it going to be able to use indexes to speed up `IS NOT DISTINCT FROM` queries? ;)