Andrew,
You can get away with not even using the temp table just by specifying your
subquerry as a table in your select and joining it like any other table.
IE: select T1.MyField from Table1 T1, (Select T2.MyField2 FROM Table2 T2,
Table3 T3 WHERE T2.MyField3=T3.MyField3 AND T2.MyField4='Thing' AND
T3.MyField5=Number) T2 WHERE T1.MyField2=T2.MyField2;
I've found that this is super fast; I was able to bring a query which took
~30 seconds down to well under a second using this technique.
Thanks,
Peter Darley
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeff Eckermann
Sent: Thursday, January 10, 2002 7:04 AM
To: andrew_perrin@unc.edu; Doug McNaught
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance tips
One way to improve performance with queries like yours
is to select the subquery results into a temporary
table, and join against that (perhaps with an index
created on the temp table, if you get a lot of rows).
Ugly and messy, but can result in massive performance
improvements on a system with limited resources, even
compared with the "EXISTS" solution.
--- Andrew Perrin <andrew_perrin@unc.edu> wrote:
> Never mind - while I was writing the last message
> the vacuum analyze
> ended. No messages from vacuum analyze (just the
> VACUUM
> acknowledgement). I'm recreating the one
> user-created index (the rest are
> based on serials) now and will re-test queries.
>
> Thanks for everyone's help.
>
>
----------------------------------------------------------------------
> Andrew J Perrin - andrew_perrin@unc.edu -
> http://www.unc.edu/~aperrin
> Assistant Professor of Sociology, U of North
> Carolina, Chapel Hill
> 269 Hamilton Hall, CB#3210, Chapel Hill, NC
> 27599-3210 USA
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the
> unregister command
> (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
__________________________________________________
Do You Yahoo!?
Send FREE video emails in Yahoo! Mail!
http://promo.yahoo.com/videomail/
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
