From: Eliot Gable <egable+pgsql-performance@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Replacing Cursors with Temporary Tables
Date: Thu, 22 Apr 2010 16:57:08 -0400
Message-ID: <m2gbf6923ed1004221357ua9ab54a3s92dfdbb59cfef832@mail.gmail.com> (raw)
In-Reply-To: <q2yb42b73151004220742gbb7ae4adyc3781cedf98e8559@mail.gmail.com>
References: <n2ybf6923ed1004211316ib844d82bn7a63bd3292ca80a0@mail.gmail.com>
<n2sb42b73151004220711mc2135db6heccf53ec1fb0136e@mail.gmail.com>
<q2yb42b73151004220742gbb7ae4adyc3781cedf98e8559@mail.gmail.com>
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@gmail.com> wrote:
> On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure <mmoncure@gmail.com>
> wrote:
> > 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
>
>
> I neglected to mention perhaps the most important point about the array
> method:
> *) 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: egable+pgsql-performance@gmail.com, mmoncure@gmail.com
Subject: Re: Replacing Cursors with Temporary Tables
In-Reply-To: <m2gbf6923ed1004221357ua9ab54a3s92dfdbb59cfef832@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox