From: Tom Lane <tgl@sss.pgh.pa.us>
To: Tim Jones <TJones@optio.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: strange query behavior
Date: Thu, 14 Dec 2006 13:30:15 -0500
Message-ID: <23149.1166121015@sss.pgh.pa.us> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, TJones@optio.com
Subject: Re: strange query behavior
In-Reply-To: <23149.1166121015@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox