X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id A7CF4D1B4D2 for ; Fri, 13 Feb 2004 02:30:32 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 06843-03 for ; Thu, 12 Feb 2004 22:30:30 -0400 (AST) Received: from ozimelb03.ozicom.com (unknown [203.185.247.100]) by svr1.postgresql.org (Postfix) with ESMTP id 92140D1B4BE for ; Thu, 12 Feb 2004 22:30:25 -0400 (AST) Content-class: urn:content-classes:message Subject: Index question MIME-Version: 1.0 Date: Fri, 13 Feb 2004 13:30:10 +1100 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-ID: X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Index question Thread-Index: AcPx2U2Fes9yEfwZSiGPQNpCqrszWQ== From: "David Witham" To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/129 X-Sequence-Number: 16820 Hi all, I have a table with around 3M records in it and a few indexes on it. One of= them is on the day column. I get 10-20K new records a day. After running A= NALYSE in psql I tried the following queries: buns=3D# explain select count(*) from cdr where day >=3D '20040127'; QUERY PLAN=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ------------------------------------------------------------------ Aggregate (cost=3D85596.50..85596.50 rows=3D1 width=3D0) -> Seq Scan on cdr (cost=3D0.00..85053.86 rows=3D217055 width=3D0) Filter: ("day" >=3D '2004-01-27'::date) (3 rows) buns=3D# explain select count(*) from cdr where day =3D '20040127';=20 QUERY PLAN=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ----- Aggregate (cost=3D12950.10..12950.10 rows=3D1 width=3D0) -> Index Scan using cdr_ix1 on cdr (cost=3D0.00..12928.00 rows=3D8839 = width=3D0) Index Cond: ("day" =3D '2004-01-27'::date) (3 rows) buns=3D# explain select count(*) from cdr where day between '20040127' and = current_date; QUERY PLAN=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 ---------------------------------------------------------------------------= ------------- Aggregate (cost=3D20129.91..20129.91 rows=3D1 width=3D0) -> Index Scan using cdr_ix1 on cdr (cost=3D0.00..20095.66 rows=3D13699= width=3D0) Index Cond: (("day" >=3D '2004-01-27'::date) AND ("day" <=3D ('now= '::text)::date)) (3 rows) I understand that selecting count(*) will involve a scan at some stage, but= I was surprised that the index wasn't used in the >=3D case, but was used = in the between case. Why is this so? Do I need to ANALYSE some more or is this just the way the = query planner works? Thanks, David David Witham Telephony Platforms Architect Unidial Ph: 03 8628 3383 Fax: 03 8628 3399