From: | George Young <gry(at)ll(dot)mit(dot)edu> |
---|---|
To: | pgsql-general(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | bad select performance for where (x=1 or x=3) |
Date: | 1999-07-20 14:02:39 |
Message-ID: | 199907201401.KAA03008@ll.mit.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general 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(at)ll(dot)mit(dot)edu
MIT Lincoln Laboratory
244 Wood St.
Lexington, Massachusetts 02420-9108 (781) 981-2756
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-07-20 14:53:39 | Re: [SQL] bad select performance for where (x=1 or x=3) |
Previous Message | The Hermit Hacker | 1999-07-20 13:24:30 | Announcement Sites ... ? |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 1999-07-20 14:53:39 | Re: [SQL] bad select performance for where (x=1 or x=3) |
Previous Message | Vikrant Rathore | 1999-07-20 04:30:28 |