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 37AD8D1E0F4 for ; Wed, 18 Feb 2004 06:23:35 +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 87122-07 for ; Wed, 18 Feb 2004 02:23:33 -0400 (AST) Received: from ozimelb03.ozicom.com (unknown [203.185.247.100]) by svr1.postgresql.org (Postfix) with ESMTP id 1BF38D1E0E1 for ; Wed, 18 Feb 2004 02:23:32 -0400 (AST) Content-class: urn:content-classes:message Subject: Re: 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 17:23:27 +1100 X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [SQL] Indexes and statistics Thread-Index: AcP13Xpwr+foq9wxS3y9p0daY+gsNgAB5h0w From: "David Witham" To: "Tom Lane" Cc: X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/243 X-Sequence-Number: 16933 Tom, I'm running PostgreSQL 7.3.2 on Red Hat Linux 7.3 with 512Mb RAM. The table definition is: Table "public.cdr" Column | Type | Modifiers=20 -----------------------+-----------------------+----------- carrier_id | integer | not null file_id | integer | not null service_num | character varying(10) | not null day | date | not null time | integer | not null destination | character varying(20) | not null duration | integer | not null charge_wholesale | numeric(8,2) | not null charge_band_id | integer |=20 charge_retail | numeric(8,2) | not null rate_plan_id | integer | not null item_code | integer | not null cust_id | integer | not null bill_id | integer |=20 prefix | character varying(12) |=20 charge_wholesale_calc | numeric(8,2) |=20 Indexes: cdr_ix1 btree ("day"), cdr_ix2 btree (service_num), cdr_ix3 btree (cust_id), cdr_ix4 btree (bill_id), cdr_ix5 btree (carrier_id), cdr_ix6 btree (file_id) Does this make it a "wide" table? The data arrives ordered by service_num, day, time. This customer has one p= rimary service_num that most of the calls are made from. Therefore each day= a clump of CDRs will be loaded for that customer, interspersed with CDRs f= rom all the other customers. Therefore the distribution of records for a se= rvice_num is clumpy but evenly distributed throughout the table. For a cust= omer with a single primary number, this result applies to the customer as a= whole. For a customer with many service_num's the result is a little more = doubtful depending on whether their service_num's arrive sequentially or no= t. This would not necessarily be the case. I hope this makes sense. Does it help any? Thanks, David -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Wednesday, 18 February 2004 16:10 To: David Witham Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Indexes and statistics=20 "David Witham" writes: > One of the customers is quite large (8.3% of the records): Hmm. Unless your rows are quite wide, a random sampling of 8.3% of the table would be expected to visit every page of the table, probably several times. So the planner's cost estimates do not seem out of line to me; an indexscan *should* be slow. The first question to ask is why the deviation from reality. Are the rows for that customer ID likely to be physically concentrated into a limited number of physical pages? Do you have so much RAM that the whole table got swapped in, eliminating the extra I/O that the planner is expecting? regards, tom lane