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?