explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oZsl : Optimization for: plan #KEBH

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 29,042.535 31,203.401 ↓ 0.0 0 1

Delete on audit_evs_all aea (cost=1,271,853.56..3,829,885.04 rows=103,633 width=38) (actual time=31,203.401..31,203.401 rows=0 loops=1)

2. 23.165 2,160.866 ↑ 9.5 10,917 1

Hash Join (cost=1,271,853.56..3,829,885.04 rows=103,633 width=38) (actual time=2,052.678..2,160.866 rows=10,917 loops=1)

  • Hash Cond: (aea2.audit_ev_id = aea.audit_ev_id)
3. 10.924 1,641.338 ↑ 164.0 10,917 1

Subquery Scan on aea2 (cost=21,064.66..2,574,396.17 rows=1,790,464 width=40) (actual time=1,553.272..1,641.338 rows=10,917 loops=1)

4. 342.132 1,630.414 ↑ 164.0 10,917 1

HashSetOp Except (cost=21,064.66..2,556,491.53 rows=1,790,464 width=12) (actual time=1,553.263..1,630.414 rows=10,917 loops=1)

5. 74.188 1,288.282 ↑ 4.6 782,795 1

Append (cost=21,064.66..2,547,585.16 rows=3,562,546 width=12) (actual time=247.019..1,288.282 rows=782,795 loops=1)

6. 47.235 401.624 ↑ 4.5 396,856 1

Subquery Scan on *SELECT* 1 (cost=21,064.66..1,246,312.74 rows=1,790,464 width=12) (actual time=247.017..401.624 rows=396,856 loops=1)

7. 251.505 354.389 ↑ 4.5 396,856 1

Bitmap Heap Scan on audit_evs_all aea_1 (cost=21,064.66..1,228,408.10 rows=1,790,464 width=8) (actual time=247.014..354.389 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
8. 102.884 102.884 ↓ 1.0 1,827,526 1

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

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

Subquery Scan on *SELECT* 2 (cost=53,695.43..1,283,459.69 rows=1,772,082 width=12) (actual time=567.299..812.470 rows=385,939 loops=1)

10. 116.507 772.390 ↑ 4.6 385,939 1

Hash Join (cost=53,695.43..1,265,738.87 rows=1,772,082 width=8) (actual time=567.298..772.390 rows=385,939 loops=1)

  • Hash Cond: (aea_2.ev_id = evs.ev_id)
11. 227.443 332.655 ↑ 4.5 396,856 1

Bitmap Heap Scan on audit_evs_all aea_2 (cost=21,064.66..1,228,408.10 rows=1,790,464 width=16) (actual time=242.827..332.655 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
12. 105.212 105.212 ↓ 1.0 1,827,526 1

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

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

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

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 31,104kB
14. 182.700 182.700 ↑ 1.0 586,547 1

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

  • Heap Fetches: 179,086
15. 118.092 496.363 ↑ 4.5 396,856 1

Hash (cost=1,228,408.10..1,228,408.10 rows=1,790,464 width=14) (actual time=496.363..496.363 rows=396,856 loops=1)

  • Buckets: 2,097,152 Batches: 1 Memory Usage: 34,987kB
16. 258.927 378.271 ↑ 4.5 396,856 1

Bitmap Heap Scan on audit_evs_all aea (cost=21,064.66..1,228,408.10 rows=1,790,464 width=14) (actual time=274.403..378.271 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
17. 119.344 119.344 ↓ 1.0 1,827,526 1

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

  • Index Cond: (when_created <= '2020-08-26 07:15:21.616+00'::timestamp with time zone)
Planning time : 0.780 ms
Execution time : 31,244.520 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
audit_evs_all_arc_tr 28,960.247 ms 10917 2.653 ms