3 ms·

Pg_hint_plan – Use planner hints on PostgreSQL

• GordonS 5y ago
  Lack of hints is one of the biggest beefs some people have with Postgres. I just came across pg_hint_plan, and thought this would be of interest to a lot of folks here - it certainly is to me! The author's page about pg_hint_plan: https://pghintplan.osdn.jp/pg_hint_plan.html https://pghintplan.osdn.jp/pg_hint_plan.html Also available in Japanese: https://pghintplan.osdn.jp/pg_hint_plan-ja.html https://pghintplan.osdn.jp/pg_hint_plan-ja.html
• iaabtpbtpnn 5y ago
  This is very useful! Postgres is opinionated about its lack of hints. And while I don't love hints as a development method (better to figure out why the planner made a suboptimal choice and correct the statistics accordingly), sometimes it's necessary to be sure that a query running in production isn't suddenly going to change from a fast plan to a slow one unpredictably.
• zbuf 5y ago
  On balance, I've found PostgreSQL lack of hints to be a good thing. I can recall a single genuine case where the planner could not make a good plan, and 1000 where a colleague would have created some unmaintainable time bomb; instead they were driven to understand an actual underlying issue that resulted in a more accurate expression of the query. That one case, as the author says, is PostgreSQL statistics are per-table and do not understand correlation in the data. How different would the implementation be for the hints to express these correlations instead?
• PeterZaitsev 5y ago
  Where I find plan hints very helpful is to force query a certain way of query execution to validate if the plan you THINK is better actually better. In many cases I see developers thinking they have better plan in mind... only to see it actually results in slower query execution. It is easy to demo for MySQL but not so for PostgreSQL