Re: [SQL] [PERFORM] 7.3.1 index use / performance

Achilleus Mantzios <achill(at)matrix(dot)gatewaynet(dot)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