explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dnYM : Optimization for: plan #UYm5

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.771 78.124 ↓ 112.0 448 1

GroupAggregate (cost=160.58..160.74 rows=4 width=71) (actual time=77.156..78.124 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
2. 0.880 77.353 ↓ 112.0 448 1

Sort (cost=160.58..160.59 rows=4 width=63) (actual time=77.141..77.353 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. 0.862 76.473 ↓ 112.0 448 1

Nested Loop Left Join (cost=12.07..160.54 rows=4 width=63) (actual time=3.665..76.473 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.571 56.739 ↓ 14.0 56 1

Nested Loop Left Join (cost=11.21..92.33 rows=4 width=53) (actual time=3.611..56.739 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.165 55.888 ↓ 56.0 56 1

Nested Loop (cost=11.21..90.81 rows=1 width=53) (actual time=3.597..55.888 rows=56 loops=1)

6. 3.222 55.163 ↓ 56.0 56 1

Nested Loop (cost=10.80..82.37 rows=1 width=66) (actual time=3.571..55.163 rows=56 loops=1)

  • 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
7. 0.505 4.229 ↓ 56.0 56 1

Nested Loop Left Join (cost=10.80..31.15 rows=1 width=67) (actual time=2.984..4.229 rows=56 loops=1)

  • Join Filter: ((sc.subsidy_contract_type_id = sct.id) AND (sct.subsidy_type_id = st.id))
  • Rows Removed by Join Filter: 728
8. 0.145 3.220 ↓ 56.0 56 1

Merge Join (cost=10.80..28.83 rows=1 width=64) (actual time=2.963..3.220 rows=56 loops=1)

  • Merge Cond: (st.id = pst.subsidy_type_id)
9. 0.137 3.009 ↓ 22.6 113 1

Nested Loop (cost=8.62..53.64 rows=5 width=60) (actual time=0.157..3.009 rows=113 loops=1)

10. 0.016 0.016 ↑ 1.7 3 1

Index Only Scan using pk_subsidy_types on subsidy_types st (cost=0.13..12.21 rows=5 width=4) (actual time=0.011..0.016 rows=3 loops=1)

  • Heap Fetches: 3
11. 0.124 2.856 ↓ 38.0 38 3

Materialize (cost=8.48..41.37 rows=1 width=56) (actual time=0.048..0.952 rows=38 loops=3)

12. 0.187 2.732 ↓ 56.0 56 1

Nested Loop Left Join (cost=8.48..41.37 rows=1 width=56) (actual time=0.134..2.732 rows=56 loops=1)

13. 0.064 0.193 ↓ 56.0 56 1

Nested Loop Left Join (cost=4.66..24.24 rows=1 width=56) (actual time=0.068..0.193 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))
14. 0.002 0.049 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.28..12.52 rows=1 width=36) (actual time=0.034..0.049 rows=1 loops=1)

  • Join Filter: ((sc.cid = p.cid) AND (sc.property_id = p.id))
15. 0.005 0.038 ↑ 1.0 1 1

Nested Loop (cost=0.28..11.18 rows=1 width=28) (actual time=0.024..0.038 rows=1 loops=1)

16. 0.014 0.014 ↑ 1.0 1 1

Index Scan using idx_properties_id on properties p (cost=0.28..8.30 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=1)

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

Seq Scan on property_subsidy_details psd (cost=0.00..2.87 rows=1 width=28) (actual time=0.008..0.019 rows=1 loops=1)

  • Filter: ((cid = 14706) AND (property_id = 533143))
  • Rows Removed by Filter: 57
18. 0.009 0.009 ↓ 0.0 0 1

Seq Scan on subsidy_contracts sc (cost=0.00..1.32 rows=1 width=16) (actual time=0.009..0.009 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
19. 0.058 0.080 ↓ 5.6 56 1

Bitmap Heap Scan on set_asides psa (cost=4.39..11.55 rows=10 width=25) (actual time=0.030..0.080 rows=56 loops=1)

  • Recheck Cond: ((cid = 14706) AND (property_id = 533143))
  • Filter: (deleted_on IS NULL)
  • Heap Blocks: exact=2
20. 0.022 0.022 ↓ 5.1 56 1

Bitmap Index Scan on idx_set_asides_cid_property_id_name (cost=0.00..4.38 rows=11 width=0) (actual time=0.022..0.022 rows=56 loops=1)

  • Index Cond: ((cid = 14706) AND (property_id = 533143))
21. 1.904 2.352 ↑ 1.0 1 56

Bitmap Heap Scan on property_buildings pb (cost=3.82..17.12 rows=1 width=8) (actual time=0.030..0.042 rows=1 loops=56)

  • Recheck Cond: ((cid = psa.cid) AND (cid = 14706))
  • Filter: (id = COALESCE(psa.property_building_id, id))
  • Rows Removed by Filter: 73
  • Heap Blocks: exact=1064
22. 0.448 0.448 ↑ 1.0 74 56

Bitmap Index Scan on pk_property_buildings (cost=0.00..3.82 rows=74 width=0) (actual time=0.008..0.008 rows=74 loops=56)

  • Index Cond: ((cid = psa.cid) AND (cid = 14706))
23. 0.044 0.066 ↑ 1.0 1 1

Sort (cost=2.18..2.18 rows=1 width=12) (actual time=0.036..0.066 rows=1 loops=1)

  • Sort Key: pst.subsidy_type_id
  • Sort Method: quicksort Memory: 25kB
24. 0.022 0.022 ↑ 1.0 1 1

Seq Scan on property_subsidy_types pst (cost=0.00..2.17 rows=1 width=12) (actual time=0.018..0.022 rows=1 loops=1)

  • Filter: ((deleted_on IS NULL) AND (cid = 14706) AND (property_id = 533143))
  • Rows Removed by Filter: 77
25. 0.504 0.504 ↑ 1.0 13 56

Seq Scan on subsidy_contract_types sct (cost=0.00..2.13 rows=13 width=11) (actual time=0.002..0.009 rows=13 loops=56)

26. 0.840 0.840 ↑ 1.0 27 56

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

27.          

SubPlan (forNested Loop)

28. 6.048 46.872 ↑ 1.0 1 1,512

Limit (cost=1.83..1.83 rows=1 width=12) (actual time=0.028..0.031 rows=1 loops=1,512)

29. 7.560 40.824 ↑ 4.0 1 1,512

Sort (cost=1.83..1.84 rows=4 width=12) (actual time=0.027..0.027 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
30. 33.264 33.264 ↑ 4.0 1 1,512

Seq Scan on subsidy_income_limit_versions (cost=0.00..1.81 rows=4 width=12) (actual time=0.014..0.022 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
31. 0.560 0.560 ↑ 1.0 1 56

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

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

Seq Scan on subsidy_income_level_types silt (cost=0.00..1.08 rows=8 width=9) (actual time=0.001..0.005 rows=8 loops=56)

33. 4.592 18.872 ↓ 16.0 16 56

Nested Loop Left Join (cost=0.85..17.03 rows=1 width=44) (actual time=0.028..0.337 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: 60
34. 1.736 1.736 ↓ 16.0 16 56

Index Scan using uk_subincomlimts_subtid_subilaid_famsize_subiltid_subtid_ishera on subsidy_income_limits sil2 (cost=0.43..8.50 rows=1 width=19) (actual time=0.013..0.031 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))
35. 12.544 12.544 ↓ 4.0 4 896

Index Scan using uk_subincomlimts_subtid_subilaid_famsize_subiltid_subtid_ishera on subsidy_income_limits sil (cost=0.43..8.50 rows=1 width=19) (actual time=0.010..0.014 rows=4 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))