explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qZbA

Settings
# exclusive inclusive rows x rows loops node
1. 103,660.182 127,685.903 ↓ 1.1 3,529 1

GroupAggregate (cost=91,770.99..96,925.18 rows=3,288 width=446) (actual time=24,006.074..127,685.903 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
2. 355.670 24,025.721 ↓ 2.7 90,188 1

Sort (cost=91,770.99..91,853.19 rows=32,882 width=370) (actual time=24,005.195..24,025.721 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: quicksort Memory: 27428kB
3. 43.432 23,670.051 ↓ 2.7 90,188 1

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

4. 23,358.566 23,626.619 ↓ 2.7 90,188 1

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

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

CTE parent_contract

6. 1.066 1.066 ↑ 1.0 2,481 1

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

  • Filter: (kind = 1)
  • Rows Removed by Filter: 2368
7. 1.032 2.860 ↑ 1.0 2,481 1

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

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

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

9. 85.657 265.193 ↓ 34.9 90,188 1

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

  • Sort Key: (COALESCE(rd.parent_contract_id, rd.contract_id))
  • Sort Method: quicksort Memory: 19280kB
10. 179.536 179.536 ↓ 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.018..179.536 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.560 ms
Execution time : 127,689.984 ms