SELECT ... FROM u, d WHERE d.ukey = u.ukey AND <restrictions on u> AND (d.status = 3 OR (u.status = 3 AND d.status IN(2,5)));
explain shows:
-> Aggregate (cost=126787.04..126787.04 rows=1 width=4)
-> Hash Join (cost=39244.00..126786.07 rows=387 width=4)
Hash Cond: ("outer".ukey = "inner".ukey)
Join Filter: (("outer".status = 3) OR ("inner".status = 3))
-> Seq Scan on u (cost=0.00..41330.30 rows=428294 width=6)
Filter: ((podkey = 260) AND (NOT banned))
-> Hash (cost=33451.61..33451.61 rows=904156 width=6)
-> Seq Scan on d (cost=0.00..33451.61 rows=904156 width=6)
Filter: ((status = 2) OR (status = 5) OR (status = 3))
counts: d: status of 3: 1 total: 1026480
u: status of 3: 1080 total: 1531154
The query is trying to find entries where the status is 3 in one table or the other, but postgres won't use an index because it uses the status of 3 in the join condition. So it is using slow seqscans even though index queries would be much faster because the total number of entries where one or the other has status of 3 is small.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
