pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbad select performance for where (x=1 or x=3)
2+ messages / 2 participants
[nested] [flat]
* bad select performance for where (x=1 or x=3)
@ 1999-07-20 14:02 George Young <gry@ll.mit.edu>
0 siblings, 1 reply; 2+ messages in thread
From: George Young @ 1999-07-20 14:02 UTC (permalink / raw)
To: pgsql-general@postgresql.org; pgsql-sql
[PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66]
I have a 'select' including 'where (x=1 or x=3)' that takes 16 times as long
as with just 'where x=1'. Here's a (somewhat simplified) example:
table opset_steps
(name text, id int2, ver int2) [1400 rows]
non-unique index is on (id, ver)
table run_opsets
(status int2, id int2, ver int2, run_id int2, seq int2) [17000 rows]
pkey is (id, seq), second index on(status, id, ver, run_id)
select count(*) from run_opsets where status=1; --> 187
select count(*) from run_opsets where status=3; --> 10564
table runs
(run_name text, run_id int2, status int2) [900 rows]
pkey is run_name, second index(run_id, status)
I have vacuum analyzed all relevant tables.
This query takes 16 seconds (without the explain of course):
explain select os.name,r.run_name,ro.status from opset_steps os,runs r,run_opsets ro where (ro.status=1 or ro.status=3) and ro.opset_id=os.opset_id and ro.run_id=r.run_id and ro.opset_ver=os.opset_ver and r.status=1;
Hash Join (cost=1793.58 rows=14560 width=38)
-> Hash Join (cost=1266.98 rows=14086 width=24)
-> Seq Scan on run_opsets ro (cost=685.51 rows=13903 width=8)
-> Hash (cost=70.84 rows=1389 width=16)
-> Seq Scan on opset_steps os (cost=70.84 rows=1389 width=16)
-> Hash (cost=47.43 rows=374 width=14)
-> Seq Scan on runs r (cost=47.43 rows=374 width=14)
This query takes just under one second:[diff is status=1 instead of (1 or 3)]
explain select os.name,r.run_name,ro.status from opset_steps os,runs r,run_opsets ro where ro.status=1 and ro.opset_id=os.opset_id and ro.run_id=r.run_id and ro.opset_ver=os.opset_ver and r.status=1;
Hash Join (cost=1359.57 rows=7719 width=38)
-> Hash Join (cost=1051.39 rows=7467 width=24)
-> Seq Scan on run_opsets ro (cost=685.51 rows=7370 width=8)
-> Hash (cost=70.84 rows=1389 width=16)
-> Seq Scan on opset_steps os (cost=70.84 rows=1389 width=16)
-> Hash (cost=47.43 rows=374 width=14)
-> Seq Scan on runs r (cost=47.43 rows=374 width=14)
Why should it take over 16 times as long for (status=1 or 3) as for status=1?
I have indexes on exactly all the fields used in the where clause.
I tried "status in (1,3)" with no improvement.
George Young, Rm. L-204 gry@ll.mit.edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts 02420-9108 (781) 981-2756
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] bad select performance for where (x=1 or x=3)
@ 1999-07-20 14:53 Tom Lane <tgl@sss.pgh.pa.us>
parent: George Young <gry@ll.mit.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 1999-07-20 14:53 UTC (permalink / raw)
To: George Young <gry@ll.mit.edu>; +Cc: pgsql-general@postgreSQL.org; pgsql-sql
George Young <gry@ll.mit.edu> writes:
> table run_opsets
> (status int2, id int2, ver int2, run_id int2, seq int2) [17000 rows]
> pkey is (id, seq), second index on(status, id, ver, run_id)
> select count(*) from run_opsets where status=1; --> 187
> select count(*) from run_opsets where status=3; --> 10564
> Why should it take over 16 times as long for (status=1 or 3) as for status=1?
Offhand it looks like the former would produce 57 times as many possible
rows from the run_opsets table as the latter (187+10564 vs 187), which
the system would then have to try to match against the other tables.
You didn't say how many tuples actually get returned, but certainly the
number of iterations through each of the join loops is likely to be much
higher. I'm surprised the cost differential isn't more than 16:1.
A more interesting question might be "why doesn't the system's cost
estimator realize that the second case will be much cheaper?" The
answer to that is that VACUUM ANALYZE doesn't keep sufficiently detailed
statistics to let it realize that there are far more x=3 than x=1 rows.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~1999-07-20 14:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-20 14:02 bad select performance for where (x=1 or x=3) George Young <gry@ll.mit.edu>
1999-07-20 14:53 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox