2

I was interested in some of the general ways one can use to optimize the performance of queries that use keywords like IN and EXISTS (relying on the state of the entire database).

For example, something like 

SELECT id FROM table1 WHERE A IN (SELECT B FROM table2 WHERE C < 200)

The execution plan for this query involves a hash join that takes almost 2 seconds on a 1 GB database. Given that I may be executing many of these queries, are there any steps I can take to optimize these queries? Something along the lines of creating an index/materializing some data?

Thanks!

paulinho
  • 133
  • 6

1 Answers1

0

Try to use equivalent query with JOIN and without IN or EXISTS and compare the runtimes:

select id
from table1 join table2 on table1.A = table2.B
where table2.C < 200
group by id

Look also at similar question here, where similar optimization is proposed for much more complex query: https://stackoverflow.com/q/17038193/684229. But note that the JOIN solution is not always faster; sometimes, surprisingly, the IN/EXISTS queries come out as faster. Depends on the optimizer. That's why I suggest to test both variants.

Tomas
  • 111
  • 2