From: Tom Lane <tgl@sss.pgh.pa.us>
To: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-performance@postgresql.org
Cc: pgsql-general@postgresql.org
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance
Date: Tue, 07 Jan 2003 12:44:16 -0500
Message-ID: <14598.1041961456@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com>
References: <Pine.LNX.4.44.0301071856280.9324-200000@matrix.gatewaynet.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, achill@matrix.gatewaynet.com, sszabo@megazone23.bigpanda.com, pgsql-performance@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [SQL] [PERFORM] 7.3.1 index use / performance
In-Reply-To: <14598.1041961456@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox