Re: Index usage on date feild , differences in '>' and '>=' and between - Mailing list pgsql-sql

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
bygenerated; 

generated  | count
------------+-------2002-09-10 |   4422002-09-11 |  10602002-09-12 |   6412002-09-13 |   6072002-09-14 |
13202002-09-15|   5212002-09-16 |  14742002-09-17 |   9402002-09-18 |  10052002-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.