From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: hashjoin chosen over 1000x faster plan
Date: Wed, 10 Oct 2007 16:32:57 -0400
Message-ID: <23650.1192048377@sss.pgh.pa.us> (raw)
In-Reply-To: <470CE34D.EE98.0025.0@wicourts.gov>
References: <470B99BE.EE98.0025.0@wicourts.gov>
<1191997904.4233.125.camel@ebony.site>
<470C9813.EE98.0025.0@wicourts.gov>
<1192035955.4233.306.camel@ebony.site>
<1192035955.4233.306.camel@ebony.site>
<20980.1192039650@sss.pgh.pa.us>
<470CD3E3.EE98.0025.0@wicourts.gov>
<1192042492.4233.334.camel@ebony.site>
<470CE34D.EE98.0025.0@wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> The point I'm trying to make is that at planning time the
> pg_statistic row for this "Charge"."reopHistSeqNo" column showed
> stanullfrac as 0.989; it doesn't seem to have taken this into account
> when making its guess about how many rows would be joined when it was
> compared to the primary key column of the "CaseHist" table.
It certainly does take nulls into account, but the estimate of resulting
rows was still nonzero; and even if it were zero, I'd be very hesitant
to make it choose a plan that is fast only if there were exactly zero
such rows and is slow otherwise. Most of the complaints we've had about
issues of this sort involve the opposite problem, ie, the planner is
choosing a plan that works well for few rows but falls down because
reality involves many rows. "Fast-for-few-rows" plans are usually a lot
more brittle than the alternatives in terms of the penalty you pay for
too many rows, and so putting a thumb on the scales to push it towards a
"fast" corner case sounds pretty unsafe to me.
As Simon notes, the only technically sound way to handle this would
involve run-time plan changeover, which is something we're not nearly
ready to tackle.
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, Kevin.Grittner@wicourts.gov, simon@2ndquadrant.com
Subject: Re: hashjoin chosen over 1000x faster plan
In-Reply-To: <23650.1192048377@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