How to manipulate join order of PostgreSQL optimizer?

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.

Related: Slow fulltext search due to wildly inaccurate row estimates

StackExchange Database Administrators Q#82519, answer score: 8