To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>,
	pgsql-performance@postgresql.org, pgsql-general@postgresql.org,
	pgsql-sql@postgresql.org
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance 
In-reply-to: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com> 
References: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com>
Comments: In-reply-to Achilleus Mantzios <achill@matrix.gatewaynet.com>
	message dated "Tue, 07 Jan 2003 19:05:22 -0200"
Date: Tue, 07 Jan 2003 12:44:16 -0500
Message-ID: <14598.1041961456@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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