Does SQL execute subqueries fully?

Question

Imagine I have this SQL query and the table2 is HUGE.

select product_id, count(product_id) 
from table1 
where table2_ptr_id in (select id 
                        from table2 
                        where author is not null)

Will SQL first execute the subquery and load all the table2 into memory? like if table1 has 10 rows and table2 has 10 million rows will it be better to join first and then filter? Or DB is smart enough to optimize this query as it is written.

Databases are actually smarter. They take the query, check indexes and statistics and convert the query to an actual execution plan that takes advantage of any existing indexes, uses different matching algorithms based on data statistics etc. If author and id appear in an index only the index data pages will be used. — Panagiotis Kanavos
I suggest you to start using EXPLAIN and EXPLAIN ANALYZE to learn more about query planning and execution in PostgreSQL. Details for those statements and their output is available in PostgreSQL's documentation. — Pavel Horal

Laurenz Albe Laurenz Albe · Accepted Answer · 2018-09-05T08:44:17

You have to EXPLAIN the query to know what it is doing.

However, your query will likely perform better in PostgreSQL if you rewrite it to

SELECT product_id
FROM table1 
WHERE EXISTS (SELECT 1
              FROM table2
              WHERE table2.id = table1.table2_ptr_id
              AND table2.author IS NOT NULL);

Then PostgreSQL can use an anti-join, which will probably perform much better with a huge table2.

Remark: the count in your query doesn't make any sense to me.

Does SQL execute subqueries fully?

1 Answers