From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Wheeler <david@kineticode.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Date: Tue, 25 Apr 2006 21:19:47 -0400
Message-ID: <11055.1146014387@sss.pgh.pa.us> (raw)
In-Reply-To: <FFF901A7-232A-4140-AE41-89ABE7766A85@kineticode.com>
References: <FFF901A7-232A-4140-AE41-89ABE7766A85@kineticode.com>
David Wheeler <david@kineticode.com> writes:
> This post is longish and has a bit of code, but here's my question up-
> front: Why are batch queries in my PL/pgSQL functions no faster than
> iterating over a loop and executing a series of queries for each
> iteration of the loop?
You'd really have to look at the plans generated for each of the
commands in the functions to be sure. A knee-jerk reaction is to
suggest that that NOT IN might be the core of the problem, but it's
only a guess.
It's a bit tricky to examine the behavior of a parameterized query,
which is what these will all be since they depend on local variables
of the plpgsql function (which are passed as parameters to the main
SQL executor). The basic idea is
PREPARE foo(datatype, datatype, ...) AS SELECT ... $1 ... $2 ...
EXPLAIN ANALYZE EXECUTE foo(value, value)
regards, tom lane
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: tgl@sss.pgh.pa.us, david@kineticode.com
Subject: Re: PL/pgSQL Loop Vs. Batch Update
In-Reply-To: <11055.1146014387@sss.pgh.pa.us>
* 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