By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,706 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,706 IT Pros & Developers. It's quick & easy.

performance problems: join conditions

P: n/a
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".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

Nov 23 '05 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.