performance problems: join conditions

[Joseph Shraibman]

I have a query like this:

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".uke y = "inner".uke y)
Join Filter: (("outer".statu s = 3) OR ("inner".sta tus
= 3))
-> Seq Scan on u (cost=0.00..413 30.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..334 51.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