Open durkie opened 1 year ago
Trigger IVM_trigger_del_before_32400811: time=42847.004 calls=1
This seems hung in IVM_trigger_delbefore..... 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.
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 inuser_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:
the executed query plan when deleting items from
user_item_id_pairs
: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!