explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hrxI : Optimization for: Optimization for: plan #YolR; plan #GNAE

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 70,878.720 74,363.015 ↓ 0.0 0 1

Delete on audit_evs_all aea (cost=3,362.39..1,429,344.95 rows=283,146 width=38) (actual time=74,363.015..74,363.015 rows=0 loops=1)

2. 45.892 3,484.295 ↑ 13.4 21,185 1

Nested Loop (cost=3,362.39..1,429,344.95 rows=283,146 width=38) (actual time=1,948.077..3,484.295 rows=21,185 loops=1)

3. 27.580 1,997.823 ↑ 13.4 21,185 1

Subquery Scan on aea2 (cost=3,361.95..729,055.52 rows=283,146 width=40) (actual time=1,901.261..1,997.823 rows=21,185 loops=1)

4. 328.985 1,970.243 ↑ 13.4 21,185 1

HashSetOp Except (cost=3,361.95..726,224.06 rows=283,146 width=12) (actual time=1,901.251..1,970.243 rows=21,185 loops=1)

5. 68.086 1,641.258 ↓ 1.0 564,939 1

Append (cost=3,361.95..724,813.92 rows=564,055 width=12) (actual time=27.315..1,641.258 rows=564,939 loops=1)

6. 54.581 198.876 ↓ 1.0 293,062 1

Subquery Scan on *SELECT* 1 (cost=3,361.95..340,281.73 rows=283,146 width=12) (actual time=27.313..198.876 rows=293,062 loops=1)

7. 119.295 144.295 ↓ 1.0 293,062 1

Bitmap Heap Scan on audit_evs_all aea_1 (cost=3,361.95..337,450.27 rows=283,146 width=8) (actual time=27.309..144.295 rows=293,062 loops=1)

  • Recheck Cond: (when_created <= (now() - '30 days'::interval))
  • Heap Blocks: exact=11,725
8. 25.000 25.000 ↓ 1.0 293,062 1

Bitmap Index Scan on _idx_audit_evs_all_when_created_ev_id_is_null_original (cost=0.00..3,291.16 rows=283,146 width=0) (actual time=25.000..25.000 rows=293,062 loops=1)

  • Index Cond: (when_created <= (now() - '30 days'::interval))
9. 27.068 1,374.296 ↑ 1.0 271,877 1

Subquery Scan on *SELECT* 2 (cost=46,269.36..381,711.92 rows=280,909 width=12) (actual time=700.530..1,374.296 rows=271,877 loops=1)

10. 85.403 1,347.228 ↑ 1.0 271,877 1

Hash Join (cost=46,269.36..378,902.83 rows=280,909 width=8) (actual time=700.529..1,347.228 rows=271,877 loops=1)

  • Hash Cond: (aea_2.ev_id = evs.ev_id)
11. 615.647 802.613 ↓ 1.0 291,444 1

Bitmap Heap Scan on audit_evs_all aea_2 (cost=3,889.67..335,779.88 rows=283,146 width=16) (actual time=239.049..802.613 rows=291,444 loops=1)

  • Recheck Cond: ((when_created <= (now() - '30 days'::interval)) AND (ev_id IS NOT NULL))
  • Heap Blocks: exact=11,721
12. 186.966 186.966 ↓ 9.1 2,552,650 1

Bitmap Index Scan on idx_audit_evs_all_when_created_ev_id_is_null (cost=0.00..3,818.89 rows=280,909 width=0) (actual time=186.966..186.966 rows=2,552,650 loops=1)

  • Index Cond: (when_created <= (now() - '30 days'::interval))
13. 233.556 459.212 ↑ 1.0 583,843 1

Hash (cost=35,054.86..35,054.86 rows=585,986 width=8) (actual time=459.212..459.212 rows=583,843 loops=1)

  • Buckets: 1,048,576 Batches: 1 Memory Usage: 30,999kB
14. 225.656 225.656 ↑ 1.0 583,843 1

Seq Scan on evs (cost=0.00..35,054.86 rows=585,986 width=8) (actual time=0.016..225.656 rows=583,843 loops=1)

15. 1,440.580 1,440.580 ↑ 1.0 1 21,185

Index Scan using audit_evs_all_pk on audit_evs_all aea (cost=0.44..2.47 rows=1 width=14) (actual time=0.068..0.068 rows=1 loops=21,185)

  • Index Cond: (audit_ev_id = aea2.audit_ev_id)
Planning time : 1.710 ms
Execution time : 74,368.990 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
audit_evs_all_arc_tr 70,672.394 ms 21185 3.336 ms