Re: PL/pgSQL Loop Vs. Batch Update

On Apr 25, 2006, at 19:36, Tom Lane wrote:

> Try one of the actual queries from the plpgsql function.

Here we go:

try=# PREPARE foo(int, int[], int) AS
try-# INSERT INTO entry_coll_tag (entry_id, tag_id, ord )
try-# SELECT $1, $2[gs.ser], gs.ser + $3
try-# FROM   generate_series(1, array_upper($2, 1)) AS gs(ser)
try-# WHERE  $2[gs.ser] NOT IN (
try(#     SELECT tag_id FROM entry_coll_tag ect2
try(#     WHERE entry_id = $1
try(# );
PREPARE
try=# explain analyze execute foo(100100, ARRAY
[600001,600002,600003,600004,600005,600006,600007], 0);

QUERY PLAN
------------------------------------------------------------------------
------------------------------------------------------------------------
-
Function Scan on generate_series gs  (cost=7.78..25.28 rows=500
width=4) (actual time=80.982..81.265 rows=7 loops=1)
    Filter: (NOT (hashed subplan))
    SubPlan
      ->  Index Scan using idx_entry_tag_ord on entry_coll_tag ect2
(cost=0.00..7.77 rows=5 width=4) (actual time=80.620..80.620 rows=0
loops=1)
            Index Cond: (entry_id = $1)
Trigger for constraint entry_coll_tag_entry_id_fkey: time=3.210 calls=7
Trigger for constraint entry_coll_tag_tag_id_fkey: time=4.412 calls=7
Total runtime: 158.672 ms
(8 rows)

Actually looks pretty good to me. Although is generate_series() being
rather slow?

Thanks,

David