explain.depesz.com

PostgreSQL's explain analyze made readable

Result: G4AR

Settings
# exclusive inclusive rows x rows loops node
1. 42,601.510 89,047.421 ↑ 45.3 492,345 1

Hash Left Join (cost=2,037,165.39..173,577,977.76 rows=22,319,210 width=164) (actual time=10,827.505..89,047.421 rows=492,345 loops=1)

  • Hash Cond: (crm_contracts.resident_id = rl.resident_id)
  • Join Filter: ((dd.date_actual >= (rl.start_date + '1 day'::interval)) AND (dd.date_actual <= COALESCE(rl.end_date, CURRENT_DATE)))
  • Rows Removed by Join Filter: 386847
2.          

CTE rp

3. 63.187 198.624 ↑ 3.4 491,720 1

Nested Loop Left Join (cost=77.23..50,906.66 rows=1,678,527 width=28) (actual time=0.563..198.624 rows=491,720 loops=1)

4. 0.762 4.299 ↓ 1.1 551 1

Merge Left Join (cost=76.94..129.56 rows=517 width=24) (actual time=0.540..4.299 rows=551 loops=1)

  • Merge Cond: (rp_2.resident_id = rank.resident_id)
  • Join Filter: (((count(rank.id) OVER (?)) + 1) = (count(rp_2.id) OVER (?)))
  • Rows Removed by Join Filter: 792
5. 1.214 2.200 ↓ 1.1 551 1

WindowAgg (cost=38.47..48.81 rows=517 width=28) (actual time=0.291..2.200 rows=551 loops=1)

6. 0.883 0.986 ↓ 1.1 551 1

Sort (cost=38.47..39.76 rows=517 width=24) (actual time=0.285..0.986 rows=551 loops=1)

  • Sort Key: rp_2.resident_id, rp_2.date DESC
  • Sort Method: quicksort Memory: 68kB
7. 0.103 0.103 ↓ 1.1 551 1

Seq Scan on resident_pensions rp_2 (cost=0.00..15.17 rows=517 width=24) (actual time=0.007..0.103 rows=551 loops=1)

8. 0.188 1.337 ↓ 1.8 951 1

Materialize (cost=38.47..57.86 rows=517 width=16) (actual time=0.243..1.337 rows=951 loops=1)

9. 0.804 1.149 ↓ 1.1 551 1

WindowAgg (cost=38.47..51.40 rows=517 width=20) (actual time=0.242..1.149 rows=551 loops=1)

10. 0.267 0.345 ↓ 1.1 551 1

Sort (cost=38.47..39.76 rows=517 width=12) (actual time=0.231..0.345 rows=551 loops=1)

  • Sort Key: rank.resident_id, rank.date DESC
  • Sort Method: quicksort Memory: 50kB
11. 0.078 0.078 ↓ 1.1 551 1

Seq Scan on resident_pensions rank (cost=0.00..15.17 rows=517 width=12) (actual time=0.003..0.078 rows=551 loops=1)

12. 131.138 131.138 ↑ 3.6 892 551

Index Only Scan using d_date_date_actual_idx on d_date dd_1 (cost=0.29..65.74 rows=3,247 width=4) (actual time=0.005..0.238 rows=892 loops=551)

  • Index Cond: ((date_actual >= rp_2.date) AND (date_actual <= COALESCE((((rank.date - '1 day'::interval))::date), CURRENT_DATE)))
  • Heap Fetches: 65966
13. 22,143.740 46,445.722 ↑ 45.3 492,341 1

Nested Loop Left Join (cost=1,986,238.42..167,276,640.11 rows=22,319,210 width=148) (actual time=10,827.093..46,445.722 rows=492,341 loops=1)

  • Join Filter: ((rs.em_crm_commercial_order_id = cco.id) OR (rs.em_crm_state_order_id = cso.id))
  • Rows Removed by Join Filter: 203098051
14. 1,202.747 12,485.798 ↑ 45.3 492,341 1

Hash Right Join (cost=1,986,238.42..5,573,947.49 rows=22,319,210 width=116) (actual time=10,826.905..12,485.798 rows=492,341 loops=1)

  • Hash Cond: ((rp.resident_id = crm_contracts.resident_id) AND (rp.date_actual = dd.date_actual))
15. 458.468 458.468 ↑ 3.4 491,720 1

CTE Scan on rp (cost=0.00..33,570.54 rows=1,678,527 width=36) (actual time=0.566..458.468 rows=491,720 loops=1)

16. 295.664 10,824.583 ↑ 45.3 492,341 1

Hash (cost=1,346,304.27..1,346,304.27 rows=22,319,210 width=88) (actual time=10,824.583..10,824.583 rows=492,341 loops=1)

  • Buckets: 131072 Batches: 512 Memory Usage: 1133kB
17. 123.022 10,528.919 ↑ 45.3 492,341 1

Nested Loop Left Join (cost=428.27..1,346,304.27 rows=22,319,210 width=88) (actual time=11.800..10,528.919 rows=492,341 loops=1)

18. 1.517 77.128 ↓ 1.0 4,609 1

Nested Loop Left Join (cost=427.97..5,316.90 rows=4,583 width=88) (actual time=4.077..77.128 rows=4,609 loops=1)

19. 3.098 48.023 ↓ 1.0 4,598 1

Nested Loop Left Join (cost=427.69..3,861.87 rows=4,578 width=80) (actual time=4.070..48.023 rows=4,598 loops=1)

20. 4.156 35.729 ↓ 1.0 4,598 1

Nested Loop Left Join (cost=427.42..2,520.01 rows=4,578 width=76) (actual time=4.063..35.729 rows=4,598 loops=1)

21. 12.055 17.779 ↓ 1.0 4,598 1

WindowAgg (cost=427.13..541.58 rows=4,578 width=248) (actual time=4.038..17.779 rows=4,598 loops=1)

22. 3.883 5.724 ↓ 1.0 4,598 1

Sort (cost=427.13..438.58 rows=4,578 width=68) (actual time=4.030..5.724 rows=4,598 loops=1)

  • Sort Key: crm_contracts.resident_id, crm_contracts.contract_start_date DESC
  • Sort Method: quicksort Memory: 839kB
23. 1.841 1.841 ↓ 1.0 4,598 1

Seq Scan on crm_contracts (cost=0.00..148.78 rows=4,578 width=68) (actual time=0.009..1.841 rows=4,598 loops=1)

24. 13.794 13.794 ↑ 1.0 1 4,598

Index Scan using index_crm_commercial_orders_on_order_id on crm_commercial_orders cco (cost=0.29..0.42 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=4,598)

  • Index Cond: (order_id = crm_contracts.order_id)
25. 9.196 9.196 ↓ 0.0 0 4,598

Index Scan using index_crm_state_orders_on_order_id on crm_state_orders cso (cost=0.27..0.29 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=4,598)

  • Index Cond: (order_id = crm_contracts.order_id)
26. 27.588 27.588 ↑ 1.0 1 4,598

Index Scan using index_resident_stay_periods_on_initial_contract_id on resident_stay_periods rsp (cost=0.28..0.31 rows=1 width=12) (actual time=0.005..0.006 rows=1 loops=4,598)

  • Index Cond: (initial_contract_id = COALESCE(crm_contracts.parent_contract_id, crm_contracts.id))
27. 10,328.769 10,328.769 ↑ 45.5 107 4,609

Index Only Scan using d_date_date_actual_idx on d_date dd (cost=0.29..243.90 rows=4,870 width=4) (actual time=2.221..2.241 rows=107 loops=4,609)

  • Index Cond: (date_actual <= COALESCE(LEAST(rsp.checkout_date, (CASE WHEN (crm_contracts.contract_start_date = first_value(crm_contracts.contract_start_date) OVER (?)) THEN CURRENT_DATE ELSE crm_contracts.contract_end_date END), CURRENT_DATE), CURRENT_DATE))
  • Filter: CASE WHEN (crm_contracts.kind = 1) THEN (date_actual >= rsp.checkin_date) ELSE (date_actual >= crm_contracts.contract_start_date) END
  • Rows Removed by Filter: 17062
  • Heap Fetches: 127922
28. 11,816.102 11,816.184 ↑ 1.0 413 492,341

Materialize (cost=0.00..17.21 rows=414 width=40) (actual time=0.000..0.024 rows=413 loops=492,341)

29. 0.082 0.082 ↑ 1.0 413 1

Seq Scan on custom_region_subsidies rs (cost=0.00..15.14 rows=414 width=40) (actual time=0.008..0.082 rows=413 loops=1)

30. 0.085 0.189 ↓ 1.0 554 1

Hash (cost=13.47..13.47 rows=547 width=16) (actual time=0.189..0.189 rows=554 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 34kB
31. 0.104 0.104 ↓ 1.0 554 1

Seq Scan on resident_leaves rl (cost=0.00..13.47 rows=547 width=16) (actual time=0.007..0.104 rows=554 loops=1)

Planning time : 1.593 ms
Execution time : 89,097.846 ms