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: <53877A06E07146D2898619A7025C6107@serenity>
References: <fcu215$2em3$1@news.hub.org>
	<1190616376.17050.51.camel@neuromancer.home.net>
	<fd8ukj$s38$1@news.hub.org>
	<1190681440.22945.8.camel@neuromancer.home.net>
	<53877A06E07146D2898619A7025C6107@serenity>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Tue, 25 Sep 2007 13:15:58 +0800
Message-Id: <1190697358.19529.5.camel@neuromancer.home.net>
Mime-Version: 1.0

On Tue, 2007-09-25 at 00:53 -0400, Carlo Stonebanks wrote:
> My problem is that I think that SRF's are causing my problems. The SRF's
> gets an automatic row estimate of 1000 rows.

That's correct. That's what I see too though I may return 10K rows of
data. (min 10 columns)
But It's way faster than the normal joins I do.

> I'm really disappointed - SRF's are a great way to place the enterprise's
> db-centric business logic at the server.

Actually, I think in general, nested Loops, while evil, are just going
to be around. Even in MSSQL, when I'm pulling from, the nested loops are
many and I presume it's cos of the 8x SMP and the multiGB ram which is
making the query better.


> 
> Carlo
> 
> -----Original Message-----
> From: Ow Mun Heng [mailto:Ow.Mun.Heng@wdc.com] 
> Sent: September 24, 2007 8:51 PM
> To: Carlo Stonebanks
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] REPOST: Nested loops row estimates always too high
> 
> 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
> 
>