From: Ow Mun Heng <Ow.Mun.Heng@wdc.com>
To: Carlo Stonebanks <stonec.register@sympatico.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: REPOST: Nested loops row estimates always too high
Date: Tue, 25 Sep 2007 08:50:40 +0800
Message-ID: <1190681440.22945.8.camel@neuromancer.home.net> (raw)
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>
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
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: Ow.Mun.Heng@wdc.com, stonec.register@sympatico.ca
Subject: Re: REPOST: Nested loops row estimates always too high
In-Reply-To: <1190681440.22945.8.camel@neuromancer.home.net>
* 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