explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2xgA

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Delete on br_lbo_grbs_ba_costs_lbo_pno (cost=9,687,453,610,170.51..9,687,454,456,241.74 rows=3,871,058 width=6) (actual rows= loops=)

2.          

CTE br_lbo_pno_row

3. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on br_lbo_grbs_ba_costs_lbo_pno gz_dlt (cost=72,462.23..918,533.46 rows=3,871,058 width=8) (actual rows= loops=)

  • Recheck Cond: (budget_cycle_id < $0)
4.          

Initplan (forBitmap Heap Scan)

5. 0.000 0.000 ↓ 0.0

Seq Scan on bp_budget_cycle (cost=0.00..1.10 rows=1 width=8) (actual rows= loops=)

  • Filter: (start_year = 2017)
6. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ind_br_lbo_grbs_ba_bc (cost=0.00..71,493.37 rows=3,871,058 width=0) (actual rows= loops=)

  • Index Cond: (budget_cycle_id < $0)
7.          

CTE br_budg_est_row

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=87,099.24..89,678.14 rows=1,995,100 width=16) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=87,098.81..87,100.81 rows=200 width=8) (actual rows= loops=)

  • Group Key: br_lbo_pno_row.id
10. 0.000 0.000 ↓ 0.0

CTE Scan on br_lbo_pno_row (cost=0.00..77,421.16 rows=3,871,058 width=8) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Index Scan using ind_br_be_el_estimate_ind on br_budget_estimates_el (cost=0.43..12.78 rows=11 width=24) (actual rows= loops=)

  • Index Cond: (estimate_indicators_id = br_lbo_pno_row.id)
12.          

CTE budg_est_prnt_prnt

13. 0.000 0.000 ↓ 0.0

Nested Loop (cost=44,890.18..46,579.25 rows=1,995,100 width=8) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

HashAggregate (cost=44,889.75..44,891.75 rows=200 width=8) (actual rows= loops=)

  • Group Key: br_bu.parent_id
15. 0.000 0.000 ↓ 0.0

CTE Scan on br_budg_est_row br_bu (cost=0.00..39,902.00 rows=1,995,100 width=8) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Index Scan using br_budget_estimates_el_pk on br_budget_estimates_el br_budget_estimates_el_1 (cost=0.43..8.43 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = br_bu.parent_id)
17.          

CTE union_ids

18. 0.000 0.000 ↓ 0.0

Unique (cost=39,146,123.62..40,153,649.12 rows=201,505,100 width=8) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Sort (cost=39,146,123.62..39,649,886.37 rows=201,505,100 width=8) (actual rows= loops=)

  • Sort Key: budg_est_prnt_prnt.id
20. 0.000 0.000 ↓ 0.0

Append (cost=0.00..3,087,417.25 rows=201,505,100 width=8) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

CTE Scan on budg_est_prnt_prnt (cost=0.00..39,902.00 rows=1,995,100 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

CTE Scan on br_budg_est_row (cost=0.00..1,032,464.25 rows=199,510,000 width=8) (actual rows= loops=)

23.          

CTE del_budg_est_cons

24. 0.000 0.000 ↓ 0.0

Delete on br_budget_est_cons_el (cost=4,533,865.18..4,539,076.55 rows=771,746 width=38) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=4,533,865.18..4,539,076.55 rows=771,746 width=38) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

HashAggregate (cost=4,533,864.75..4,533,866.75 rows=200 width=40) (actual rows= loops=)

  • Group Key: uids.id
27. 0.000 0.000 ↓ 0.0

CTE Scan on union_ids uids (cost=0.00..4,030,102.00 rows=201,505,100 width=40) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using ind_br_be_source_el on br_budget_est_cons_el (cost=0.43..25.78 rows=27 width=14) (actual rows= loops=)

  • Index Cond: (br_budget_est_el_id = uids.id)
29.          

CTE del_budg_est_parent

30. 0.000 0.000 ↓ 0.0

Delete on br_budget_estimates_el el (cost=0.00..9,687,407,790,191.76 rows=2,533,012 width=38) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.00..9,687,407,790,191.76 rows=2,533,012 width=38) (actual rows= loops=)

  • Join Filter: ((el.id = uids_1.id) OR (el.parent_id = uids_1.id))
32. 0.000 0.000 ↓ 0.0

Seq Scan on br_budget_estimates_el el (cost=0.00..156,220.99 rows=3,990,199 width=22) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

CTE Scan on union_ids uids_1 (cost=0.00..4,030,102.00 rows=201,505,100 width=40) (actual rows= loops=)

34.          

Initplan (forDelete)

35. 0.000 0.000 ↓ 0.0

Seq Scan on bp_budget_cycle bp_budget_cycle_1 (cost=0.00..1.10 rows=1 width=8) (actual rows= loops=)

  • Filter: (start_year = 2017)
36. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on br_lbo_grbs_ba_costs_lbo_pno (cost=72,461.13..918,532.36 rows=3,871,058 width=6) (actual rows= loops=)

  • Recheck Cond: (budget_cycle_id < $12)
37. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ind_br_lbo_grbs_ba_bc (cost=0.00..71,493.37 rows=3,871,058 width=0) (actual rows= loops=)