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