From: | Jakub Ouhrabka <jouh8664(at)ss1000(dot)ms(dot)mff(dot)cuni(dot)cz> |
---|---|
To: | <pgsql-hackers(at)postgresql(dot)org> |
Subject: | another optimizer question |
Date: | 2002-04-18 11:34:46 |
Message-ID: | Pine.LNX.4.33.0204181313220.23244-100000@u-pl2 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
hi,
can anyone explain me why there are different query plans for "select ...
from ... where y!=x" and "select ... from ... where y<x or y>x" for
integers, please?
see the details below...
thanks,
kuba
db_cen7=# analyze;
ANALYZE
db_cen7=# \d ts19
Table "ts19"
Column | Type | Modifiers
-----------+--------------------------+--------------------------------------------------------
ts19pk___ | integer | not null default
nextval('"ts19_ts19pk____seq"'::text)
ts19datum | timestamp with time zone | not null
ts19zavaz | integer | not null
ts19cislo | integer | not null
ts19text_ | character varying(65536) | not null
ts19idpri | integer | not null
Indexes: ts19_ts19zavaz_idx
Primary key: ts19_pkey
db_cen7=# explain analyze select * from ts19 where ts19zavaz != 7 order by
ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.17..50868.18 rows=10 loops=1)
-> Sort (cost=89635.63..89635.63 rows=1 width=38) (actual
time=50868.16..50868.17 rows=11 loops=1)
-> Seq Scan on ts19 (cost=0.00..89635.62 rows=1 width=38)
(actual time=95.99..50852.34 rows=300 loops=1)
Total runtime: 50868.27 msec
db_cen7=# explain analyze select * from ts19 where ts19zavaz < 7 or
ts19zavaz > 7 order by ts19pk___ desc limit 10;
NOTICE: QUERY PLAN:
Limit (cost=4.04..4.04 rows=1 width=38) (actual time=1118.28..1118.29
rows=10 loops=1)
-> Sort (cost=4.04..4.04 rows=1 width=38) (actual
time=1118.27..1118.28 rows=11 loops=1)
-> Index Scan using ts19_ts19zavaz_idx, ts19_ts19zavaz_idx on
ts19 (cost=0.00..4.03 rows=1 width=38) (actual time=0.03..1117.58
rows=300 loops=1)
Total runtime: 1118.40 msec
the runtime times depends on the machine load but generally the second
query is much faster...
more info:
db_cen7=# select count(*) from ts19;
count
---------
4190527
(1 row)
db_cen7=# select distinct(ts19zavaz) from ts19;
ts19zavaz
-----------
3
7
(2 rows)
db_cen7=# select count(*) from ts19 where ts19zavaz = 3;
count
-------
300
(1 row)
db_cen7=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)
From | Date | Subject | |
---|---|---|---|
Next Message | mlw | 2002-04-18 11:52:35 | Re: Index Scans become Seq Scans after VACUUM ANALYSE |
Previous Message | Rod Taylor | 2002-04-18 11:33:29 | Re: [PATCHES] YADP - Yet another Dependency Patch |