4

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?

3

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? – Federico Razzoli Mar 10 '18 at 11:28
1

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 Aug 22 at 12:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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