Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 320A19FA02C for ; Thu, 14 Dec 2006 15:19:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 56551-04 for ; Thu, 14 Dec 2006 15:19:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.optiosoftware.com (mail.optio.com [192.216.93.10]) by postgresql.org (Postfix) with ESMTP id B6D209F9F47 for ; Thu, 14 Dec 2006 15:19:06 -0400 (AST) X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Subject: Re: strange query behavior Date: Thu, 14 Dec 2006 14:18:55 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D39F7C5D@mail.optiosoftware.com> In-Reply-To: <22464.1166118515@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] strange query behavior Thread-Index: AccfqBUp2FeB8fmgT4ufkpsqr8j4UAADDo9A References: <47668A1334CDBF46927C1A0DFEB223D39F79A5@mail.optiosoftware.com> <10648.1166037392@sss.pgh.pa.us> <47668A1334CDBF46927C1A0DFEB223D39F79FA@mail.optiosoftware.com> <12988.1166052295@sss.pgh.pa.us> <47668A1334CDBF46927C1A0DFEB223D39F7B01@mail.optiosoftware.com> <22464.1166118515@sss.pgh.pa.us> From: "Tim Jones" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/384 X-Sequence-Number: 22440 ok thanks Tom I will alter the statistics and re-analyze the table. Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Thursday, December 14, 2006 12:49 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] strange query behavior=20 "Tim Jones" writes: > 18,273,008 rows in observationresults > [ and n_distinct =3D 12942 ] OK, so the estimated rowcounts are coming from those two numbers. It's notoriously hard to get a decent n_distinct estimate from a small sample :-(, and I would imagine the number of batteryidentifiers is really a lot more than 12942? What you need to do is increase the statistics target for observationresults.batteryidentifier (see ALTER TABLE) and re-ANALYZE and see if you get a saner n_distinct in pg_stats. I'd try 100 and then 1000 as target. Or you could just increase the global default target (see postgresql.conf) but that might be overkill. It's still a bit odd that the case with two batteryidentifiers was estimated fairly accurately when the other wasn't; I'll go look into that. But in any case you need better stats if you want good plans. regards, tom lane