Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 57333476064 for ; Thu, 19 Sep 2002 02:35:17 -0400 (EDT) Received: from mail-relay.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id ABE3447590C for ; Thu, 19 Sep 2002 02:35:11 -0400 (EDT) Received: (qmail 1358 invoked from network); 19 Sep 2002 06:26:54 -0000 Received: from system67.trade-india-local.com (192.168.0.67) by infocom-236-129-del.trade-india.com with SMTP; 19 Sep 2002 06:26:54 -0000 Content-Type: text/plain; charset="us-ascii" From: "Rajesh Kumar Mallah." Organization: Infocom Network Limited. 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 User-Agent: KMail/1.4.1 MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200209191208.46626.mallah@trade-india.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200209/209 X-Sequence-Number: 9486 Hi , I am trying to improve a query to use existing indexes but facing diffcul= ty. Looks like 'between' amd '>=3D' are not using indexes althoug > and < does. all my application code uses between and i am sure it use to work fine=20 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 generat= ed > '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=3D0.00..15796.97 rows=3D4= 150 width=3D4) EXPLAIN tradein_clients=3D> explain select list_id from eyp_rfi a where generat= ed >=3D '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=3D0.00..17923.81 rows=3D5061 width=3D4) EXPLAIN tradein_clients=3D> explain select list_id from eyp_rfi a where generat= ed >=3D '2002-08-13' and generated <=3D '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=3D0.00..17923.81 rows=3D5061 width=3D4) EXPLAIN tradein_clients=3D> explain select list_id from eyp_rfi a where generat= ed between '2002-08-13' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=3D0.00..17923.81 rows=3D5061 width=3D4) EXPLAIN tradein_clients=3D>=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=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D --=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.