[HN Gopher] How Postgres Chooses Which Index to Use for a Query
___________________________________________________________________
How Postgres Chooses Which Index to Use for a Query
Author : tosh
Score : 46 points
Date : 2022-04-21 21:37 UTC (1 hours ago)
(HTM) web link (pganalyze.com)
(TXT) w3m dump (pganalyze.com)
| qeternity wrote:
| Postgres really really needs index hints.
| gleenn wrote:
| Is there a good reason they haven't added them? Difficult to
| implement?
| gfody wrote:
| 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.
| luhn wrote:
| > 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
| alyandon wrote:
| 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 vs explicit hints on the client side sql or via
| server side plan tuning) at any reasonably large scale.
|
| 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.
| dspillett wrote:
| 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 as many and 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 wrote:
| I agree, and viraptor came to my rescue recently:
|
| 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 _
___________________________________________________________________
(page generated 2022-04-21 23:00 UTC)