Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id B242B9F9FDA for ; Thu, 14 Dec 2006 14:30:28 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 60894-04 for ; Thu, 14 Dec 2006 14:30:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0B9559F9FB2 for ; Thu, 14 Dec 2006 14:30:16 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kBEIUFTa023150; Thu, 14 Dec 2006 13:30:15 -0500 (EST) To: "Tim Jones" cc: pgsql-performance@postgresql.org Subject: Re: strange query behavior In-reply-to: <22464.1166118515@sss.pgh.pa.us> 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> Comments: In-reply-to Tom Lane message dated "Thu, 14 Dec 2006 12:48:35 -0500" Date: Thu, 14 Dec 2006 13:30:15 -0500 Message-ID: <23149.1166121015@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/382 X-Sequence-Number: 22438 I wrote: > 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. For the sake of the archives: I looked into this, and it seems there's not anything going wrong other than the bogusly small n_distinct for observationresults. I'm assuming that battery.batteryidentifier is unique (stop me here, Tim, if not). That means that (a) there won't be any most-common-values statistics list for it, and (b) the n_distinct estimate should be pretty accurate. What happens in the multiple-batteryidentifier case is that eqjoinsel() doesn't have two MCV lists to work with, and so it bases its selectivity estimate on the larger n_distinct, which in this case is the accurate value from the battery table. So we come out with a decent estimate even though the other n_distinct is all wrong. What happens in the single-batteryidentifier case is that transitive equality deduction removes the battery.batteryidentifier = observationresults.batteryidentifier join condition altogether, replacing it with two restriction conditions batteryidentifier = 1177470. So eqjoinsel() is never called, and the join size estimate is just the product of the indexscan size estimates, and the scan estimate for observationresults is too high because its n_distinct is too small. So the bottom line is that eqjoinsel() is actually a bit more robust than one might have thought ... regards, tom lane