3 ms·

A really great feature that the Oracle database has is ability to provide hints to the planner. Is there anything that prevents PostgreSQL from adding this feat

A really great feature that the Oracle database has is ability to provide hints to the planner. Is there anything that prevents PostgreSQL from adding this feature? edit: Googled it, looks like PostgreSQL maintainers just have an opinion that hints are bad and the planner is good enough: https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion There are "Explicit JOINs" but I'm not sure how useful they are: https://www.postgresql.org/docs/current/explicit-joins.html https://www.postgresql.org/docs/current/explicit-joins.html

deleted 7y ago
[deleted]
ollysb 7y ago
While it's easy to nod along with their justification for no hints the practical reality is that if the query planner gets it wrong you can have query time an order of magnitude slower. This is particular obvious when using gin indexes with `like`. On the application I'm currently working on the difference between the two indexes is night and day, the gin index will respond in 100ms, whilst the btree index can be 15secs+. We've resorted to having two columns with the same content, one with a btree index and one with a gin index so that we can explicitly choose which index to hit.
goatinaboat 7y ago
I’m reminded of the MySQL developers in the 90s decrying foreign keys, transactions etc as unnecessary overheads, only to belatedly add them later. Hints are one thing I miss coming from Oracle - even being able to hint if you want the first row fastest or the entire result set.
ants_a 7y ago
There is one implementation of hints available as an extension: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan It's unfortunate that it comes off as if maintainers think planner is good enough, because that is demonstrably not true. And from my discussions there is wide agreement that the planner will never be perfect. But agreeing that it's something that needs work is not enough, someone actually has to do the work too. Reality is that implementing any feature to PostgreSQL quality standards is hard work.

deleted 7y ago

[deleted]

ollysb 7y ago

While it's easy to nod along with their justification for no hints the practical reality is that if the query planner gets it wrong you can have query time an order of magnitude slower. This is particular obvious when using gin indexes with `like`. On the application I'm currently working on the difference between the two indexes is night and day, the gin index will respond in 100ms, whilst the btree index can be 15secs+. We've resorted to having two columns with the same content, one with a btree index and one with a gin index so that we can explicitly choose which index to hit.

goatinaboat 7y ago

I’m reminded of the MySQL developers in the 90s decrying foreign keys, transactions etc as unnecessary overheads, only to belatedly add them later. Hints are one thing I miss coming from Oracle - even being able to hint if you want the first row fastest or the entire result set.

ants_a 7y ago

There is one implementation of hints available as an extension: https://github.com/ossc-db/pg_hint_plan https://github.com/ossc-db/pg_hint_plan It's unfortunate that it comes off as if maintainers think planner is good enough, because that is demonstrably not true. And from my discussions there is wide agreement that the planner will never be perfect. But agreeing that it's something that needs work is not enough, someone actually has to do the work too. Reality is that implementing any feature to PostgreSQL quality standards is hard work.