Skip site navigation (1) Skip section navigation (2)

Re: strange query behavior

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tim Jones" <TJones(at)optio(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange query behavior
Date: 2006-12-14 18:30:15
Message-ID: 23149.1166121015@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Alexander StauboDate: 2006-12-14 19:14:03
Subject: Re: New to PostgreSQL, performance considerations
Previous:From: Tom LaneDate: 2006-12-14 17:48:35
Subject: Re: strange query behavior

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group