From: Rajesh Kumar Mallah. <mallah@trade-india.com>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Index usage on date feild , differences in '>' and '>=' and between
Date: Thu, 19 Sep 2002 12:36:11 +0530
Message-ID: <200209191236.11671.mallah@trade-india.com> (raw)
In-Reply-To: <20020918233621.A23870-100000@megazone23.bigpanda.com>
References: <20020918233621.A23870-100000@megazone23.bigpanda.com>
Thanks very much for the response.
set enable_seqscan=off; Definitely helps.
and for wide date ranges it usees indexes.
But with default value of enable_sequence changing date range seems to have effect.
can you explain me a bit more or point me to right documents for understanding
the languae of "EXPLAIN".
====================================================================
EXPLAIN
tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ;
NOTICE: QUERY PLAN:
Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4)
EXPLAIN
tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ;
NOTICE: QUERY PLAN:
Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4)
EXPLAIN
tradein_clients=>
======================================================================
the distribution of values are as follows:
> select generated ,count(generated) from eyp_rfi a where generated between '2002-09-10' and '2002-09-19' group by generated;
generated | count
------------+-------
2002-09-10 | 442
2002-09-11 | 1060
2002-09-12 | 641
2002-09-13 | 607
2002-09-14 | 1320
2002-09-15 | 521
2002-09-16 | 1474
2002-09-17 | 940
2002-09-18 | 1005
2002-09-19 | 178
(10 rows)
Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI
Thanks and Regards
Rajesh Mallah.
Tsday 19 September 2002 12:07, Stephan Szabo wrote:
> On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote:
> > 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)
>
> Given how close the two results are (and the fact that they differ by 900
> rows), have you tried using set enable_seqscan=off and seeing what
> explain gives you for the second query? My guess is that it'll have
> an estimated cost greater than the 17923.81 it's estimating from the
> sequence scan.
--
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, sszabo@megazone23.bigpanda.com
Subject: Re: Index usage on date feild , differences in '>' and '>=' and between
In-Reply-To: <200209191236.11671.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