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 75301D1E0E2 for ; Wed, 18 Feb 2004 04:43: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 67969-10 for ; Wed, 18 Feb 2004 00:43:30 -0400 (AST) Received: from ozimelb03.ozicom.com (unknown [203.185.247.100]) by svr1.postgresql.org (Postfix) with ESMTP id 256CED1DEFC for ; Wed, 18 Feb 2004 00:43:29 -0400 (AST) Content-class: urn:content-classes:message Subject: Indexes and statistics MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Date: Wed, 18 Feb 2004 15:43:07 +1100 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Indexes and statistics Thread-Index: AcP12bRdurdlOWvdQqSfhlRkqF0otQ== From: "David Witham" To: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/240 X-Sequence-Number: 16930 Hi all, This is a further post from last week. I've got a table of phone call detai= l records. buns=3D# select count(*) from cdr;=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 count=20=20 --------- 2800653 (1 row) One of the customers is quite large (8.3% of the records): buns=3D# select count(*) from cdr where cust_id =3D 99201110;=20=20=20=20= =20=20=20=20 count=20=20 -------- 231889 (1 row) I have indexes on cust_id (integer) and bill_id (integer). If I try to do a= query on that customer it doesn't use the index: buns=3D# explain analyse select count(*) from cdr where cust_id =3D 9920111= 0 and bill_id is null; 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=20=20=20=20=20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= -------------------------------------- Aggregate (cost=3D87082.81..87082.81 rows=3D1 width=3D0) (actual time=3D8= 2279.63..82279.63 rows=3D1 loops=3D1) -> Seq Scan on cdr (cost=3D0.00..87037.71 rows=3D18041 width=3D0) (act= ual time=3D82279.61..82279.61 rows=3D0 loops=3D1) Filter: ((cust_id =3D 99201110) AND (bill_id IS NULL)) Total runtime: 82280.19 msec (4 rows) I tried this: alter table cdr alter column cust_id set statistics 1000; alter table cdr alter column bill_id set statistics 1000; analyze verbose; The I ran the query again but I still got the same result. Then I tried disabling sequential scans in postgresql.conf, restarted the p= ostmaster and did the query again: buns=3D# explain analyse select count(*) from cdr where cust_id =3D 9920111= 0 and bill_id is null; 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=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20 ---------------------------------------------------------------------------= ------------------------------------------------------- Aggregate (cost=3D913498.60..913498.60 rows=3D1 width=3D0) (actual time= =3D48387.91..48387.91 rows=3D1 loops=3D1) -> Index Scan using cdr_ix3 on cdr (cost=3D0.00..913453.49 rows=3D1804= 1 width=3D0) (actual time=3D48387.89..48387.89 rows=3D0 loops=3D1) Index Cond: (cust_id =3D 99201110) Filter: (bill_id IS NULL) Total runtime: 48388.47 msec (5 rows) The computed cost of using the index was a factor of 10 higher which I pres= ume is why the query planner wasn't using the index, but it ran in half the= time. So I guess I need to know how to alter the statistics collection so = that the index will get used. I gather that index columns that occur in mor= e than "a few" percent of the table cause the query planner to not use the = index. Does that mean I won't be able to get the query planner to ever use = the cust_id index for that customer or can I tune some parameters to alter = that? Any suggestions appreciated. Thanks, David David Witham Telephony Platforms Architect Unidial, Australia