explain.depesz.com

PostgreSQL's explain analyze made readable

Result: t1Ve

Settings
# exclusive inclusive rows x rows loops node
1. 225.103 24,068.426 ↓ 1.1 3,529 1

HashAggregate (cost=90,372.68..90,405.56 rows=3,288 width=250) (actual time=24,066.270..24,068.426 rows=3,529 loops=1)

  • Group Key: date_part('year'::text, (rd.date_actual)::timestamp without time zone), date_part('month'::text, (rd.date_actual)::timestamp without time zone), ((date_trunc('month'::text, (rd.date_actual)::timestamp with time zone) + '1 mon'::interval) - '1 day'::interval), (COALESCE(rd.parent_contract_id, rd.contract_id)), rd.resident_id, rd.client_id, rd.product_client_id, rd.department_id, get_department_name(rd.department_id), rd.product_id, rd.pansion_id, pc.parent_contract_date, pc.parent_contract_number, CASE WHEN (rd.product_id = 28) THEN get_subsidy_contract_id(rd.resident_id, (COALESCE(rd.parent_contract_id, rd.contract_id)), rd.contract_id, rd.date_actual) ELSE NULL::text END, CASE WHEN (rd.product_id = 28) THEN get_subsidy_date(rd.resident_id, (COALESCE(rd.parent_contract_id, rd.contract_id)), rd.contract_id, rd.date_actual) ELSE NULL::date END, CASE WHEN (rd.product_id = 28) THEN get_subsidy_number(rd.resident_id, (COALESCE(rd.parent_contract_id, rd.contract_id)), rd.contract_id, rd.date_actual) ELSE NULL::character varying END, rd.checkin_date, rd.checkout_date, rd.em_pension_percent, rd.pension_amount, rd.em_region, rd.cco_id, rd.cso_id
2. 23,602.735 23,843.323 ↓ 2.7 90,188 1

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

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

CTE parent_contract

4. 1.117 1.117 ↑ 1.0 2,481 1

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

  • Filter: (kind = 1)
  • Rows Removed by Filter: 2368
5. 1.126 3.091 ↑ 1.0 2,481 1

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

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

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

7. 79.339 237.497 ↓ 34.9 90,188 1

Sort (cost=20,388.73..20,395.18 rows=2,582 width=115) (actual time=207.335..237.497 rows=90,188 loops=1)

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: quicksort Memory: 17092kB
8. 158.158 158.158 ↓ 34.9 90,188 1

Seq Scan on resident_days rd (cost=0.00..20,242.40 rows=2,582 width=115) (actual time=0.036..158.158 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
Planning time : 0.550 ms
Execution time : 24,069.585 ms