explain.depesz.com

PostgreSQL's explain analyze made readable

Result: MxLR

Settings
# exclusive inclusive rows x rows loops node
1. 6.080 28,212.900 ↓ 21.4 3,533 1

Nested Loop Left Join (cost=97,507.07..103,315.19 rows=165 width=588) (actual time=24,785.769..28,212.900 rows=3,533 loops=1)

2. 0.574 28,206.820 ↓ 21.4 3,533 1

Nested Loop Left Join (cost=97,506.78..103,227.03 rows=165 width=500) (actual time=24,785.762..28,206.820 rows=3,533 loops=1)

3. 2.054 28,202.713 ↓ 21.4 3,533 1

Nested Loop Left Join (cost=97,506.50..103,136.42 rows=165 width=496) (actual time=24,785.758..28,202.713 rows=3,533 loops=1)

4. 2.451 28,193.593 ↓ 21.4 3,533 1

Nested Loop Left Join (cost=97,506.21..103,051.57 rows=165 width=472) (actual time=24,785.745..28,193.593 rows=3,533 loops=1)

5. 3.129 28,184.076 ↓ 21.4 3,533 1

Nested Loop Left Join (cost=97,505.93..102,960.95 rows=165 width=468) (actual time=24,785.733..28,184.076 rows=3,533 loops=1)

6. 4.397 28,173.881 ↓ 21.4 3,533 1

Nested Loop (cost=97,505.64..102,808.77 rows=165 width=444) (actual time=24,785.719..28,173.881 rows=3,533 loops=1)

7. 2.384 28,162.418 ↓ 21.4 3,533 1

Hash Join (cost=97,505.36..102,740.23 rows=165 width=440) (actual time=24,785.682..28,162.418 rows=3,533 loops=1)

  • Hash Cond: (rdti.pansion_id = pan.id)
8. 3,346.448 28,160.004 ↓ 1.1 3,533 1

GroupAggregate (cost=97,504.13..102,697.26 rows=3,292 width=446) (actual time=24,785.640..28,160.004 rows=3,533 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. 470.628 24,813.556 ↓ 2.8 90,762 1

Sort (cost=97,504.13..97,586.43 rows=32,920 width=370) (actual time=24,785.561..24,813.556 rows=90,762 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: 18816kB
10. 38.280 24,342.928 ↓ 2.8 90,762 1

Subquery Scan on rdti (cost=20,755.10..89,406.03 rows=32,920 width=370) (actual time=291.609..24,342.928 rows=90,762 loops=1)

11. 23,966.440 24,304.648 ↓ 2.8 90,762 1

Merge Right Join (cost=20,755.10..89,076.83 rows=32,920 width=422) (actual time=291.608..24,304.648 rows=90,762 loops=1)

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

CTE parent_contract

13. 1.411 1.411 ↑ 1.0 2,484 1

Seq Scan on crm_contracts cc (cost=0.00..148.55 rows=2,547 width=19) (actual time=0.020..1.411 rows=2,484 loops=1)

  • Filter: (kind = 1)
  • Rows Removed by Filter: 2368
14. 1.220 3.504 ↑ 1.0 2,484 1

Sort (cost=195.03..201.40 rows=2,547 width=40) (actual time=3.109..3.504 rows=2,484 loops=1)

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

CTE Scan on parent_contract pc (cost=0.00..50.94 rows=2,547 width=40) (actual time=0.021..2.284 rows=2,484 loops=1)

16. 158.162 334.704 ↓ 35.1 90,762 1

Sort (cost=20,411.52..20,417.98 rows=2,585 width=123) (actual time=287.375..334.704 rows=90,762 loops=1)

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: external sort Disk: 10528kB
17. 176.542 176.542 ↓ 35.1 90,762 1

Seq Scan on resident_days rd (cost=0.00..20,265.00 rows=2,585 width=123) (actual time=0.020..176.542 rows=90,762 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: 428838
18. 0.006 0.030 ↓ 1.1 11 1

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

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

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

20. 7.066 7.066 ↑ 1.0 1 3,533

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

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

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

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

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

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

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

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

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

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

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

  • Index Cond: (product_cl.person_id = id)
Planning time : 4.563 ms
Execution time : 28,220.477 ms