Greg/Tom/Josh, Thanks for your comments about this problem...very much appreciated. We have resolve the issue by re-doing the query partly based on your advice and partly just spending more time in analysis. There's one oddball thing we turned up which I'm including below in the full series of steps we did to optimize things around the "explain" functionality. 1) The original query (89 rows returned) with an EXPLAIN ANALYZE takes over 300 secs. Without the explain analyze, it runs in 45 seconds. With nested loops disabled (and hence forcing a merge), it completes in under 1 second. The outstanding question here is why does the explain analyze take (quite a bit) longer than just executing the query? 2) Removing the LEFT JOIN (89 rows returned) - lowered query execution time to 37 secs 3) Changing the 3 occurrences of (prop_key LIKE 'string...') to = - row estimate improved from 1 to 286 - query execution time still at 37 secs 4) Adding a DISTINCT to the IN subquery on - records returned in subquery changes from 2194 to 112. - ... web_user.web_user_id IN (SELECT DISTINCT web_user_id - query execution time falls to 1 sec. We then ran a totally unscientific test (unscientific because this was on a different machine, different OS, etc.) just to see if there was any difference between newer versions of Postgres and that which is bundled with the application. Using 8.3 on a Windows desktop - original query executes in 7 secs - improved query executes in 6 secs So it seems there may well be some changes in newer versions which we can take advantage of. More fuel to look into upgrading the embedded database version ;) Again, thanks all for the input. Regards Dave > -----Original Message----- > From: gsstark@xxxxxxxxx [mailto:gsstark@xxxxxxxxx] On Behalf > Of Greg Stark > Sent: June 25, 2009 5:30 PM > To: Tom Lane > Cc: Dave North; pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: Nested Loop "Killer" on 8.1 > > On Thu, Jun 25, 2009 at 10:05 PM, Tom Lane<tgl@xxxxxxxxxxxxx> wrote: > > > > Uh, it appears to me the string *does* contain _ > characters; perhaps > > the OP has neglected to escape those? > > Sigh. Indeed. > > -- > greg > http://mit.edu/~gsstark/resume.pdf > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
