explain.depesz.com

PostgreSQL's explain analyze made readable

Result: KEBH

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 17,732.466 111,384.273 ↓ 0.0 0 1

Delete on audit_evs_all aea (cost=2,610,761.97..4,787,089.41 rows=1,790,464 width=38) (actual time=111,384.273..111,384.273 rows=0 loops=1)

2. 12,616.266 93,651.807 ↑ 164.0 10,917 1

Hash Join (cost=2,610,761.97..4,787,089.41 rows=1,790,464 width=38) (actual time=76,044.792..93,651.807 rows=10,917 loops=1)

  • Hash Cond: (aea.audit_ev_id = aea2.audit_ev_id)
3. 79,245.899 79,245.899 ↑ 1.0 30,603,686 1

Seq Scan on audit_evs_all aea (cost=0.00..1,779,047.96 rows=30,933,896 width=14) (actual time=0.168..79,245.899 rows=30,603,686 loops=1)

4. 5.923 1,789.642 ↑ 164.0 10,917 1

Hash (cost=2,574,393.17..2,574,393.17 rows=1,790,464 width=40) (actual time=1,789.642..1,789.642 rows=10,917 loops=1)

  • Buckets: 2,097,152 Batches: 2 Memory Usage: 16,771kB
5. 3.916 1,783.719 ↑ 164.0 10,917 1

Subquery Scan on aea2 (cost=21,063.16..2,574,393.17 rows=1,790,464 width=40) (actual time=1,709.721..1,783.719 rows=10,917 loops=1)

6. 349.503 1,779.803 ↑ 164.0 10,917 1

HashSetOp Except (cost=21,063.16..2,556,488.53 rows=1,790,464 width=12) (actual time=1,709.694..1,779.803 rows=10,917 loops=1)

7. 72.065 1,430.300 ↑ 4.6 782,795 1

Append (cost=21,063.16..2,547,582.16 rows=3,562,546 width=12) (actual time=290.564..1,430.300 rows=782,795 loops=1)

8. 42.850 469.240 ↑ 4.5 396,856 1

Subquery Scan on *SELECT* 1 (cost=21,063.16..1,246,311.24 rows=1,790,464 width=12) (actual time=290.562..469.240 rows=396,856 loops=1)

9. 314.377 426.390 ↑ 4.5 396,856 1

Bitmap Heap Scan on audit_evs_all aea_1 (cost=21,063.16..1,228,406.60 rows=1,790,464 width=8) (actual time=290.559..426.390 rows=396,856 loops=1)

  • Recheck Cond: (when_created <= '2020-08-26 07:15:21.616+00'::timestamp with time zone)
  • Heap Blocks: exact=16,298
10. 112.013 112.013 ↓ 1.0 1,827,526 1

Bitmap Index Scan on idx_audit_evs_all_when_created (cost=0.00..20,615.54 rows=1,790,464 width=0) (actual time=112.013..112.013 rows=1,827,526 loops=1)

  • Index Cond: (when_created <= '2020-08-26 07:15:21.616+00'::timestamp with time zone)
11. 40.522 888.995 ↑ 4.6 385,939 1

Subquery Scan on *SELECT* 2 (cost=53,693.93..1,283,458.19 rows=1,772,082 width=12) (actual time=644.774..888.995 rows=385,939 loops=1)

12. 120.553 848.473 ↑ 4.6 385,939 1

Hash Join (cost=53,693.93..1,265,737.37 rows=1,772,082 width=8) (actual time=644.772..848.473 rows=385,939 loops=1)

  • Hash Cond: (aea_2.ev_id = evs.ev_id)
13. 250.608 354.931 ↑ 4.5 396,856 1

Bitmap Heap Scan on audit_evs_all aea_2 (cost=21,063.16..1,228,406.60 rows=1,790,464 width=16) (actual time=266.375..354.931 rows=396,856 loops=1)

  • Recheck Cond: (when_created <= '2020-08-26 07:15:21.616+00'::timestamp with time zone)
  • Heap Blocks: exact=16,298
14. 104.323 104.323 ↓ 1.0 1,827,526 1

Bitmap Index Scan on idx_audit_evs_all_when_created (cost=0.00..20,615.54 rows=1,790,464 width=0) (actual time=104.323..104.323 rows=1,827,526 loops=1)

  • Index Cond: (when_created <= '2020-08-26 07:15:21.616+00'::timestamp with time zone)
15. 157.488 372.989 ↑ 1.0 586,457 1

Hash (cost=25,270.77..25,270.77 rows=588,800 width=8) (actual time=372.989..372.989 rows=586,457 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 31,101kB
16. 215.501 215.501 ↑ 1.0 586,457 1

Index Only Scan using evs_pk on evs (cost=0.42..25,270.77 rows=588,800 width=8) (actual time=0.017..215.501 rows=586,457 loops=1)

  • Heap Fetches: 175,258
Planning time : 2.278 ms
Execution time : 111,435.812 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
audit_evs_all_arc_tr 17,651.557 ms 10917 1.617 ms