CODEWITHSUNDEEP
postgresql
EralpB
EralpB

Reputation: 1742

Does SQL execute subqueries fully?

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.

Upvotes: 0

Views: 39

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246463

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.

Upvotes: 1

Related Questions