12 ms·
How Postgres chooses which index to use for a query
- qeternity 5y agoPostgres really really needs index hints.
- gleenn 5y agoIs there a good reason they haven't added them? Difficult to implement?
- ako 5y agoMany databases are also used for executing dynamically generated SQL queries: ORM based, or BI/ETL generated queries. If you rely on hints too much, having good statistics is often neglected, and this will impact any query that cannot be provided with hints.
- gfody 5y agoseems to be a matter of principle with the (some?) core devs position that it adds complexity and shouldn't be necessary. I remember the big debate long ago that resulted in postgres rejecting the standard sql merge command in favor of a proprietary syntax for upserting despite the common practice of using merge w/hints for doing upserts in most other engines - iirc it boiled down to postgres devs do not want hints, ever.
- SigmundA 5y agoOf course they are now adding merge anyway: https://www.depesz.com/2022/03/31/waiting-for-postgresql-15-add-support-for-merge-sql-command/ https://www.depesz.com/2022/03/31/waiting-for-postgresql-15-...
- uhoh-itsmaciek 5y agoThey're not really the same thing: https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDGYAcTZg0xA8-1_FCVo5Xm7hrEL34kw@mail.gmail.com https://www.postgresql.org/message-id/CAM3SWZRP0c3g6+aJ=YYDG...
- SigmundA 5y agoDid you read the article I posted, its being added to PG 15: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7103ebb7aae8ab8076b7e85f335ceb8fe799097c https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit...
- petergeoghegan 5y agoDid you read the text that uhoh-itsmaciek posted?
- nitrogen 5y agoFrom 2014. The MERGE discussion is from 2022.
- uhoh-itsmaciek 5y agoI don't see anything in the 2022 blog post that contradicts the 2014 mailing list post. These are different features with different trade-offs and somewhat overlapping use cases.
- SigmundA 5y agoOther systems like SQL server solve this with hints (HOLDLOCK) which is also discussed here that PG really needs hints. So PG doesn't like hints and instead makes non standard syntax for upsert instead of standard merge syntax with a hint. Now PG is adding merge anyway and probably could have just extended it with non standard syntax to get an atomic upsert instead it has two different approaches. IMO it probably would have been better to go with the SQL standard MERGE with a non-standard hint syntax so its more readable between systems instead of adding non standard syntax then adding the standard much later. Not sure if there is any movement on adding the upsert approach to the SQL standard instead.
- petergeoghegan 5y ago> a proprietary syntax for upserting despite the common practice of using merge w/hints for doing upserts in most other engines I was the primary author of the feature. That decision wasn't related to hints at all. MERGE just doesn't address the problem that I wanted to address. I think that you must be referring to Oracle's ignore_row_on_dupkey_index. Is that really in widespread use? While it is technically a hint, it is unlike any other hint it at least one important way: it changes the semantics of the query!
- gfody 5y ago> ignore_row_on_dupkey_index I was thinking of the hints you used to have to use with a merge statement in order to get an atomic upsert - holdlock in sql server and lock table in oracle. My recollection of the terribly old newsgroup thread where it was (presumably you) said merge doesn't perform atomic upserts without these hints and since people were primarily wanting merge to perform atomic upserts and postgres doesn't support hints it made more sense to just make something for upserts hence the on conflict update stuff. Do I have it wrong?
- petergeoghegan 5y agoThe Oracle docs introduce MERGE as follows: "Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view. You can specify conditions to determine whether to update or insert into the target table or view. This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements." MERGE doesn't claim to work as an atomic statement, and doesn't work as an atomic statement in practice. In other words you can get a unique violation error where a "true upsert" would update the row instead. You can work around this "limitation" in MERGE by retrying the statement until it succeeds, but at that point you might as well use a regular INSERT instead. The syntax for INSERT .. ON CONFLICT constrains the problem in certain ways, which is quite deliberate. It's a pretty faithful representation of what's really going on. Hiding that creates a lot of subtle but real problems.
- 5y ago
- luhn 5y ago> Poor application code maintainability: hints in queries require massive refactoring. > Interference with upgrades: today's helpful hints become anti-performance after an upgrade. > Encouraging bad DBA habits slap a hint on instead of figuring out the real issue. Does not scale with data size: the hint that's right when a table is small is likely to be wrong when it gets larger. > Failure to actually improve query performance: most of the time, the optimizer is actually right. > Interfering with improving the query planner: people who use hints seldom report the query problem to the project. https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
- alyandon 5y agoThere is no optimizer that is capable of getting the correct result 100% of the time. Even Oracle with decades of costly engineering behind it requires query tuning (either via explicit hints on the client side sql or via server side plan tuning) at any reasonably large scale. I like Postgresql and use it when I can and I respect the Postgreql core dev team's adherence to principles but in this particular case I believe they are just inventing arguments to justify their unreasonable stance against allowing query hinting.
- _jal 5y agoSo "most of the time" means "100% of the time" where you live?
- stormbrew 5y agoI mean, I think this is just one of those things where it only matters rarely, but when it does it matters a lot.
- staticassertion 5y agoMaybe postgres isn't the right solution in those cases. If you need tight control over how your queries are executing you may not want an rdbms.
- dspillett 5y agoI know nothing of why postgres don't want to implement them, but coming from the MS SQL Server side of things I can tell you they are quite an effective foot-gun in a number of circumstances. More often, in fact, than they are a benefit. Often people reach for index hints when they should be fixing issues in the query or the available indexes, swapping a scan for many seeks which on small data does give a benefit but once code goes into production and the data grows performance falls through the floor. Also if they are named in the hint then you open up a new family of errors if the index gets altered later (though you could perhaps implement the hint as “use an index on columns x & y” rather than “use this specific named index” to get around this). There are circumstances where they are a genuinely useful tool, of course, but not nearly as many as people seem to think. Beyond that I assume implementation will have difficult points. If the hints are taken as instructions it may require significant changes to the rest of the generated query plan, if they are sometimes ignored then people will complain bitterly…
- password4321 5y agoI agree, and viraptor came to my rescue recently: https://news.ycombinator.com/item?id=31067059#31068194 https://news.ycombinator.com/item?id=31067059#31068194 > There's also an extension if you want just the hints: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html
- fdr 5y agoProbably. There are old legends about why they're not in, when it's really more a laundry list as to how they are perhaps somewhat less useful than people think. My personal opinion is that missing from the equation is that it's an invasive feature with a wide interface, and nobody in a position to do the work and credibly maintain it for a long period (this is a tiny number of people) has found it the optimal use of their time. You can contrast this with, say, the JIT feature, where it's a ton of technical work, but the interface is relatively thin. I feel like the evolution in regarding hot standby was somewhat similar: people had objections, people pointed out weaknesses in this family of systems, people pointed at Slony or Skytools, but when someone cleared their schedule and looked like they'd account for bugs for a good while, it wasn't such a big fight in the end.
- jerrysievert 5y agothere is a maintained index hint extension: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan - at least as far as 13 (and likely 14). if we're going to talk about index functionality that would be good and effective for Postgres, an index across all partitioned tables (both normal and unique) would be very much welcomed. the problem is finding someone to maintain it for life.
- rtpg 5y agoI would go further and say that there should be an alternate language that lets you just provide a planned path. Just yesterday i spent time debugging a whole query thing cuz heuristics made the planner plan the wrong thing. The planner is pretty amazing black magic that can just make life easy (just like compilers do a bunch of cool stuff. But I think SQL perf would be a lot easier for people to grok if the low level involved specifying “hey, system, go through this btree, pull these items, merge with items from this other btree, etc”
- kevan 5y agoDo any relational DBs let you lock statistics or the query planner directly? Something like how ML pipelines are split between training and inference phases. Let me train the optimizer on a representative load (prod replay or synthetic) and then lock the query planner for my production traffic. Still take advantage of the black magic but control it so query plans don't unexpectedly change.
- ako 5y agoProblem with databases is that they have the tendency to grow over time, so the statistics change all the time, and thus the optimal query plan.
- daigoba66 5y agoMSSQL mostly has this feature with Query Store.
- iracic 5y agoYes, some are able to verify that execution plan already exists (saved in gather run or by explicit command) before replaning. Also you can group them with one label and activate only when needed. I guess that there must be a good number of people who would use it in PostgreSQL. Anybody analyzed previous tries to implement it?
- protomyth 5y agoSybase 11 and 12 let you force a plan and declare what indexes to use including temp tables if they were declared in a stored procedure that was calling the stored procedure you used the indexes. The optimizer was not the best.
- deleted 5y ago[deleted]
- ako 5y agoI always assumed that an ideal query planner would be in the best position to decide what plan is best based if it had real-time access to up-to-date data statistics and available indexes. Adding hints might hinder performance improvements when migrating to a newer version of a database, as the newer version might have a better way to execute your query than what the hint is suggesting. You'd have to revisit all your hints when upgrading.
- g_delgado14 5y agoKind of unrelated ... but what's the pganalyze of MySQL?
- winrid 5y agoThe fun thing about databases is that they can sometimes spend more time figuring out what to do than actually executing the query. It's usually worth it - because doing the wrong thing is pretty expensive. But it's nice to be able to use index hints when you understand and accept the danger. I've seen queries that take 30-40ms drop to < 1ms just by telling the DB which index to use, on multi terrabyte data sets with a few complicated indexes. The query always was fast, and picked the same index, but by just telling it which index to use you get an order of magnitude better performance.
- winrid 5y agoAlso for those curious here's the C++ query planner for Mongo: https://github.com/mongodb/mongo/blob/master/src/mongo/db/query/query_planner.cpp#L624 https://github.com/mongodb/mongo/blob/master/src/mongo/db/qu... Checking whether the key pattern matches an index: https://github.com/mongodb/mongo/blob/master/src/mongo/db/query/planner_ixselect.cpp#L355 https://github.com/mongodb/mongo/blob/master/src/mongo/db/qu... I wish Mongo could use intersecting indexes for queries and sorting...
- SigmundA 5y agoIts was actually very surprising to me to find PG does not cache query plans but instead replans on every execution! I am used to db's like MSSQL server that cache and reuse plans making things like prepared statements redundant. My understanding is the planner is so simple that it is deemed not necessary, unless of course it is then do a prepared statement which only works on one connection... This would absolute trash performance on SQL server to not cache plans as its planner spends significant time optimizing.
- deleted 5y ago[deleted]
- winrid 5y agoI did not know PG lacked a plan cache. I guess if the planner is fast enough trying to build cache keys from a query will just slow things down.
- zmmmmm 5y agoI love this kind of breakdown that not only explains how something works but walks you through the actual mechanics at the code level. Apart from the explanation itself, it shows how nice the Postgresql code is. It's a joy to read through. And specifically this topic is great to understand better because I have multiple times encountered queries that chose insane strategies that resulted in ridiculously long query times. Unfortunately the developers of Postgresql really seem to have a "you're holding it wrong" approach to this issue ... yes I know I'm doing something dumb if my query plan is doing that, but it would be really nice if Postgres gave better tools to figure out why so that I can do it better. The explain plan tells you what plan it created, but it doesn't help you understand why it didn't do the one that you think it should. This is where hints (which Postgresql doesn't support) are so powerful because if I can force it to use the plan I am thinking of and then see what costs it estimated for each operation, I can figure out why it is rejecting it. I would love to know if there are any good tools for this!
- deleted 5y ago[deleted]
- Ozzie_osman 5y agoI've found that the planner generally does a good job with single-table restrictions. With joins, not so much. Sometimes simply turning a join of multiple tables into a CTE has forced the planner to be more efficient. Sometimes, counterintuitive, doing the join in the application is faster. And yes, before resorting to any of those, I've spent time doing EXPLAINs and making sure I have the right indexes.
- olau 5y agoSimple constricting joins are usually okay, but if you have joins with "or" conditions on largish tables, that can easily throw it off completely. I find that I often need to rewrite those to use union.
- mbalex99 5y agoWhenever I see query planning in databases, I immediately think that artificial intelligence should be able to do this pretty well. Which databases out there actually use some sort of AI to create a plan and execute?
- pjungwir 5y agoPostgres does. :-) At least if genetic algorithms count as AI. If there are a lot of tables in the query, Postgres will switch to its GEQO planner [0]. The hardest thing about planners is they need to make a decision fast. It would be silly if they took longer than the marginal improvement over a worse plan. And since the possible ways to join tables grows exponentially, an exhaustive comparison quickly becomes impractical. [0] https://www.postgresql.org/docs/current/geqo.html https://www.postgresql.org/docs/current/geqo.html