explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cdqsn : Optimization for: Optimization for: Optimization for: plan #g3ZrB; plan #Q7Oa; plan #FrV3

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 2.043 1,214.545 ↓ 0.0 0 1

Delete on audit_evs_all aea (cost=6,808.08..108,128.71 rows=567 width=38) (actual time=1,214.536..1,214.545 rows=0 loops=1)

  • Delete on audit_evs_all_20200809 aea_1
  • Delete on audit_evs_all_default aea_2
2. 0.294 713.202 ↑ 9.2 29 1

Nested Loop (cost=6,808.08..54,044.48 rows=267 width=38) (actual time=554.000..713.202 rows=29 loops=1)

3. 0.125 712.308 ↑ 1.0 300 1

Subquery Scan on aea2 (cost=6,807.66..53,049.23 rows=300 width=40) (actual time=553.966..712.308 rows=300 loops=1)

4. 0.037 712.183 ↑ 1.0 300 1

Limit (cost=6,807.66..53,046.23 rows=300 width=8) (actual time=553.942..712.183 rows=300 loops=1)

5. 45.225 712.146 ↑ 5.1 300 1

Hash Left Join (cost=6,807.66..242,007.88 rows=1,526 width=8) (actual time=553.940..712.146 rows=300 loops=1)

  • Hash Cond: (aea_3.ev_id = evs.ev_id)
  • Filter: (((aea_3.ev_id IS NOT NULL) AND (evs.ev_id IS NULL)) OR (aea_3.ev_id IS NULL))
  • Rows Removed by Filter: 132,661
6. 19.450 119.139 ↑ 13.0 252,077 1

Append (cost=0.00..194,277.83 rows=3,268,969 width=16) (actual time=0.026..119.139 rows=252,077 loops=1)

7. 97.293 97.293 ↑ 1.0 246,460 1

Seq Scan on audit_evs_all_20200809 aea_3 (cost=0.00..13,744.55 rows=246,900 width=16) (actual time=0.025..97.293 rows=246,460 loops=1)

  • Filter: (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone)
  • Rows Removed by Filter: 30,784
8. 2.396 2.396 ↑ 538.0 5,617 1

Seq Scan on audit_evs_all_default aea_4 (cost=0.00..164,188.44 rows=3,022,069 width=16) (actual time=0.020..2.396 rows=5,617 loops=1)

  • Filter: (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone)
9. 23.184 547.782 ↑ 1.0 105,596 1

Hash (cost=5,059.18..5,059.18 rows=106,518 width=8) (actual time=547.782..547.782 rows=105,596 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,100kB
10. 524.598 524.598 ↑ 1.0 105,596 1

Seq Scan on evs (cost=0.00..5,059.18 rows=106,518 width=8) (actual time=18.081..524.598 rows=105,596 loops=1)

11. 0.600 0.600 ↓ 0.0 0 300

Index Scan using audit_evs_all_20200809_pkey on audit_evs_all_20200809 aea_1 (cost=0.42..3.31 rows=1 width=14) (actual time=0.002..0.002 rows=0 loops=300)

  • Index Cond: ((audit_ev_id = aea2.audit_ev_id) AND (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone))
12. 0.270 499.300 ↑ 1.1 271 1

Nested Loop (cost=6,808.09..54,084.23 rows=300 width=38) (actual time=233.662..499.300 rows=271 loops=1)

13. 0.167 220.330 ↑ 1.0 300 1

Subquery Scan on aea2_1 (cost=6,807.66..53,049.23 rows=300 width=40) (actual time=50.414..220.330 rows=300 loops=1)

14. 0.064 220.163 ↑ 1.0 300 1

Limit (cost=6,807.66..53,046.23 rows=300 width=8) (actual time=50.404..220.163 rows=300 loops=1)

15. 49.287 220.099 ↑ 5.1 300 1

Hash Left Join (cost=6,807.66..242,007.88 rows=1,526 width=8) (actual time=50.401..220.099 rows=300 loops=1)

  • Hash Cond: (aea_5.ev_id = evs_1.ev_id)
  • Filter: (((aea_5.ev_id IS NOT NULL) AND (evs_1.ev_id IS NULL)) OR (aea_5.ev_id IS NULL))
  • Rows Removed by Filter: 130,080
16. 20.928 126.700 ↑ 13.1 249,476 1

Append (cost=0.00..194,277.83 rows=3,268,969 width=16) (actual time=0.022..126.700 rows=249,476 loops=1)

17. 103.920 103.920 ↑ 1.0 246,460 1

Seq Scan on audit_evs_all_20200809 aea_5 (cost=0.00..13,744.55 rows=246,900 width=16) (actual time=0.021..103.920 rows=246,460 loops=1)

  • Filter: (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone)
  • Rows Removed by Filter: 30,784
18. 1.852 1.852 ↑ 1,002.0 3,016 1

Seq Scan on audit_evs_all_default aea_6 (cost=0.00..164,188.44 rows=3,022,069 width=16) (actual time=0.017..1.852 rows=3,016 loops=1)

  • Filter: (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone)
19. 21.536 44.112 ↑ 1.0 105,596 1

Hash (cost=5,059.18..5,059.18 rows=106,518 width=8) (actual time=44.111..44.112 rows=105,596 loops=1)

  • Buckets: 131,072 Batches: 2 Memory Usage: 3,100kB
20. 22.576 22.576 ↑ 1.0 105,596 1

Seq Scan on evs evs_1 (cost=0.00..5,059.18 rows=106,518 width=8) (actual time=0.009..22.576 rows=105,596 loops=1)

21. 278.700 278.700 ↑ 1.0 1 300

Index Scan using audit_evs_all_default_pkey on audit_evs_all_default aea_2 (cost=0.43..3.44 rows=1 width=14) (actual time=0.929..0.929 rows=1 loops=300)

  • Index Cond: ((audit_ev_id = aea2_1.audit_ev_id) AND (when_created <= '2020-08-09 19:15:21.635+00'::timestamp with time zone))
Planning time : 1,621.551 ms
Execution time : 7,353.357 ms
Trigger times:
Trigger Name:Total time:Calls:Average time:
audit_evs_all_arc_tr on audit_evs_all_20200809 1,493.325 ms 29 51.494 ms
audit_evs_all_arc_tr on audit_evs_all_default 4,641.267 ms 271 17.126 ms