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?
0
Add a comment
|
2 Answers
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.
Related: Slow fulltext search due to wildly inaccurate row estimates
answered Nov 13, 2014 at 7:38
-
1When such techniques are needed, one table is much bigger than another and it's very unlikely that this fact will change. So I'm wondering, why do you suggest to avoid this? Mar 10, 2018 at 11:28
-
You can also control the collapse of subqueries in a FROM clause with
from_collapse_limit, see here: postgresql.org/docs/14/…– ADJenksDec 31, 2021 at 23:18
There is also an extension to force join order using hints (comments). This pg_hint_plan will help you to get the preffered order.
-
pg_hint_plan seems to be something like Hints in Oracle. But in this case the module is developed by other people than Postgres developers, and this makes me not confident about installing this module in production server.– EAmezAug 22, 2019 at 12:41