explain.depesz.com

PostgreSQL's explain analyze made readable

Result: S4yQ

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.612 73,239.088 ↓ 112.0 448 1

GroupAggregate (cost=341,543.29..341,543.53 rows=4 width=71) (actual time=73,238.302..73,239.088 rows=448 loops=1)

  • Group Key: p.cid, p.id, st.id, pb.id, (COALESCE(sil.is_hera, sil2.is_hera)), (COALESCE(sil.family_size, sil2.family_size)), sct.id, pb.cid, psa.id
  • Planning time: 15.930 ms
  • Execution time: 73239.863 ms
2. 1.722 73,238.476 ↓ 112.0 448 1

Sort (cost=341,543.29..341,543.30 rows=4 width=63) (actual time=73,238.281..73,238.476 rows=448 loops=1)

  • Sort Key: st.id, pb.id, (COALESCE(sil.is_hera, sil2.is_hera)), (COALESCE(sil.family_size, sil2.family_size)), sct.id, pb.cid, psa.id
  • Sort Method: quicksort Memory: 60kB
3. 2.327 73,236.754 ↓ 112.0 448 1

Nested Loop Left Join (cost=3.83..341,543.25 rows=4 width=63) (actual time=149.593..73,236.754 rows=448 loops=1)

  • Join Filter: ((COALESCE(sil.is_hera, sil2.is_hera)) = ((st.id = 2) AND COALESCE(psd.tax_credit_use_hera_limits, false)))
  • Rows Removed by Join Filter: 448
4. 0.936 45.787 ↓ 14.0 56 1

Nested Loop Left Join (cost=2.98..29.04 rows=4 width=53) (actual time=0.780..45.787 rows=56 loops=1)

  • Join Filter: CASE WHEN (sct.id = 1) THEN (silt.id = CASE WHEN (sc.start_date <= '10/01/1981'::date) THEN 6 ELSE 3 END) WHEN (sct.id = 6) THEN (silt.id = 1) WHEN (sct.id = ANY ('{7,8}'::integer[])) THEN (silt.id = 3) WHEN (sct.id = ANY ('{3,2,9,4}'::integer[])) THEN (silt.id = 6) WHEN (sct.id = 5) THEN (silt.id = 7) WHEN (st.id = 2) THEN (silt.percent = psa.median_income_percent) WHEN (sct.id = 10) THEN (silt.id = 6) WHEN (sct.id = 11) THEN (silt.id = 3) ELSE NULL::boolean END
  • Rows Removed by Join Filter: 392
5. 0.337 44.403 ↓ 56.0 56 1

Nested Loop (cost=2.98..27.20 rows=1 width=53) (actual time=0.759..44.403 rows=56 loops=1)

6. 0.275 43.058 ↓ 56.0 56 1

Nested Loop Left Join (cost=2.56..24.75 rows=1 width=66) (actual time=0.705..43.058 rows=56 loops=1)

7. 0.327 40.375 ↓ 56.0 56 1

Nested Loop Left Join (cost=2.28..20.00 rows=1 width=66) (actual time=0.646..40.375 rows=56 loops=1)

  • Join Filter: (sct.subsidy_type_id = st.id)
8. 3.073 39.992 ↓ 56.0 56 1

Hash Join (cost=2.15..17.79 rows=1 width=63) (actual time=0.639..39.992 rows=56 loops=1)

  • Hash Cond: (pst.subsidy_type_id = st.id)
  • Join Filter: (((st.id = 2) AND psd.tax_credit_is_hold_harmless AND (COALESCE(sc.start_date, psa.placed_in_service_date) > silv.effective_date)) OR (silv.id = (SubPlan 1)))
  • Rows Removed by Join Filter: 1456
9. 6.802 8.176 ↓ 56.0 1,512 1

Nested Loop (cost=0.83..16.41 rows=27 width=68) (actual time=0.206..8.176 rows=1,512 loops=1)

10. 0.133 0.534 ↓ 56.0 56 1

Nested Loop Left Join (cost=0.83..13.79 rows=1 width=60) (actual time=0.193..0.534 rows=56 loops=1)

  • Join Filter: ((psa.cid = p.cid) AND (psa.property_id = p.id) AND ((psa.property_building_id IS NOT NULL) = psd.is_tax_credit_multiple_building_project))
11. 0.003 0.154 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.56..7.36 rows=1 width=40) (actual time=0.142..0.154 rows=1 loops=1)

  • Join Filter: ((sc.cid = p.cid) AND (sc.property_id = p.id))
12. 0.003 0.130 ↑ 1.0 1 1

Nested Loop (cost=0.56..5.74 rows=1 width=32) (actual time=0.120..0.130 rows=1 loops=1)

13. 0.004 0.071 ↑ 1.0 1 1

Nested Loop (cost=0.42..4.53 rows=1 width=28) (actual time=0.064..0.071 rows=1 loops=1)

14. 0.033 0.033 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.28..2.32 rows=1 width=8) (actual time=0.031..0.033 rows=1 loops=1)

  • Index Cond: (id = 533143)
  • Filter: (cid = 14706)
15. 0.034 0.034 ↑ 1.0 1 1

Index Scan using uk_property_subsidy_details_cid_property_id on property_subsidy_details psd (cost=0.14..2.18 rows=1 width=28) (actual time=0.031..0.034 rows=1 loops=1)

  • Index Cond: ((cid = 14706) AND (property_id = 533143))
16. 0.056 0.056 ↑ 1.0 1 1

Index Only Scan using idx_property_subsidy_types_cid_pid_delon_subtypeid on property_subsidy_types pst (cost=0.14..1.19 rows=1 width=12) (actual time=0.054..0.056 rows=1 loops=1)

  • Index Cond: ((cid = 14706) AND (property_id = 533143) AND (deleted_on IS NULL))
  • Heap Fetches: 0
17. 0.021 0.021 ↓ 0.0 0 1

Seq Scan on subsidy_contracts sc (cost=0.00..1.58 rows=1 width=16) (actual time=0.020..0.021 rows=0 loops=1)

  • Filter: ((deleted_on IS NULL) AND (cid = 14706) AND (property_id = 533143) AND (now() >= start_date) AND (now() <= end_date))
  • Rows Removed by Filter: 13
18. 0.247 0.247 ↓ 5.6 56 1

Index Scan using idx_set_asides_cid_property_id_name on set_asides psa (cost=0.27..6.05 rows=10 width=25) (actual time=0.048..0.247 rows=56 loops=1)

  • Index Cond: ((cid = 14706) AND (property_id = 533143))
  • Filter: (deleted_on IS NULL)
19. 0.840 0.840 ↑ 1.0 27 56

Seq Scan on subsidy_income_limit_versions silv (cost=0.00..1.81 rows=27 width=8) (actual time=0.001..0.015 rows=27 loops=56)

20. 0.008 0.015 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=4) (actual time=0.014..0.015 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
21. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on subsidy_types st (cost=0.00..1.15 rows=5 width=4) (actual time=0.004..0.007 rows=5 loops=1)

22.          

SubPlan (forHash Join)

23. 4.536 28.728 ↑ 1.0 1 1,512

Limit (cost=2.37..2.37 rows=1 width=12) (actual time=0.017..0.019 rows=1 loops=1,512)

24. 6.048 24.192 ↑ 4.0 1 1,512

Sort (cost=2.37..2.38 rows=4 width=12) (actual time=0.016..0.016 rows=1 loops=1,512)

  • Sort Key: subsidy_income_limit_versions.property_id, subsidy_income_limit_versions.effective_date DESC, subsidy_income_limit_versions.id DESC
  • Sort Method: quicksort Memory: 25kB
25. 18.144 18.144 ↑ 4.0 1 1,512

Seq Scan on subsidy_income_limit_versions (cost=0.00..2.35 rows=4 width=12) (actual time=0.008..0.012 rows=1 loops=1,512)

  • Filter: (is_published AND (CURRENT_DATE >= effective_date) AND (CURRENT_DATE <= effective_through_date) AND (((pst.subsidy_type_id = 2) AND (property_id = 533143) AND (cid = 14706)) OR ((cid = 1) AND (property_id IS NULL))))
  • Rows Removed by Filter: 26
26. 0.056 0.056 ↓ 0.0 0 56

Index Scan using pk_subsidy_contract_types on subsidy_contract_types sct (cost=0.14..2.17 rows=1 width=11) (actual time=0.001..0.001 rows=0 loops=56)

  • Index Cond: (sc.subsidy_contract_type_id = id)
27. 2.408 2.408 ↑ 1.0 1 56

Index Only Scan using pk_property_buildings on property_buildings pb (cost=0.28..4.72 rows=1 width=8) (actual time=0.020..0.043 rows=1 loops=56)

  • Index Cond: ((cid = psa.cid) AND (cid = 14706))
  • Filter: (id = COALESCE(psa.property_building_id, id))
  • Rows Removed by Filter: 73
  • Heap Fetches: 1344
28. 1.008 1.008 ↑ 1.0 1 56

Index Scan using uk_subsidy_income_limit_areas_silvi_hmfa_code on subsidy_income_limit_areas sila (cost=0.41..2.45 rows=1 width=25) (actual time=0.018..0.018 rows=1 loops=56)

  • Index Cond: ((subsidy_income_limit_version_id = silv.id) AND (hmfa_code = psd.hmfa_code))
29. 0.448 0.448 ↑ 1.0 8 56

Seq Scan on subsidy_income_level_types silt (cost=0.00..1.24 rows=8 width=9) (actual time=0.002..0.008 rows=8 loops=56)

30. 17.752 73,188.640 ↓ 16.0 16 56

Nested Loop Left Join (cost=0.85..85,378.49 rows=1 width=44) (actual time=144.530..1,306.940 rows=16 loops=56)

  • Join Filter: ((sil.subsidy_income_limit_version_id = sil2.subsidy_income_limit_version_id) AND (sil.subsidy_income_limit_area_id = sil2.subsidy_income_limit_area_id) AND (sil.subsidy_type_id = sil2.subsidy_type_id) AND (sil.is_hera = sil2.is_hera) AND (sil.family_size = sil2.family_size))
  • Rows Removed by Join Filter: 120
31. 4,674.376 4,674.376 ↓ 16.0 16 56

Index Scan using uk_subincomlimts_subtid_subilaid_famsize_subiltid_subtid_ishera on subsidy_income_limits sil2 (cost=0.43..42,689.22 rows=1 width=19) (actual time=70.911..83.471 rows=16 loops=56)

  • Index Cond: ((subsidy_income_limit_version_id = silv.id) AND (subsidy_income_limit_area_id = sila.id) AND (subsidy_income_level_type_id = 3) AND (subsidy_type_id = pst.subsidy_type_id))
32. 68,496.512 68,496.512 ↓ 8.0 8 896

Index Scan using uk_subincomlimts_subtid_subilaid_famsize_subiltid_subtid_ishera on subsidy_income_limits sil (cost=0.43..42,689.22 rows=1 width=19) (actual time=70.451..76.447 rows=8 loops=896)

  • Index Cond: ((subsidy_income_limit_version_id = silv.id) AND (subsidy_income_limit_area_id = sila.id) AND (subsidy_income_level_type_id = silt.id) AND (subsidy_type_id = pst.subsidy_type_id))