Menu
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.
545
No. The order of columns in the WHERE clause does not matter at all.
Basically, join order DOES matter because if we can join two tables that will reduce the number of rows needed to be processed by subsequent steps, then our performance will improve.
PostgreSQL doesn't care what order you write things in PostgreSQL doesn't care at all about the order of entries in a WHERE clause, and chooses indexes and execution order based on cost and selectivity estimation alone.
Answer. Generally, no, the order of the tables in the JOIN will not affect the overall results of the query. As long as you specify what columns to select, the results should appear essentially the same, just that the rows will be ordered according to the appearance in the first table.
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).
181
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?
29
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
