I am working on table join order optimization.
While query processing, I want to get table join order generated by optimizer, and update it by using my own algorithm in PostgreSQL?
How can I update table join order while query processing?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
You can do that but it needs some trickery.
Imagine you got this:
select ... from a, b, c, d, e ...
...this type of query is always reordered.
But if you do:
select ... from a JOIN b ... JOIN c JOIN d JOIN c ...
...then PostgreSQL will only reorder
join_collapse_limit tables. You can reduce this variable to a low value to force PostgreSQL into your order; however, I would try to avoid that.
This should only be locally using
SET LOCAL, and you should first think, why you want the join order to be changed at all.
There is also an extension to force join order using hints (comments). This pg_hint_plan will help you to get the preffered order.