Re: Performance tips

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Jeff Eckermann
Sent: Thursday, January 10, 2002 7:04 AM
To: andrew_perrin(at)unc(dot)edu; Doug McNaught
Cc: pgsql-general(at)postgresql(dot)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(at)unc(dot)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(at)unc(dot)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(at)postgresql(dot)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