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@xxxxxxx] > Sent: September 24, 2007 8:51 PM > To: Carlo Stonebanks > Cc: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: 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@xxxxxxx> wrote in message > > news:1190616376.17050.51.camel@xxxxxxxxxxxxxxxxxxxxxxx > > > 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 > > ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
