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@gmail.com [mailto:gsstark@gmail.com] On Behalf
> Of Greg Stark
> Sent: June 25, 2009 5:30 PM
> To: Tom Lane
> Cc: Dave North; pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Nested Loop "Killer" on 8.1
>
> On Thu, Jun 25, 2009 at 10:05 PM, Tom Lane<tgl@sss.pgh.pa.us> 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
>