Any reason DELETE operations would take an especially long time?

[durkie]

Hi there -- thanks for this really useful extension. I noticed in a view that I recently created that it seems like DELETE operations are going quite slow. Is this common? I'm using version 1.5.

The view (basically getting each item_id and the number of times it occurs in user_item_id_pairs):
SELECT create_immv('completed_item_ids_immv', 'SELECT DISTINCT user_item_id_pairs.item_id, count(user_item_id_pairs.item_id) AS count FROM user_item_id_pairs GROUP BY user_item_id_pairs.item_id');

The base table:

postgres=> \d user_item_id_pairs
                              Table "public.user_item_id_pairs"
   Column   |  Type  | Collation | Nullable |                     Default                      
------------+--------+-----------+----------+--------------------------------------------------
 id         | bigint |           | not null | nextval('user_item_id_pairs_id_seq'::regclass)
 item_id    | bigint |           | not null | 
 user_id    | bigint |           |          | 
Indexes:
    "user_item_id_pairs_pkey" PRIMARY KEY, btree (id)
    "index_user_item_id_pairs_on_user_id" btree (user_id)
    "index_user_item_id_pairs_on_item_id_and_user_id" UNIQUE, btree (item_id, user_id)

the executed query plan when deleting items from user_item_id_pairs:

postgres=> explain (analyze, buffers, verbose) delete FROM "user_item_id_pairs" WHERE "user_item_id_pairs"."item_id" IN (129601734, 1032902619, 1032902620) AND "user_item_id_pairs"."user_id" = 4;
                                                                                       QUERY PLAN                                                                                       
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Delete on public.user_item_id_pairs  (cost=0.57..17.77 rows=0 width=0) (actual time=42847.048..42847.049 rows=0 loops=1)
   Buffers: shared hit=12
   ->  Index Scan using index_user_item_id_pairs_on_item_id_and_user_id on public.user_item_id_pairs  (cost=0.57..17.77 rows=1 width=6) (actual time=0.035..0.036 rows=0 loops=1)
         Output: ctid
         Index Cond: ((user_item_id_pairs.item_id = ANY ('{129601734,1032902619,1032902620}'::bigint[])) AND (user_item_id_pairs.user_id = 4))
         Buffers: shared hit=12
 Query Identifier: -3375582956699710130
 Planning Time: 0.073 ms
 Trigger IVM_trigger_del_after_32400815: time=0.369 calls=1
 Trigger IVM_trigger_del_before_32400811: time=42847.004 calls=1
 Execution Time: 42847.446 ms
(11 rows)

Time: 42848.536 ms (00:42.849)

Every part of this query seems to execute pretty quickly and use indexes where available, but that delete trigger is definitely getting hung up on something. I don't have much insight in to what it's actually doing though. The particular query plan that I pasted above returned 0 rows from the index scan, but that was just for testing purposes and would at most return 3 rows in production.

During the 5 times I ran this query, the delete operation took between 9 and 42 seconds (28, 16, 40, 9, 42 seconds). A bulk insert of 3 rows in to this same user_item_id_pairs table takes 150-250ms.

Any ideas of things to try?

Thanks!

[yugo-n]

Trigger IVM_trigger_del_before_32400811: time=42847.004 calls=1

This seems hung in IVM_trigger_del_before_..... In this trigger function, an exclusive lock is taken if the view has an aggregate or DISTINCT for keeping consistency under concurrent transactions. Therefore, if there is a transaction that update or delete the base table, other transaction that tries update or delete on the same table would wait for the former transaction finishes. I would like to improve the concurrency performance, but unfortunately it is not yet resolved.

If you have concurrent transactions in your test environment, I think this would be the reason.