Re: [SQL] 7.3.1 index use / performance - Mailing list pgsql-performance

Achilleus Mantzios <achill@matrix.gatewaynet.com> writes:
>> Also, I would like to see the results of these queries on both versions,
>> so that we can see what the planner thinks the index selectivity is:
>>
> [ data supplied ]

There is something really, really bizarre going on there.  You have

dynacom=# EXPLAIN ANALYZE select * from noon where report_date between '2002-01-07' and '2003-01-07';
                                                           QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_date on noon  (cost=0.00..15919.50 rows=11139 width=1974) (actual time=2.05..13746.17 rows=7690
loops=1)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
 Total runtime: 13775.48 msec
(3 rows)

and from your earlier message

dynacom=# EXPLAIN ANALYZE select FUELCONSUMPTION,rpm,Steam_Hours,voyage_activity,ldin from noon where
v_code='4500' and rotation='NOON     ' and report_date between '2002-01-07' and '2003-01-07';
                                                    QUERY PLAN

-------------------------------------------------------------------------------------------------------------------
 Index Scan using noonf_date on noon  (cost=0.00..4.46 rows=1 width=39) (actual time=0.27..52.89 rows=259 loops=1)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
   Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON'::character varying))
 Total runtime: 53.98 msec
(4 rows)

There is no way that adding the filter condition should have reduced the
estimated runtime for this plan --- reducing the estimated number of
output rows, yes, but not the runtime.  And in fact I can't duplicate
that when I try it here.  I did this on 7.3.1:

regression=# create table noon (v_code character varying(4) ,
regression(# report_date   date ,
regression(# rotation   character varying(9));
CREATE TABLE
regression=# create index noonf_date on noon(report_date);
CREATE INDEX
regression=# EXPLAIN select * from noon where report_date between
regression-# '2002-01-07' and '2003-01-07';
                                         QUERY PLAN

---------------------------------------------------------------------------------------------
 Index Scan using noonf_date on noon  (cost=0.00..17.08 rows=5 width=25)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
(2 rows)

regression=# explain select *  from noon where
regression-# v_code='4500' and rotation='NOON     ' and report_date between
regression-# '2002-01-07' and '2003-01-07';
                                            QUERY PLAN

--------------------------------------------------------------------------------
------------------
 Index Scan using noonf_date on noon  (cost=0.00..17.11 rows=1 width=25)
   Index Cond: ((report_date >= '2002-01-07'::date) AND (report_date <= '2003-01-07'::date))
   Filter: ((v_code = '4500'::character varying) AND (rotation = 'NOON     '::character varying))
(3 rows)

Note that the cost went up, not down.

I am wondering about a compiler bug, or some other peculiarity on your
platform.  Can anyone else using FreeBSD try the above experiment and
see if they get different results from mine on 7.3.* (or CVS tip)?

            regards, tom lane