Re: REPOST: Nested loops row estimates always too high

My problem is that I think that SRF's are causing my problems. The SRF's
gets an automatic row estimate of 1000 rows. Add a condition to it, the
planner guesses 333 rows. Even at 333, this is an overestimate of the number
of rows returned.

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

Carlo

-----Original Message-----
From: Ow Mun Heng [mailto:Ow(dot)Mun(dot)Heng(at)wdc(dot)com]
Sent: September 24, 2007 8:51 PM
To: Carlo Stonebanks
Cc: pgsql-performance(at)postgresql(dot)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(dot)Mun(dot)Heng(at)wdc(dot)com> wrote in message
> news:1190616376(dot)17050(dot)51(dot)camel(at)neuromancer(dot)home(dot)net(dot)(dot)(dot)
> > 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