Content-Type: text/plain;
  charset="iso-8859-1"
From: "Rajesh Kumar Mallah." <mallah@trade-india.com>
Organization: Infocom Network Limited.
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Subject: Re: Index usage on date feild ,
	differences in '>' and '>=' and between
Date: Thu, 19 Sep 2002 12:36:11 +0530
User-Agent: KMail/1.4.1
Cc: <pgsql-sql@postgresql.org>
References: <20020918233621.A23870-100000@megazone23.bigpanda.com>
In-Reply-To: <20020918233621.A23870-100000@megazone23.bigpanda.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200209191236.11671.mallah@trade-india.com>


Thanks very much for the response.
set enable_seqscan=3Doff; Definitely helps.
and for wide date ranges it usees indexes.


But with default value of  enable_sequence changing date range  seems to ha=
ve effect.
can you explain me a bit more or point me to right documents for understand=
ing
the languae of "EXPLAIN".


=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
EXPLAIN
tradein_clients=3D> explain  select   list_id from eyp_rfi a where  generat=
ed between '2002-09-11' and  '2002-09-19'   ;
NOTICE:  QUERY PLAN:

Seq Scan on eyp_rfi a  (cost=3D0.00..17923.81 rows=3D12924 width=3D4)

EXPLAIN
tradein_clients=3D> explain  select   list_id from eyp_rfi a where  generat=
ed between '2002-09-12' and  '2002-09-19'   ;
NOTICE:  QUERY PLAN:

Index Scan using eyp_rfi_date on eyp_rfi a  (cost=3D0.00..17369.05 rows=3D1=
2220 width=3D4)

EXPLAIN
tradein_clients=3D>=20=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

the distribution of values are as follows:
> select   generated ,count(generated) from eyp_rfi a where  generated betw=
een '2002-09-10' and  '2002-09-19'   group by generated;


 generated  | count=20
------------+-------
 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=3Doff" in Application Level? I us=
e 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 '>=3D' are not using indexes althoug > and < d=
oes.
> > all my application code uses between and i am sure it use to work fine
> > at one point of time.
> >
> >
> > regds
> > mallah.
> >
> > SQL TRANSCRIPT:
> > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
> >
> > tradein_clients=3D> explain  select   list_id from eyp_rfi a where=20
> > generated >  '2002-08-13' and generated <  '2002-09-19'   ; NOTICE:=20
> > QUERY PLAN:
> >
> > Index Scan using eyp_rfi_date on eyp_rfi a  (cost=3D0.00..15796.97
> > rows=3D4150 width=3D4)
> >
> > EXPLAIN
> > tradein_clients=3D> explain  select   list_id from eyp_rfi a where=20
> > generated >=3D  '2002-08-13' and generated <  '2002-09-19'   ; NOTICE:=
=20
> > QUERY PLAN:
> >
> > Seq Scan on eyp_rfi a  (cost=3D0.00..17923.81 rows=3D5061 width=3D4)
>
> Given how close the two results are (and the fact that they differ by 900
> rows), have you tried using set enable_seqscan=3Doff 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.

--=20
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.