explain.depesz.com

PostgreSQL's explain analyze made readable

Result: q9SC

Settings
# exclusive inclusive rows x rows loops node
1. 8.967 123,976.964 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=91,773.93..97,539.69 rows=164 width=588) (actual time=24,341.001..123,976.964 rows=3,529 loops=1)

2. 2.188 123,967.997 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=91,773.64..97,452.07 rows=164 width=500) (actual time=24,340.996..123,967.997 rows=3,529 loops=1)

3. 2.665 123,962.280 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=91,773.35..97,361.96 rows=164 width=496) (actual time=24,340.992..123,962.280 rows=3,529 loops=1)

4. 5.163 123,949.028 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=91,773.07..97,277.62 rows=164 width=472) (actual time=24,340.986..123,949.028 rows=3,529 loops=1)

5. 2.999 123,933.278 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=91,772.78..97,187.51 rows=164 width=468) (actual time=24,340.977..123,933.278 rows=3,529 loops=1)

6. 6.725 123,916.163 ↓ 21.5 3,529 1

Nested Loop (cost=91,772.49..97,036.25 rows=164 width=444) (actual time=24,340.966..123,916.163 rows=3,529 loops=1)

7. 5.595 123,888.264 ↓ 21.5 3,529 1

Hash Join (cost=91,772.21..96,968.09 rows=164 width=440) (actual time=24,340.951..123,888.264 rows=3,529 loops=1)

  • Hash Cond: (rdti.pansion_id = pan.id)
8. 99,525.702 123,882.654 ↓ 1.1 3,529 1

GroupAggregate (cost=91,770.99..96,925.18 rows=3,288 width=446) (actual time=24,340.927..123,882.654 rows=3,529 loops=1)

  • Group Key: rdti.year, rdti.month, rdti.last_day_of_month, rdti.contract_uuid, rdti.resident_id, rdti.client_id, rdti.product_client_id, rdti.department_id, rdti.department_name, rdti.product_id, rdti.pansion_id, rdti.parent_contract_date, rdti.parent_contract_number, rdti.subsidy_contract_id, rdti.subsidy_contract_date, rdti.subsidy_contract_number, rdti.checkin_date, rdti.checkout_date, rdti.em_pension_percent, rdti.pension_amount, rdti.em_region, rdti.cco_id, rdti.cso_id
9. 356.360 24,356.952 ↓ 2.7 90,188 1

Sort (cost=91,770.99..91,853.19 rows=32,882 width=370) (actual time=24,340.172..24,356.952 rows=90,188 loops=1)

  • Sort Key: rdti.month, rdti.last_day_of_month, rdti.contract_uuid, rdti.resident_id, rdti.client_id, rdti.product_client_id, rdti.department_id, rdti.department_name, rdti.product_id, rdti.pansion_id, rdti.parent_contract_date, rdti.parent_contract_number, rdti.subsidy_contract_id, rdti.subsidy_contract_date, rdti.subsidy_contract_number, rdti.checkin_date, rdti.checkout_date, rdti.em_pension_percent, rdti.pension_amount, rdti.em_region, rdti.cco_id, rdti.cso_id
  • Sort Method: quicksort Memory: 27428kB
10. 41.151 24,000.592 ↓ 2.7 90,188 1

Subquery Scan on rdti (cost=20,732.31..89,304.01 rows=32,882 width=370) (actual time=219.768..24,000.592 rows=90,188 loops=1)

11. 23,706.802 23,959.441 ↓ 2.7 90,188 1

Merge Right Join (cost=20,732.31..88,975.19 rows=32,882 width=422) (actual time=219.767..23,959.441 rows=90,188 loops=1)

  • Merge Cond: (pc.parent_contract_id = (COALESCE(rd.parent_contract_id, rd.contract_id)))
12.          

CTE parent_contract

13. 1.084 1.084 ↑ 1.0 2,481 1

Seq Scan on crm_contracts cc (cost=0.00..148.55 rows=2,547 width=19) (actual time=0.006..1.084 rows=2,481 loops=1)

  • Filter: (kind = 1)
  • Rows Removed by Filter: 2368
14. 1.082 2.927 ↑ 1.0 2,481 1

Sort (cost=195.03..201.40 rows=2,547 width=40) (actual time=2.535..2.927 rows=2,481 loops=1)

  • Sort Key: pc.parent_contract_id
  • Sort Method: quicksort Memory: 278kB
15. 1.845 1.845 ↑ 1.0 2,481 1

CTE Scan on parent_contract pc (cost=0.00..50.94 rows=2,547 width=40) (actual time=0.008..1.845 rows=2,481 loops=1)

16. 84.787 249.712 ↓ 34.9 90,188 1

Sort (cost=20,388.73..20,395.18 rows=2,582 width=123) (actual time=216.937..249.712 rows=90,188 loops=1)

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: quicksort Memory: 19280kB
17. 164.925 164.925 ↓ 34.9 90,188 1

Seq Scan on resident_days rd (cost=0.00..20,242.40 rows=2,582 width=123) (actual time=0.020..164.925 rows=90,188 loops=1)

  • Filter: ((COALESCE(product_id, 0) <> 32) AND (date_part('year'::text, (date_actual)::timestamp without time zone) = '2019'::double precision))
  • Rows Removed by Filter: 428832
18. 0.004 0.015 ↓ 1.1 11 1

Hash (cost=1.10..1.10 rows=10 width=36) (actual time=0.015..0.015 rows=11 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
19. 0.011 0.011 ↓ 1.1 11 1

Seq Scan on pansions pan (cost=0.00..1.10 rows=10 width=36) (actual time=0.008..0.011 rows=11 loops=1)

20. 21.174 21.174 ↑ 1.0 1 3,529

Index Scan using residents_pkey on residents res (cost=0.28..0.42 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=3,529)

  • Index Cond: (id = rdti.resident_id)
21. 14.116 14.116 ↑ 1.0 1 3,529

Index Scan using people_pkey on people p_res (cost=0.29..0.92 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=3,529)

  • Index Cond: (res.person_id = id)
22. 10.587 10.587 ↑ 1.0 1 3,529

Index Scan using crm_clients_pkey on crm_clients cl (cost=0.29..0.55 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=3,529)

  • Index Cond: (id = rdti.client_id)
23. 10.587 10.587 ↑ 1.0 1 3,529

Index Scan using people_pkey on people p_cl (cost=0.29..0.51 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=3,529)

  • Index Cond: (cl.person_id = id)
24. 3.529 3.529 ↓ 0.0 0 3,529

Index Scan using crm_clients_pkey on crm_clients product_cl (cost=0.29..0.55 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=3,529)

  • Index Cond: (id = rdti.product_client_id)
25. 0.000 0.000 ↓ 0.0 0 3,529

Index Scan using people_pkey on people p_product_cl (cost=0.29..0.51 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=3,529)

  • Index Cond: (product_cl.person_id = id)
Planning time : 1.996 ms
Execution time : 123,980.314 ms