Re: PL/pgSQL Loop Vs. Batch Update

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

> 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?

Scratch that:

try=# delete from entry_coll_tag ;
DELETE 7
try=# vacuum;
analyze;
VACUUM
try=# analyze;
ANALYZE
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=0.193..0.284 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=0.022..0.022 rows=0
loops=1)
            Index Cond: (entry_id = $1)
Trigger for constraint entry_coll_tag_entry_id_fkey: time=0.858 calls=7
Trigger for constraint entry_coll_tag_tag_id_fkey: time=0.805 calls=7
Total runtime: 3.266 ms
(8 rows)

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

So my tests are calling this query six hundred times. Could it be
that it just gets slower over time because the database needs to be
vacuumed? Or perhaps pg_autovacuum is kicking in during execution and
*that* slows things down?

Thanks,

David