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

Re: Inefficient queryplan for query with

From: Ron <rjpeace(at)earthlink(dot)net>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Inefficient queryplan for query with
Date: 2005-08-27 15:07:37
Message-ID: 6.2.3.4.0.20050827104222.05e36150@pop.earthlink.net (view raw or flat)
Thread:
Lists: pgsql-performance
At 10:27 AM 8/27/2005, Tom Lane wrote:
>Arjen van der Meijden <acmmailing(at)tweakers(dot)net> writes:
> > But appareantly there is a bug in the explain mechanism of the 8.1devel
> > I'm using (I downloaded a nightly 25 august somewhere in the morning
> > (CEST)), since it returned:
> > ERROR:  bogus varno: 9
>
>Yeah, someone else sent in a test case for this failure (or at least one
>with a similar symptom) yesterday.  I'll try to fix it today.
>
> > Is a nested loop normally so much (3x) more costly than a hash join? Or
> > is it just this query that gets estimated wronly?
>
>There's been some discussion that we are overestimating the cost of
>nestloops in general, because we don't take into account that successive
>scans of the inner relation are likely to find many pages already in
>cache from the earlier scans.  So far no one's come up with a good cost
>model to use for this, though.
>
>                         regards, tom lane
It certainly seems common in the EXPLAIN ANALYZE output I see that 
the (estimated) cost of Nested Loop is far higher than the actual 
time measured.

What happened when someone tried the naive approach of telling the 
planner to estimate the cost of a nested loop based on fitting 
whatever entities are involved in the nested loop in RAM as much as 
possible?  When there are multiple such mappings, use whichever one 
results in the lowest cost for the NL in question.

Clearly, this should lead to an underestimate of the cost of the 
constant of operation involved, but since nested loops have the only 
polynomial growth function of the planner's choices, NL's should 
still have a decent chance of being more expensive than other choices 
under most circumstances.

In addition, if those costs are based on actual measurements of how 
long it takes to do such scans then the estimated cost has a decent 
chance of being fairly accurate under such circumstances.

It might not work well, but it seems like a reasonable first attempt 
at a solution?
Ron Peacetree



In response to

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2005-08-27 15:26:03
Subject: Re: Weird performance drop after VACUUM
Previous:From: Tom LaneDate: 2005-08-27 15:05:01
Subject: Re: Weird performance drop after VACUUM

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