explain.depesz.com

PostgreSQL's explain analyze made readable

Result: baAd

Settings
# exclusive inclusive rows x rows loops node
1. 0.501 14,272.234 ↓ 126.0 126 1

Subquery Scan on subsidy_hap_request_ar_transcation (cost=22.49..22.52 rows=1 width=291) (actual time=14,271.365..14,272.234 rows=126 loops=1)

  • Filter: (subsidy_hap_request_ar_transcation.subsidy_certification_rank = 1)
  • Rows Removed by Filter: 420
2.          

CTE old_hap_transactions

3. 0.235 84.503 ↓ 132.0 132 1

Nested Loop (cost=0.84..10.25 rows=1 width=29) (actual time=0.437..84.503 rows=132 loops=1)

4. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on subsidy_hap_requests shr_1 (cost=0.00..1.88 rows=1 width=4) (actual time=0.007..0.012 rows=1 loops=1)

  • Filter: ((cid = 15760) AND (id = 1158))
  • Rows Removed by Filter: 24
5. 18.897 84.256 ↓ 132.0 132 1

Nested Loop (cost=0.84..8.34 rows=1 width=29) (actual time=0.426..84.256 rows=132 loops=1)

6. 25.453 25.453 ↓ 6,651.0 13,302 1

Index Scan Backward using idx_ar_transactions_allocations_join on ar_transactions at (cost=0.56..3.62 rows=2 width=25) (actual time=0.020..25.453 rows=13,302 loops=1)

  • Index Cond: ((cid = 15760) AND (property_id = 649300))
7. 39.906 39.906 ↓ 0.0 0 13,302

Index Only Scan using uk_subsidy_hap_request_ar_transactions_name on subsidy_hap_request_ar_transactions shrat (cost=0.28..2.33 rows=1 width=20) (actual time=0.003..0.003 rows=0 loops=13,302)

  • Index Cond: ((cid = 15760) AND (property_id = 649300) AND (subsidy_hap_request_id = shr_1.previous_subsidy_hap_request_id) AND (lease_id = at.lease_id) AND (ar_transaction_id = at.id) AND (is_adjustment = false))
  • Filter: (NOT is_adjustment)
  • Heap Fetches: 10
8. 1.107 14,271.733 ↓ 546.0 546 1

Sort (cost=12.24..12.24 rows=1 width=972) (actual time=14,271.356..14,271.733 rows=546 loops=1)

  • Sort Key: cl.display_number
  • Sort Method: quicksort Memory: 383kB
9. 1.377 14,270.626 ↓ 546.0 546 1

WindowAgg (cost=12.15..12.23 rows=1 width=972) (actual time=14,268.881..14,270.626 rows=546 loops=1)

10. 1.324 14,269.249 ↓ 546.0 546 1

Sort (cost=12.15..12.16 rows=1 width=831) (actual time=14,268.868..14,269.249 rows=546 loops=1)

  • Sort Key: cl.id, sc.id DESC
  • Sort Method: quicksort Memory: 381kB
11. 37.474 14,267.925 ↓ 546.0 546 1

GroupAggregate (cost=12.03..12.14 rows=1 width=831) (actual time=14,229.419..14,267.925 rows=546 loops=1)

  • Group Key: cl.display_number, cl.id, cl.unit_number_cache, cl.name_full, pu.number_of_bedrooms, sc.id, sc.subsidy_certification_type_id, sc.effective_date, sc.effective_through_date, sc.mat_data, sc.first_voucher_date, sct.name, sc1.start_date, siet.id, art.transaction_amount, oht.old_transaction_id, oht.transaction_amount, cl.primary_customer_id
12. 87.125 14,230.451 ↓ 1,092.0 1,092 1

Sort (cost=12.03..12.03 rows=1 width=750) (actual time=14,229.290..14,230.451 rows=1,092 loops=1)

  • Sort Key: cl.display_number, cl.id, cl.unit_number_cache, cl.name_full, pu.number_of_bedrooms, sc.id, sc.subsidy_certification_type_id, sc.effective_date, sc.effective_through_date, sc.mat_data, sc.first_voucher_date, sct.name, sc1.start_date, siet.id, art.transaction_amount, oht.old_transaction_id, oht.transaction_amount, cl.primary_customer_id
  • Sort Method: quicksort Memory: 762kB
13. 4.838 14,143.326 ↓ 1,092.0 1,092 1

Nested Loop (cost=8.18..12.02 rows=1 width=750) (actual time=63.120..14,143.326 rows=1,092 loops=1)

14. 637.266 14,135.212 ↓ 1,092.0 1,092 1

Nested Loop (cost=8.05..11.78 rows=1 width=734) (actual time=63.105..14,135.212 rows=1,092 loops=1)

  • Join Filter: ((cl.id = sc.lease_id) AND (shr.subsidy_contract_id = sc.subsidy_contract_id))
  • Rows Removed by Join Filter: 162708
15. 6,214.551 12,134.122 ↓ 340,956.0 340,956 1

Merge Join (cost=7.77..11.17 rows=1 width=132) (actual time=40.471..12,134.122 rows=340,956 loops=1)

  • Merge Cond: (shr.subsidy_contract_id = r.occupancy_type_object_id)
  • Join Filter: ((sc1.subsidy_contract_type_id = r.occupancy_type_reference_id) AND (pu.unit_type_id = r.unit_type_id) AND (ac.ar_code_type_id = r.ar_code_type_id) AND (date_trunc('month'::text, ((r.effective_datetime)::date)::timestamp with time zone) <= date_trunc('month'::text, (shr.voucher_date)::timestamp with time zone)))
  • Rows Removed by Join Filter: 5796252
16. 423.085 1,255.575 ↓ 126.1 170,478 1

Nested Loop Left Join (cost=5.29..392.58 rows=1,352 width=137) (actual time=39.982..1,255.575 rows=170,478 loops=1)

17. 292.690 662.012 ↓ 126.1 170,478 1

Nested Loop (cost=5.15..155.72 rows=1,352 width=134) (actual time=39.973..662.012 rows=170,478 loops=1)

18. 13.612 145.798 ↓ 126.0 126 1

Nested Loop Left Join (cost=4.87..14.34 rows=1 width=122) (actual time=39.947..145.798 rows=126 loops=1)

  • Join Filter: ((oht.cid = art.cid) AND (oht.property_id = art.property_id) AND (oht.lease_id = art.lease_id) AND (oht.ar_code_id = art.ar_code_id))
  • Rows Removed by Join Filter: 16510
19. 0.788 29.874 ↓ 126.0 126 1

Nested Loop (cost=4.87..14.23 rows=1 width=104) (actual time=0.133..29.874 rows=126 loops=1)

20. 0.921 27.826 ↓ 126.0 126 1

Nested Loop (cost=4.59..11.86 rows=1 width=96) (actual time=0.108..27.826 rows=126 loops=1)

21. 0.951 25.645 ↓ 126.0 126 1

Nested Loop (cost=4.30..8.50 rows=1 width=92) (actual time=0.095..25.645 rows=126 loops=1)

22. 0.464 2.038 ↓ 192.0 192 1

Nested Loop (cost=3.74..5.86 rows=1 width=75) (actual time=0.064..2.038 rows=192 loops=1)

23. 0.005 0.046 ↑ 1.0 1 1

Merge Join (cost=3.31..3.36 rows=1 width=32) (actual time=0.042..0.046 rows=1 loops=1)

  • Merge Cond: (sc1.id = shr.subsidy_contract_id)
24. 0.008 0.022 ↑ 1.0 1 1

Sort (cost=1.37..1.37 rows=1 width=16) (actual time=0.020..0.022 rows=1 loops=1)

  • Sort Key: sc1.id
  • Sort Method: quicksort Memory: 25kB
25. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on subsidy_contracts sc1 (cost=0.00..1.36 rows=1 width=16) (actual time=0.012..0.014 rows=1 loops=1)

  • Filter: (cid = 15760)
  • Rows Removed by Filter: 10
26. 0.006 0.019 ↑ 1.0 1 1

Sort (cost=1.95..1.95 rows=1 width=16) (actual time=0.018..0.019 rows=1 loops=1)

  • Sort Key: shr.subsidy_contract_id
  • Sort Method: quicksort Memory: 25kB
27. 0.013 0.013 ↑ 1.0 1 1

Seq Scan on subsidy_hap_requests shr (cost=0.00..1.94 rows=1 width=16) (actual time=0.011..0.013 rows=1 loops=1)

  • Filter: ((cid = 15760) AND (property_id = 649300) AND (id = 1158))
  • Rows Removed by Filter: 24
28. 1.528 1.528 ↓ 192.0 192 1

Index Scan using idx_cached_leases_cid_property_id on cached_leases cl (cost=0.42..2.47 rows=1 width=43) (actual time=0.019..1.528 rows=192 loops=1)

  • Index Cond: ((cid = 15760) AND (property_id = 649300))
  • Filter: ((occupancy_type_id = 6) AND (lease_status_type_id = ANY ('{3,5,6,4}'::integer[])))
  • Rows Removed by Filter: 141
29. 22.656 22.656 ↑ 1.0 1 192

Index Scan using idx_ar_transactions_allocations_join on ar_transactions art (cost=0.56..2.62 rows=1 width=33) (actual time=0.037..0.118 rows=1 loops=192)

  • Index Cond: ((cid = 15760) AND (property_id = 649300) AND (lease_id = cl.id))
  • Filter: ((ar_transaction_id IS NULL) AND (is_posted IS TRUE) AND (is_deleted IS FALSE) AND (is_reversal IS FALSE) AND (is_temporary IS FALSE) AND (ar_code_id = ANY ('{144358,144440}'::integer[])) AND (transaction_amount > '0'::numeric) AND (date_trunc('month'::text, (post_date)::timestamp with time zone) = '2020-04-01 00:00:00-06'::timestamp with time zone))
  • Rows Removed by Filter: 57
30. 1.260 1.260 ↑ 1.0 1 126

Index Scan using pk_property_units on property_units pu (cost=0.29..2.33 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=126)

  • Index Cond: ((cid = 15760) AND (id = cl.property_unit_id))
  • Filter: (property_id = 649300)
31. 1.260 1.260 ↑ 1.0 1 126

Index Scan using idx_ar_codes on ar_codes ac (cost=0.28..2.32 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=126)

  • Index Cond: (id = art.ar_code_id)
  • Filter: (cid = 15760)
32. 102.312 102.312 ↓ 132.0 132 126

CTE Scan on old_hap_transactions oht (cost=0.00..0.07 rows=1 width=38) (actual time=0.005..0.812 rows=132 loops=126)

  • Filter: ((cid = 15760) AND (property_id = 649300))
33. 223.524 223.524 ↓ 1.0 1,353 126

Index Scan using uk_application_subsidy_details_cid_application_id on application_subsidy_details asd (cost=0.28..100.83 rows=1,352 width=12) (actual time=0.011..1.774 rows=1,353 loops=126)

  • Index Cond: (cid = 15760)
34. 170.478 170.478 ↓ 0.0 0 170,478

Index Scan using pk_subsidy_income_exception_types on subsidy_income_exception_types siet (cost=0.13..0.18 rows=1 width=7) (actual time=0.001..0.001 rows=0 loops=170,478)

  • Index Cond: (id = asd.subsidy_income_exception_type_id)
35. 4,663.597 4,663.996 ↓ 5,966,749.0 5,966,749 1

Materialize (cost=2.48..2.49 rows=1 width=39) (actual time=0.352..4,663.996 rows=5,966,749 loops=1)

36. 0.138 0.399 ↓ 54.0 54 1

Sort (cost=2.48..2.49 rows=1 width=39) (actual time=0.348..0.399 rows=54 loops=1)

  • Sort Key: r.occupancy_type_object_id
  • Sort Method: quicksort Memory: 29kB
37. 0.261 0.261 ↓ 54.0 54 1

Index Scan using idx_rates_cid_property_id_unit_space_id on rates r (cost=0.43..2.47 rows=1 width=39) (actual time=0.111..0.261 rows=54 loops=1)

  • Index Cond: ((cid = 15760) AND (property_id = 649300))
  • Filter: (is_allowed AND (occupancy_type_id = 6) AND (ar_origin_id = 1))
  • Rows Removed by Filter: 73
38. 1,363.824 1,363.824 ↓ 0.0 0 340,956

Index Scan using idx_subsidy_certifications_cid_appid_certtype_certstatus on subsidy_certifications sc (cost=0.28..0.58 rows=1 width=662) (actual time=0.003..0.004 rows=0 loops=340,956)

  • Index Cond: ((cid = 15760) AND (application_id = asd.application_id) AND (subsidy_certification_status_type_id >= 4))
  • Filter: ((deleted_on IS NULL) AND (property_id = 649300) AND (date_trunc('month'::text, (anticipated_voucher_date)::timestamp with time zone) <= '2020-04-01 00:00:00-06'::timestamp with time zone))
  • Rows Removed by Filter: 0
39. 3.276 3.276 ↑ 1.0 1 1,092

Index Scan using pk_subsidy_certification_types on subsidy_certification_types sct (cost=0.13..0.22 rows=1 width=20) (actual time=0.003..0.003 rows=1 loops=1,092)

  • Index Cond: (id = sc.subsidy_certification_type_id)
Planning time : 93.747 ms
Execution time : 14,272.928 ms