From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: pgsql-sql@postgresql.org
Subject: Index usage on date feild , differences in '>' and '>=' and between
Date: Thu, 19 Sep 2002 12:08:46 +0530
Message-ID: <200209191208.46626.mallah@trade-india.com> (raw)
Hi ,
I am trying to improve a query to use existing indexes but facing diffculty.
Looks like 'between' amd '>=' are not using indexes althoug > and < does.
all my application code uses between and i am sure it use to work fine
at one point of time.
regds
mallah.
SQL TRANSCRIPT:
======================================================================================
tradein_clients=> explain select list_id from eyp_rfi a where generated > '2002-08-13' and generated < '2002-09-19' ;
NOTICE: QUERY PLAN:
Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4)
EXPLAIN
tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated < '2002-09-19' ;
NOTICE: QUERY PLAN:
Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4)
EXPLAIN
tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated <= '2002-09-19' ;
NOTICE: QUERY PLAN:
Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4)
EXPLAIN
tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-08-13' and '2002-09-19' ;
NOTICE: QUERY PLAN:
Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4)
EXPLAIN
tradein_clients=>
======================================================================================
--
Rajesh Kumar Mallah,
Project Manager (Development)
Infocom Network Limited, New Delhi
phone: +91(11)6152172 (221) (L) ,9811255597 (M)
Visit http://www.trade-india.com ,
India's Leading B2B eMarketplace.
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: mallah@trade-india.com
Subject: Re: Index usage on date feild , differences in '>' and '>=' and between
In-Reply-To: <200209191208.46626.mallah@trade-india.com>
* 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