explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ehDl

Settings
# exclusive inclusive rows x rows loops node
1. 230.781 23,804.440 ↓ 1.1 3,587 1

HashAggregate (cost=89,507.96..89,541.01 rows=3,305 width=258) (actual time=23,802.378..23,804.440 rows=3,587 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,250.221 23,573.659 ↓ 2.8 91,263 1

Merge Right Join (cost=19,435.59..87,194.67 rows=33,047 width=422) (actual time=272.987..23,573.659 rows=91,263 loops=1)

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

CTE parent_contract

4. 1.124 1.124 ↑ 1.0 2,481 1

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

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

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

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

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

7. 171.285 320.365 ↓ 35.2 91,263 1

Sort (cost=19,092.01..19,098.49 rows=2,595 width=115) (actual time=270.033..320.365 rows=91,263 loops=1)

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: external sort Disk: 10176kB
8. 149.080 149.080 ↓ 35.2 91,263 1

Seq Scan on resident_days rd (cost=0.00..18,944.85 rows=2,595 width=115) (actual time=0.018..149.080 rows=91,263 loops=1)

  • Filter: (date_part('year'::text, (date_actual)::timestamp without time zone) = '2019'::double precision)
  • Rows Removed by Filter: 427757
Planning time : 0.668 ms
Execution time : 23,806.764 ms