5 ms·

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

by gleenn 5y ago

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
        to be fair sql server's holdlock is pretty specific to sql server, there's this note on pg's merge project page (https://wiki.postgresql.org/wiki/SQL_MERGE https://wiki.postgresql.org/wiki/SQL_MERGE) > PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature. as of 2017 at least the plan was to implement merge using the underlying insert on conflict mechanism and I think everyone agrees that the only reasonable expected behavior from merge is atomic upserts (and the pk violations from concurrent merges on sql server and oracle really should be recognized as bugs!) so maybe postgres merge will be the first non-buggy implementation w/o hints. reading the related threads exposes a surprising amount of complexity though so who knows.
- 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…

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

by 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
        to be fair sql server's holdlock is pretty specific to sql server, there's this note on pg's merge project page (https://wiki.postgresql.org/wiki/SQL_MERGE https://wiki.postgresql.org/wiki/SQL_MERGE) > PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature. as of 2017 at least the plan was to implement merge using the underlying insert on conflict mechanism and I think everyone agrees that the only reasonable expected behavior from merge is atomic upserts (and the pk violations from concurrent merges on sql server and oracle really should be recognized as bugs!) so maybe postgres merge will be the first non-buggy implementation w/o hints. reading the related threads exposes a surprising amount of complexity though so who knows.
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…