another optimizer question
От | Jakub Ouhrabka |
---|---|
Тема | another optimizer question |
Дата | |
Msg-id | Pine.LNX.4.33.0204181313220.23244-100000@u-pl2 обсуждение исходный текст |
Ответы |
Re: another optimizer question
|
Список | 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 nullts19zavaz | integer | not nullts19cislo | integer | not nullts19text_ | character varying(65536) | not nullts19idpri | 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)
В списке pgsql-hackers по дате отправления: