WHERE and JOIN clauses order performance

user_11444715 0
Viewed 54

I have a query like this:

SELECT *
FROM my_table_1
INNER JOIN my_table 2
USING(column_name)
WHERE my_table_1.date=<my_date>

my_table_1 has millions of lines, but I only want the entries with date=<my_date>

How PSQL query is processed? Is it worth to make an inner join only in the part of the table my_table_1 I want, like:

SELECT *
FROM (
    SELECT *
    FROM my_table_1
    WHERE my_table_1.date=<my_date>
) A
INNER JOIN my_table 2
USING(column_name)

Or the query is processed in a way such that where I put WHERE clause doesn't matter after all?

postgresql sql
1 Answers

No, there is no reason to do so.

To the compiler, these two queries will look exactly the same after optimization. It will use a technique called "predicate pushdown", and other techniques such as switching join order, to transform the query into the most efficient form. Good indexing and up-to-date statistics can be very helpful here.

In very rare circumstances, where the compiler has not calculated correctly, it is necessary to force the order of joins and predicates. But this is not the way to do it, as the compiler can see straight through it.

You can see the execution plan that the compiler has used with EXPLAIN PLAN

user_148689970
Related