explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gv3K

Settings
# exclusive inclusive rows x rows loops node
1. 5.934 130,363.872 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=97,394.93..103,160.69 rows=164 width=588) (actual time=26,193.607..130,363.872 rows=3,529 loops=1)

2. 2.541 130,354.409 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=97,394.64..103,073.07 rows=164 width=500) (actual time=26,193.600..130,354.409 rows=3,529 loops=1)

3. 2.730 130,348.339 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=97,394.35..102,982.96 rows=164 width=496) (actual time=26,193.595..130,348.339 rows=3,529 loops=1)

4. 5.799 130,331.493 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=97,394.07..102,898.62 rows=164 width=472) (actual time=26,192.997..130,331.493 rows=3,529 loops=1)

5. 6.644 130,311.578 ↓ 21.5 3,529 1

Nested Loop Left Join (cost=97,393.78..102,808.51 rows=164 width=468) (actual time=26,192.982..130,311.578 rows=3,529 loops=1)

6. 8.665 130,287.289 ↓ 21.5 3,529 1

Nested Loop (cost=97,393.49..102,657.25 rows=164 width=444) (actual time=26,192.968..130,287.289 rows=3,529 loops=1)

7. 6.432 130,257.450 ↓ 21.5 3,529 1

Hash Join (cost=97,393.21..102,589.09 rows=164 width=440) (actual time=26,192.955..130,257.450 rows=3,529 loops=1)

  • Hash Cond: (rdti.pansion_id = pan.id)
8. 104,023.973 130,250.993 ↓ 1.1 3,529 1

GroupAggregate (cost=97,391.99..102,546.18 rows=3,288 width=446) (actual time=26,192.913..130,250.993 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. 517.061 26,227.020 ↓ 2.7 90,188 1

Sort (cost=97,391.99..97,474.19 rows=32,882 width=370) (actual time=26,190.065..26,227.020 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: external merge Disk: 18680kB
10. 43.544 25,709.959 ↓ 2.7 90,188 1

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

11. 25,318.311 25,666.415 ↓ 2.7 90,188 1

Merge Right Join (cost=20,732.31..88,975.19 rows=32,882 width=422) (actual time=290.343..25,666.415 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.390 1.390 ↑ 1.0 2,481 1

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

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

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

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

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

16. 169.352 344.303 ↓ 34.9 90,188 1

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

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: external sort Disk: 10464kB
17. 174.951 174.951 ↓ 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.029..174.951 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.007 0.025 ↓ 1.1 11 1

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

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

Seq Scan on pansions pan (cost=0.00..1.10 rows=10 width=36) (actual time=0.016..0.018 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. 17.645 17.645 ↑ 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.005..0.005 rows=1 loops=3,529)

  • Index Cond: (res.person_id = id)
22. 14.116 14.116 ↑ 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.004..0.004 rows=1 loops=3,529)

  • Index Cond: (id = rdti.client_id)
23. 14.116 14.116 ↑ 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.004..0.004 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. 3.529 3.529 ↓ 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.001..0.001 rows=0 loops=3,529)

  • Index Cond: (product_cl.person_id = id)
Planning time : 3.796 ms
Execution time : 130,373.751 ms