explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s4M

Settings
# exclusive inclusive rows x rows loops node
1. 661.627 10,875.268 ↑ 45.3 492,341 1

Hash Right Join (cost=2,037,145.08..5,680,652.17 rows=22,319,210 width=116) (actual time=9,850.312..10,875.268 rows=492,341 loops=1)

  • Hash Cond: ((rp.resident_id = crm_contracts.resident_id) AND (rp.date_actual = dd.date_actual))
2.          

CTE rp

3. 55.604 137.152 ↑ 3.4 491,720 1

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

4. 0.598 3.306 ↓ 1.1 551 1

Merge Left Join (cost=76.94..129.56 rows=517 width=24) (actual time=0.678..3.306 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. 0.957 1.525 ↓ 1.1 551 1

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

6. 0.324 0.568 ↓ 1.1 551 1

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

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

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

8. 0.166 1.183 ↓ 1.8 951 1

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

9. 0.678 1.017 ↓ 1.1 551 1

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

10. 0.261 0.339 ↓ 1.1 551 1

Sort (cost=38.47..39.76 rows=517 width=12) (actual time=0.232..0.339 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. 78.242 78.242 ↑ 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.004..0.142 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. 365.791 365.791 ↑ 3.4 491,720 1

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

14. 284.155 9,847.850 ↑ 45.3 492,341 1

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

  • Buckets: 131072 Batches: 512 Memory Usage: 1133kB
15. 122.833 9,563.695 ↑ 45.3 492,341 1

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

16. 4.849 66.156 ↓ 1.0 4,609 1

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

17. 3.504 47.513 ↓ 1.0 4,598 1

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

18. 4.917 34.813 ↓ 1.0 4,598 1

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

19. 10.799 16.102 ↓ 1.0 4,598 1

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

20. 3.746 5.303 ↓ 1.0 4,598 1

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

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

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

22. 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)
23. 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)
24. 13.794 13.794 ↑ 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.002..0.003 rows=1 loops=4,598)

  • Index Cond: (initial_contract_id = COALESCE(crm_contracts.parent_contract_id, crm_contracts.id))
25. 9,374.706 9,374.706 ↑ 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.015..2.034 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
Planning time : 1.107 ms
Execution time : 10,899.976 ms