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?


2 Answers 2


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

  • 1
    When 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? Commented 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/…
    – ADJenks
    Commented Dec 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.
    – EAmez
    Commented Aug 22, 2019 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.