Proposal for more flexible hint usage in pg_plan_hint

[Hustsurvivor]

Currently, the hint usage in pg_plan_hint is relatively rigid. To enhance flexibility, I propose supporting more complex connection order specifications. For instance, using an expression like ((a b c) (d e f)), which means first connecting a b c and d e f separately, and then joining the results of the two.

In this proposal, the connection order of tables within the parentheses (e.g., (a b c)) should be determined by PostgreSQL’s optimizer, rather than being forced to follow the order of a, b, and c.

Expected Behavior:

1. Multiple subquery join operations can be performed separately.
2. The join order is determined by PostgreSQL’s optimizer to improve query performance.
3. Provides more flexible query optimization capabilities by allowing users to specify the join structure while leaving the join order optimization to PostgreSQL.

Example:

• Input: ((a b c) (d e f))
• Expected Behavior: First, a b c and d e f are joined separately, and then the results of the two are joined. The join order within each set of parentheses (e.g., a b c) is determined by PostgreSQL’s optimizer.

I hope the team will consider this feature request to allow for more flexible query optimization.

[michaelpq]

pg_hint_plan tries to create a parallel with Oracle. Does this proposal mirror with something on the other side of the force?