SELECT * FROM table1
JOIN table2 USING (key2)
LEFT JOIN table3 JOIN table4 USING (key4) USING (key3)
All keys are primary keys and so indexed, and table1.x and table2.y are also indexed. VACUUM ANALYZE has been run.
This query takes a very slow 17 seconds. I can see from the query plan that this is due to sequence scans on table3 and table4.
If I change it from a LEFT JOIN to a JOIN then it takes under 0.5 seconds and it correctly uses the indexes on table3.key3 and table4.key4.
It's also fast (<0.5s) if I remove the join to table4, OR the join to table2 OR the last "AND table2.y=2".
So, as its any of a number of things that drastically speed it up, it seems its the complexity of the query rather than any lack of indexing that's causing the slow behaviour.
Any ideas why this would be ? Is there any way I can force it to use the indexes on table3 and table4 ? Am I missing something ?