How to manipulate join order of PostgreSQL optimizer?

Question

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?

score 8 · Accepted Answer · edited Mar 10 '18 at 11:23

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

score 1 · Answer 2 · edited Mar 02 '23 at 11:09

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.

edited Mar 02 '23 at 11:09

200_success

196
1
11

answered Mar 08 '18 at 15:44

Pradeep

11
3

How to manipulate join order of PostgreSQL optimizer?

2 Answers2