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)



Thu, 18 Apr 2002 13:34:46 +0200 (CEST)
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz>
another optimizer question
Jakub Ouhrabka <jouh8664@ss1000.ms.mff.cuni.cz> writes:
> 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?

!= isn't an indexable operation.  This is not the planner's fault, but
a consequence of the index opclass design we inherited from Berkeley.
I suppose we could make it an indexable operation --- but there are so
few cases where it'd be a win that I'm not excited about it.

			regards, tom lane

Thu, 18 Apr 2002 10:31:26 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: another optimizer question 