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.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 4: Have you searched our list archives? http://archives.postgresql.org
