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" <[EMAIL PROTECTED]> wrote in message > news:[EMAIL PROTECTED] > > 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 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
