Subject: Re: REPOST: Nested loops row estimates always too high
From: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <fd8ukj$s38$1@news.hub.org>
References: <fcu215$2em3$1@news.hub.org>
	<1190616376.17050.51.camel@neuromancer.home.net>
	<fd8ukj$s38$1@news.hub.org>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Tue, 25 Sep 2007 08:50:40 +0800
Message-Id: <1190681440.22945.8.camel@neuromancer.home.net>
Mime-Version: 1.0

On Mon, 2007-09-24 at 14:12 -0400, Carlo Stonebanks wrote:
> Has anyone offered any answers to you? No one else has replied to this post.

Overestimate of selectivity. I guess it's mainly due to my one to many
table relationships. I've tried everything from concatenated join
columns and indexing it to creating all sorts of indexes and splitting
the (1) tables into multiple tables and upping the indexes to 1000 and
turning of nestloops/enabling geqo/ tweaking the threshold/effort and
much much more (as much as I was asked to/suggested to) but still no
luck.

In my case, the individual queries were fast. So, In then end, I made a
SRF and used the SRFs to join each other. This worked better.


> 
> 
> "Ow Mun Heng" <Ow.Mun.Heng@wdc.com> wrote in message 
> news:1190616376.17050.51.camel@neuromancer.home.net...
> > On Thu, 2007-09-20 at 11:02 -0400, Carlo Stonebanks wrote:
> >> (SORRY FOR THE REPOST, I DON'T SEE MY ORIGINAL QUESTION OR ANY ANSWERS 
> >> HERE)
> >>
> >> I am noticing that my queries are spending a lot of time in nested loops.
> >> The table/index row estimates are not bad, but the nested loops can be 
> >> off
> >> by a factor of 50. In any case, they are always too high.
> >>
> >> Are the over-estimations below significant, and if so, is this an 
> >> indication
> >> of a general configuration problem?
> > Sounds much like the issue I was seeing as well.
> >
> >>
> >> Unique  (cost=67605.91..67653.18 rows=4727 width=16) (actual
> >> time=8634.618..8637.918 rows=907 loops=1)
> >
> > You can to rewrite the queries to individual queries to see it if helps.
> >
> > In my case, I was doing
> >
> > select a.a,b.b,c.c from
> > (select a from x where) a <--- Put as a SRF
> > left join (
> > select b from y where ) b <--- Put as a SRF
> > on a.a = b.a
> >
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> > 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
> 
>                 http://www.postgresql.org/about/donate