Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 70DD8633E4C for ; Thu, 22 Apr 2010 17:57:23 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 71138-01 for ; Thu, 22 Apr 2010 20:57:06 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f196.google.com (mail-qy0-f196.google.com [209.85.221.196]) by mail.postgresql.org (Postfix) with ESMTP id 0C4C1632F9E for ; Thu, 22 Apr 2010 17:57:10 -0300 (ADT) Received: by mail-qy0-f196.google.com with SMTP id 34so4113262qyk.10 for ; Thu, 22 Apr 2010 13:57:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:received:message-id:subject :from:to:cc:content-type; bh=amY27Oo9U+bvb+JI2YvZ3PDMas8w9fcjSw0+M/v/Dy8=; b=gEF9VQKu2yYfwhAI4a48l17ZpI1924jKBuu/NqLCNvIhyPKZXU3AsM2Z4SXHWNrsH4 G8j/JyF7Sh4FOSDNQlZY2tiTgbxGTOvXgQRJ1bmTtiJVdi9Qf95ZDgcWEj6QaRe6V4wg rGYhGSHfX/K7IYVioWrpmvS/ENPpOO0co/rYc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=aorz30eBfEDR6ccxuYf61uLlSFfKmSq5Se5N+tb63m8YV14gvRZomHe9u9vJvntvUX U7IIONO77ML9+USbyfOXzLWJv8eZEdqx81MCfw8WBwmElbChOaekD2Q0dgAper/ph3Gj dH3HcBxHhHkXKIPVRurBzgtS77/VoF7x+iqNo= MIME-Version: 1.0 Received: by 10.229.37.65 with HTTP; Thu, 22 Apr 2010 13:57:08 -0700 (PDT) In-Reply-To: References: Date: Thu, 22 Apr 2010 16:57:08 -0400 X-Google-Sender-Auth: 52d033eae6d164eb Received: by 10.229.222.76 with SMTP id if12mr538767qcb.17.1271969828571; Thu, 22 Apr 2010 13:57:08 -0700 (PDT) Message-ID: Subject: Re: Replacing Cursors with Temporary Tables From: Eliot Gable To: Merlin Moncure Cc: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016361e7f34cc2acf0484d98d02 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.801 tagged_above=-10 required=5 tests=BAYES_50=0.8, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/294 X-Sequence-Number: 38544 --0016361e7f34cc2acf0484d98d02 Content-Type: text/plain; charset=ISO-8859-1 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 wrote: > On Thu, Apr 22, 2010 at 10:11 AM, Merlin Moncure > 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 --0016361e7f34cc2acf0484d98d02 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
I appreciate all the comments.

I will per= form some benchmarking before doing the rewrite to be certain of how it wil= l impact performance. At the very least, I think can say for near-certain n= ow 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 co= mbined 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:
> *) =A0runs in a single statement. =A0If this is executed from the clie= nt
> that means less round trips
> *) can be passed around as a variable between functions. =A0temp 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. =A0specifically dealing with arrays on th= e
> 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 arr= ay 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. =A0The planning time in particular for large-ish
functions that touch a lot of objects can exceed the execution time of
the function. =A0Depending 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. =A0 If your functions a= re
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
<= br>"We do not inherit the Earth from our ancestors: we borrow it from = our children." ~David Brower

"I decided the words were to= o 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 e= at to live; not live to eat.) ~Marcus Tullius Cicero
--0016361e7f34cc2acf0484d98d02--