7 ms·

Postgres really really needs index hints.

by qeternity 5y ago

gleenn 5y ago
Is there a good reason they haven't added them? Difficult to implement?
- ako 5y ago
  Many 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 ago
  seems 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 ago
    Of 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 ago
      They'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 ago
        Did 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 ago
        Did you read the text that uhoh-itsmaciek posted?
        nitrogen 5y ago
        From 2014. The MERGE discussion is from 2022.
        uhoh-itsmaciek 5y ago
        I 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 ago
        Other 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.
        gfody 5y ago
  - 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 ago
        The 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 ago
    There 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 ago
      So "most of the time" means "100% of the time" where you live?
      - stormbrew 5y ago
        I 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 ago
        Maybe 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 ago
  I 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 ago
I 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 ago
Probably. 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 ago
there 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 ago
I 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 ago
  Do 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 ago
    Problem 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 ago
    MSSQL mostly has this feature with Query Store.
  - iracic 5y ago
    Yes, 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 ago
    Sybase 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 ago
I 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.

Postgres really really needs index hints.

gleenn 5y ago

Is there a good reason they haven't added them? Difficult to implement?

ako 5y ago
Many 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 ago
seems 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 ago
  Of 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 ago
    They'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 ago
      Did 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 ago
        Did you read the text that uhoh-itsmaciek posted?
        nitrogen 5y ago
        From 2014. The MERGE discussion is from 2022.
        uhoh-itsmaciek 5y ago
        I 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 ago
        Other 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.
        gfody 5y ago
- 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 ago
      The 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 ago
  There 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 ago
    So "most of the time" means "100% of the time" where you live?
    - stormbrew 5y ago
      I 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 ago
        Maybe 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 ago
I 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 ago

I 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 ago

Probably. 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 ago

there 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 ago

I 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 ago
Do 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 ago
  Problem 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 ago
  MSSQL mostly has this feature with Query Store.
- iracic 5y ago
  Yes, 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 ago
  Sybase 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 ago

I 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.