Re: PL/pgSQL Loop Vs. Batch Update

On May 2, 2006, at 16:52, David Wheeler wrote:

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

Bah, dammit, there were no rows in that relevant table. Please
disregard my previous EXPLAIN ANALYZE posts.

I've re-run my script and populated it with 549,815 rows. *Now* let's
see what we've got:


try=# VACUUM;
VACUUM
try=# ANALYZE;
ANALYZE
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=9.68..27.18 rows=500
width=4) (actual time=0.965..1.055 rows=7 loops=1)
    Filter: (NOT (hashed subplan))
    SubPlan
      ->  Index Scan using idx_entry_tag_ord on entry_coll_tag ect2
(cost=0.00..9.66 rows=8 width=4) (actual time=0.844..0.844 rows=0
loops=1)
            Index Cond: (entry_id = $1)
Trigger for constraint entry_coll_tag_entry_id_fkey: time=3.872 calls=7
Trigger for constraint entry_coll_tag_tag_id_fkey: time=3.872 calls=7
Total runtime: 12.797 ms
(8 rows)

try=# delete from entry_coll_tag where entry_id = 100100;
DELETE 7
try=# explain analyze execute foo(100100, ARRAY
[600001,600002,600003,600004,600005,600006,600007], 0);

QUERY PLAN
------------------------------------------------------------------------
-----------------------------------------------------------------------
Function Scan on generate_series gs  (cost=9.68..27.18 rows=500
width=4) (actual time=0.117..0.257 rows=7 loops=1)
    Filter: (NOT (hashed subplan))
    SubPlan
      ->  Index Scan using idx_entry_tag_ord on entry_coll_tag ect2
(cost=0.00..9.66 rows=8 width=4) (actual time=0.058..0.058 rows=0
loops=1)
            Index Cond: (entry_id = $1)
Trigger for constraint entry_coll_tag_entry_id_fkey: time=0.542 calls=7
Trigger for constraint entry_coll_tag_tag_id_fkey: time=0.590 calls=7
Total runtime: 2.118 ms
(8 rows)

Damn, that seems pretty efficient. I wonder if it's the other
function, then. I'll have to work EXPLAIN ANALYZEing _it_.

Best,

David