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