From: David Wheeler <david@kineticode.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: PL/pgSQL Loop Vs. Batch Update
Date: Tue, 25 Apr 2006 19:27:48 -0700
Message-ID: <8FE57896-2F9B-4838-857B-FA6CC2EE162A@kineticode.com> (raw)
In-Reply-To: <11055.1146014387@sss.pgh.pa.us>
References: <FFF901A7-232A-4140-AE41-89ABE7766A85@kineticode.com>
<11055.1146014387@sss.pgh.pa.us>
On Apr 25, 2006, at 18:19, Tom Lane wrote:
> 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.
Well, the rows are indexed (I forgot to include the indexes in my
first post), and given that each entry_id has no more than ten
associated tag_ids, I would expect it to be quite fast, relying on
the primary key index to look up the entry_id first, and then the
associated tag_ids. But that's just a guess on my part, too. Perhaps
I should try a left outer join with tag_id IS NULL?
> 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).
Right, that makes sense.
> The basic idea is
>
> PREPARE foo(datatype, datatype, ...) AS SELECT ... $1 ... $2 ...
>
> EXPLAIN ANALYZE EXECUTE foo(value, value)
Just on a lark, I tried to get this to work:
try=# explain analyze EXECUTE foo(1, ARRAY
[600001,600002,600003,600004,600005,600006,600007]);
QUERY PLAN
------------------------------------------------------------------------
--------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=26.241..26.251
rows=1 loops=1)
Total runtime: 27.512 ms
(2 rows)
That's not much use. Is there no way to EXPLAIN ANALYZE this stuff?
Thanks Tom.
Best,
David
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: david@kineticode.com, tgl@sss.pgh.pa.us
Subject: Re: PL/pgSQL Loop Vs. Batch Update
In-Reply-To: <8FE57896-2F9B-4838-857B-FA6CC2EE162A@kineticode.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