Re: Index usage on date feild , differences in '>' and '>=' and between

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.