Re: PL/pgSQL Loop Vs. Batch Update

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