I appreciate all the comments.
I will perform some benchmarking before doing the rewrite to be certain of how it will impact performance. At the very least, I think can say for near-certain now that the indexes are not going to help me given the particular queries I am dealing with and limited number of records the temp tables will have combined with the limited number of times I will re-use them.
On Thu, Apr 22, 2010 at 10:42 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:I neglected to mention perhaps the most important point about the array method:
> The timings are similar, but the array returning case:
> *) runs in a single statement. If this is executed from the client
> that means less round trips
> *) can be passed around as a variable between functions. temp table
> requires re-query
> *) make some things easier/cheap such as counting the array -- you get
> to call the basically free array_upper()
> *) makes some things harder. specifically dealing with arrays on the
> client is a pain UNLESS you expand the array w/unnest() or use
> libpqtypes
> *) can nest. you can trivially nest complicated sets w/arrays
> *) does not require explicit transaction mgmt
*) does not rely on any temporary resources.
If you write a lot of plpsql, you will start to appreciate the
difference in execution time between planned and unplanned functions.
The first time you run a function in a database session, it has to be
parsed and planned. The planning time in particular for large-ish
functions that touch a lot of objects can exceed the execution time of
the function. Depending on _any_ temporary resources causes plan mgmt
issues because the database detects that a table in the old plan is
gone ('on commit drop') and has to re-plan. If your functions are
complex/long and you are counting milliseconds, then that alone should
be enough to dump any approach that depends on temp tables.
merlin
--
Eliot Gable
"We do not inherit the Earth from our ancestors: we borrow it from our children." ~David Brower
"I decided the words were too conservative for me. We're not borrowing from our children, we're stealing from them--and it's not even considered to be a crime." ~David Brower
"Esse oportet ut vivas, non vivere ut edas." (Thou shouldst eat to live; not live to eat.) ~Marcus Tullius Cicero
