CODEWITHSUNDEEP
sqlpostgresqlperformancequery-optimization
Dave McLean
Dave McLean

Reputation: 156

Postgres slow Trigger performance on DELETE

I have a table transactions:

                                                Table "public.transaction"
      Column       |           Type           | Collation | Nullable |                       Default
-------------------+--------------------------+-----------+----------+-----------------------------------------------------
 transaction_id    | bigint                   |           | not null | nextval('transaction_transaction_id_seq'::regclass)
 member_account_id | character varying        |           |          |
 reverses          | bigint                   |           |          |
Indexes:
    "pk_transaction" PRIMARY KEY, btree (transaction_id)
    "ix_transaction_member_account_id" btree (member_account_id)
    "ix_transaction_reverses" btree (reverses)
Foreign-key constraints:
    "fk_transaction_reverses_transaction" FOREIGN KEY (reverses) REFERENCES transaction(transaction_id)
Referenced by:
    TABLE "distributor_transaction_extras_es" CONSTRAINT "fk_distributor_transaction_extras_es_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "distributor_transaction_extras_tx" CONSTRAINT "fk_distributor_transaction_extras_tx_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "entry" CONSTRAINT "fk_entry_transaction_id_transaction" FOREIGN KEY (transaction_id) REFERENCES transaction(transaction_id)
    TABLE "transaction" CONSTRAINT "fk_transaction_reverses_transaction" FOREIGN KEY (reverses) REFERENCES transaction(transaction_id)

I'm attempting to delete from this table, first deleting all the entries related to the transactions.

begin;
    
delete from entry where account_id in (
    select account_id from account where territory = 'FR'
);
 
explain (analyze, buffers, timing) delete from transaction where member_account_id in (
    select external_account_id from account where territory = 'FR' limit 1
);

rollback;

which returns the following execution plan:

Delete on transaction  (cost=1.27..2168.99 rows=576 width=45) (actual time=0.207..0.209 rows=0 loops=1)
  Buffers: shared hit=68 dirtied=9
  ->  Nested Loop  (cost=1.27..2168.99 rows=576 width=45) (actual time=0.062..0.153 rows=21 loops=1)
        Buffers: shared hit=26 dirtied=9
        ->  HashAggregate  (cost=0.71..0.72 rows=1 width=54) (actual time=0.028..0.029 rows=1 loops=1)
              Group Key: ("ANY_subquery".external_account_id)::text
              Buffers: shared hit=1
              ->  Subquery Scan on "ANY_subquery"  (cost=0.00..0.70 rows=1 width=54) (actual time=0.024..0.025 rows=1 loops=1)
                    Buffers: shared hit=1
                    ->  Limit  (cost=0.00..0.69 rows=1 width=15) (actual time=0.019..0.019 rows=1 loops=1)
                          Buffers: shared hit=1
                          ->  Seq Scan on account  (cost=0.00..27744.49 rows=39931 width=15) (actual time=0.018..0.018 rows=1 loops=1)
                                Filter: ((territory)::text = 'FR'::text)
                                Rows Removed by Filter: 78
                                Buffers: shared hit=1
        ->  Index Scan using ix_transaction_member_account_id on transaction  (cost=0.56..2162.51 rows=576 width=21) (actual time=0.032..0.118 rows=21 loops=1)
              Index Cond: ((member_account_id)::text = ("ANY_subquery".external_account_id)::text)
              Buffers: shared hit=25 dirtied=9
Planning Time: 0.452 ms
Trigger for constraint fk_distributor_transaction_extras_tx_transaction_id_transaction: time=1.895 calls=21
Trigger for constraint fk_entry_transaction_id_transaction: time=0.694 calls=21
Trigger for constraint fk_distributor_transaction_extras_es_transaction_id_transaction: time=0.550 calls=21
Trigger for constraint fk_transaction_reverses_transaction: time=53410.870 calls=21
Execution Time: 53414.303 ms

The execution plan shows that 53410ms out of 53414ms (99.9925%) are spent in the trigger for the self-referential FK reverses.

It says that it's calling this trigger 21 times:

Trigger for constraint fk_transaction_reverses_transaction: time=53410.870 calls=21

which seems reasonable (there are 21 transactions associated with the first member_account_id) however it appears to take ~60 seconds which I was not expecting.

If I force postgres to seq_scan a full seq_scan on the table takes around 2.7 seconds:

set enable_indexscan = off;
SET enable_indexonlyscan = off;
SET enable_bitmapscan = off;
explain analyze select * from transaction where transaction_id = 10000000;

>
Gather  (cost=1000.00..588617.04 rows=1 width=143) (actual time=2728.717..2730.243 rows=0 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Seq Scan on transaction  (cost=0.00..587616.94 rows=1 width=143) (actual time=2698.268..2698.268 rows=0 loops=3)
        Filter: (transaction_id = 10000000)
        Rows Removed by Filter: 3361579
Planning Time: 0.125 ms
Execution Time: 2730.271 ms

This result is roughly consistent with the db doing a full seq_scan on transaction for each row that it's deleting. (21 * 2730ms == 57330ms and the time in the query plan was 53414.303 ms)

But why is it doing that?

These answers seem clear and point me the exact issue that I currently have:

However the resolution given is to add appropriate indexes to the child tables so that the delete is performant. My table already has indexes on both (reverses) and also (transaction_id).

How do I find out why this FK trigger is slow and how do I speed it up?

Upvotes: 4

Views: 4468

Answers (1)

Dave McLean
Dave McLean

Reputation: 156

Ok the issue was a index bloat. Running select * from transaction where reverses = 12 shows the query planner running a slow seq_scan rather than using the table.

Reindexing the table resolves the issue and a replay of the select now uses index_scan as you'd expect.

The following completes in ~0.04ms

reindex table transaction;

begin;
    
delete from entry where account_id in (
    select account_id from account where territory = 'FR'
);
 
explain (analyze, buffers, timing) delete from transaction where member_account_id in (
    select external_account_id from account where territory = 'FR' limit 1
);

rollback;

This is reasonably reassuring since this comment on this related question remains true:

https://dba.stackexchange.com/questions/37034/very-slow-delete-in-postgresql-workaround

After 5 years, I'm changing the accepted answer. Slow DELETEs are almost always caused by missing indexes on foreign keys that directly or indirectly reference the table being deleted from. Triggers that fire on DELETE statements can slow things down too, although the solution is almost always to make them run faster (e.g. by adding missing indexes) and almost never to disable all triggers.

I'm going to leave this question/answer incase it's of value to anyone else.

Upvotes: 1

Related Questions