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

Re: hashjoin chosen over 1000x faster plan

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: hashjoin chosen over 1000x faster plan
Date: 2007-10-10 18:54:52
Message-ID: 1192042492.4233.334.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 2007-10-10 at 13:30 -0500, Kevin Grittner wrote:
> >>> On Wed, Oct 10, 2007 at  1:07 PM, in message <20980(dot)1192039650(at)sss(dot)pgh(dot)pa(dot)us>,
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote: 
> > Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
> >> Basically the planner doesn't ever optimise for the possibility of the
> >> never-executed case because even a single row returned would destroy
> >> that assumption. 
> > 
> > It's worse than that: the outer subplan *does* return some rows.
> > I suppose that all of them had NULLs in the join keys, which means
> > that (since 8.1 or so) nodeMergejoin discards them as unmatchable.
> > Had even one been non-NULL the expensive subplan would have been run.
>  
> Well, this query is run tens of thousands of times per day by our web
> application; less than one percent of those runs would require the
> subplan.  (In my initial post I showed counts to demonstrate that 1%
> of the rows had a non-NULL value and, while I wouldn't expect the
> planner to know this, these tend to be clustered on a lower
> percentage of cases.)  If the philosophy of the planner is to go for
> the lowest average cost (versus lowest worst case cost) shouldn't it
> use the statistics for to look at the percentage of NULLs?

But the planner doesn't work on probability. It works on a best-guess
selectivity, as known at planning time.

That's why dynamic planning was invented, which we don't do yet. Don't
hold your breath either.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


In response to

Responses

pgsql-performance by date

Next:From: Simon RiggsDate: 2007-10-10 19:01:03
Subject: Re: hashjoin chosen over 1000x faster plan
Previous:From: Kevin GrittnerDate: 2007-10-10 18:30:13
Subject: Re: hashjoin chosen over 1000x faster plan

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