CODEWITHSUNDEEP
sqldatabasepostgresql
Gnanam
Gnanam

Reputation: 10923

Does arrangement/order of tables in FROM clause make any difference in improving performance?

In general, does arrangement or order of tables in the FROM clause make any difference in improving the performance of the query? By arrangement I mean smallest table and largest table.

Any different experience/ideas/opinions/factors are also appreciated.

In my case, we're using PostgreSQL v8.2.3.

Upvotes: 5

Views: 1243

Answers (4)

Peter Eisentraut
Peter Eisentraut

Reputation: 36739

If you write FROM foo, bar, baz then no, it doesn't make a difference. If you write FROM foo JOIN bar ... JOIN baz ... (inner joins), then it doesn't make a difference if you have less than join_collapse_limit elements; otherwise the join order is fixed, which you can use to manually optimize such things (but that is rarely needed). For outer joins, the join order is fixed as it is written, because that affects the results.

Upvotes: 0

araqnid
araqnid

Reputation: 133692

For inner joins, it should not make any difference- the optimiser will generate plans doing the joins in as many different orders as is possible (up to geqo_threshold tables in the from clause).

Outer joins are not symmetric, so there the ordering in the statement is significant (although the actual execution order is still decided by the server).

Upvotes: 4

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

(Generic SQL advice, rather than Postgre specific)

It shouldn't make a difference - the optimizer should be building a plan based on the information it has available to it, and SQL has generally been designed so that the optimizer is free to reorder joins, condition checks, etc, provided that the result is the same.

It may be that a particular query causes problems for a particular optimizer (for a certain DB, server state, version of product, etc), but it should be rare to encounter such a situation.

Upvotes: 0

Nanne
Nanne

Reputation: 64419

Are you using something like "SELECT FROM table1,table2" ? The tables are "implicitly" cross-joined, so I don't think it matters.

The first thing I would do is test two possible queries using EXPLAIN, and see if there is any difference?

Upvotes: 2

Related Questions