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