explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PKQW

Settings
# exclusive inclusive rows x rows loops node
1. 9.338 130,410.820 ↓ 21.4 3,531 1

Nested Loop Left Join (cost=97,464.41..103,237.68 rows=165 width=588) (actual time=25,969.210..130,410.820 rows=3,531 loops=1)

2. 2.312 130,401.482 ↓ 21.4 3,531 1

Nested Loop Left Join (cost=97,464.13..103,149.52 rows=165 width=500) (actual time=25,969.203..130,401.482 rows=3,531 loops=1)

3. 4.732 130,395.639 ↓ 21.4 3,531 1

Nested Loop Left Join (cost=97,463.84..103,058.90 rows=165 width=496) (actual time=25,969.196..130,395.639 rows=3,531 loops=1)

4. 4.114 130,369.721 ↓ 21.4 3,531 1

Nested Loop Left Join (cost=97,463.55..102,974.04 rows=165 width=472) (actual time=25,968.274..130,369.721 rows=3,531 loops=1)

5. 5.554 130,347.952 ↓ 21.4 3,531 1

Nested Loop Left Join (cost=97,463.27..102,883.42 rows=165 width=468) (actual time=25,968.252..130,347.952 rows=3,531 loops=1)

6. 5.457 130,321.212 ↓ 21.4 3,531 1

Nested Loop (cost=97,462.98..102,731.24 rows=165 width=444) (actual time=25,968.232..130,321.212 rows=3,531 loops=1)

7. 6.200 130,291.038 ↓ 21.4 3,531 1

Hash Join (cost=97,462.70..102,662.69 rows=165 width=440) (actual time=25,968.190..130,291.038 rows=3,531 loops=1)

  • Hash Cond: (rdti.pansion_id = pan.id)
8. 104,283.926 130,284.816 ↓ 1.1 3,531 1

GroupAggregate (cost=97,461.47..102,619.73 rows=3,291 width=446) (actual time=25,968.154..130,284.816 rows=3,531 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. 509.647 26,000.890 ↓ 2.7 90,470 1

Sort (cost=97,461.47..97,543.74 rows=32,907 width=370) (actual time=25,965.337..26,000.890 rows=90,470 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: 18752kB
10. 41.518 25,491.243 ↓ 2.7 90,470 1

Subquery Scan on rdti (cost=20,744.11..89,367.94 rows=32,907 width=370) (actual time=344.059..25,491.243 rows=90,470 loops=1)

11. 25,052.354 25,449.725 ↓ 2.7 90,470 1

Merge Right Join (cost=20,744.11..89,038.87 rows=32,907 width=422) (actual time=344.058..25,449.725 rows=90,470 loops=1)

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

CTE parent_contract

13. 1.345 1.345 ↑ 1.0 2,483 1

Seq Scan on crm_contracts cc (cost=0.00..148.55 rows=2,547 width=19) (actual time=0.016..1.345 rows=2,483 loops=1)

  • Filter: (kind = 1)
  • Rows Removed by Filter: 2367
14. 1.267 3.492 ↑ 1.0 2,482 1

Sort (cost=195.03..201.40 rows=2,547 width=40) (actual time=3.008..3.492 rows=2,482 loops=1)

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

CTE Scan on parent_contract pc (cost=0.00..50.94 rows=2,547 width=40) (actual time=0.018..2.225 rows=2,483 loops=1)

16. 174.032 393.879 ↓ 35.0 90,470 1

Sort (cost=20,400.53..20,406.99 rows=2,584 width=123) (actual time=339.997..393.879 rows=90,470 loops=1)

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: external sort Disk: 10496kB
17. 219.847 219.847 ↓ 35.0 90,470 1

Seq Scan on resident_days rd (cost=0.00..20,254.08 rows=2,584 width=123) (actual time=0.041..219.847 rows=90,470 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: 428834
18. 0.008 0.022 ↓ 1.1 11 1

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

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

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

20. 24.717 24.717 ↑ 1.0 1 3,531

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

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

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

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

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

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

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

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

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,531)

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

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,531)

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