search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
710 stars 103 forks source link

Allow using table name in hint when there is a single reference for a table #154

Closed Akshit97 closed 2 months ago

Akshit97 commented 1 year ago

While using hints which require table name/alias name like INDEXSCAN, if we have a query without any alias then we can use the table name in the hint, but if there is an alias associated with the table, then we are not able to use the table name, even if there is a single reference for that table.

Examples:

/*+ INDEXSCAN(TableA INDEXA) */ select * from TableA;

This is working.

/*+ INDEXSCAN(TableA INDEXA) */ select * from TableA a;

This is not working.

Can we support this, as in many cases ORMs like Hibernate are generating the query aliases at runtime, so passing alias name in hint is not possible.

michaelpq commented 2 months ago

Complain back to your ORM that has the idea to blindly change your queries, then, because that's not something pg_hint_plan can know about :)

The default behavior to rely on aliases by default is more useful so as it is possible to target correctly a hint if a same table is used in multiple portions of the query.

Spoiler: I've heard a similar request not long ago, discarded it based on the same argument. PostgreSQL has a very bad history of GUCs manipulating query behaviors (see the GUC autocommit around 8.1~8.2-ish area, for example).