performance problems: join conditions

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: performance problems: join conditions
Date: 2004-03-05 05:47:11
Message-ID: 4048145F.4070506@selectacast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Browse pgsql-general by date

  From Date Subject
Next Message Joe Conway 2004-03-05 06:11:56 Re: [GENERAL] dblink: rollback transaction
Previous Message Tom Lane 2004-03-05 05:17:32 Re: building 7.4.1 (on linux) with --disable-shared