Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 213F19FA182 for ; Thu, 14 Dec 2006 10:02:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 96090-06 for ; Thu, 14 Dec 2006 10:02:01 -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 0D6309FA311 for ; Thu, 14 Dec 2006 10:02:11 -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 09:02:08 -0500 Message-ID: <47668A1334CDBF46927C1A0DFEB223D39F7B01@mail.optiosoftware.com> In-Reply-To: <12988.1166052295@sss.pgh.pa.us> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] strange query behavior Thread-Index: AccfDeaiZxUDqWrwQ2KUhZbX1GJ5VwAeii+A References: <47668A1334CDBF46927C1A0DFEB223D39F79A5@mail.optiosoftware.com> <10648.1166037392@sss.pgh.pa.us> <47668A1334CDBF46927C1A0DFEB223D39F79FA@mail.optiosoftware.com> <12988.1166052295@sss.pgh.pa.us> From: "Tim Jones" To: "Tom Lane" Cc: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/363 X-Sequence-Number: 22419 18,273,008 rows in observationresults pg_stats: select * from pg_stats where tablename=3D'observationresults' and attname=3D'batteryidentifier'; schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation ------------+--------------------+-------------------+-----------+------ -----+------------+----------------------------------------------------- ---------------------+-------------------------------------------------- -----------------------+------------------------------------------------ -------------------------------------+------------- public | observationresults | batteryidentifier | 0 | 4 | 12942 | {437255,1588952,120420,293685,356599,504069,589910,693683,834990,854693} | {0.00133333,0.00133333,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001} | {3561,271263,556929,839038,1125682,1406538,1697589,1970463,2226781,25392 41,2810844} | 0.31779 thanks Tim Jones Healthcare Project Manager Optio Software, Inc. (770) 576-3555 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Wednesday, December 13, 2006 6:25 PM To: Tim Jones Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] strange query behavior=20 The large rowcount estimate makes it back off to a non-nestloop plan for the outer joins, and in this situation that's a loser. I'm actually not sure why they're not both too high --- with the rowcount estimate of 1362 for the inner scan in the first example, you'd expect about twice that for the join result. But the immediate problem is that in the case where it knows exactly what batteryidentifier is being probed for, it's still off by more than a factor of 100 on the rowcount estimate for observationresults. How many rows in observationresults, and may we see the pg_stats entry for observationresults.batteryidentifier? It's likely that the answer for you will be "raise the statistics target for observationresults and re-ANALYZE", but I'd like to gather more info about what's going wrong first. regards, tom lane