Achilleus Mantzios <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
