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
