explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nvYw

Settings
# exclusive inclusive rows x rows loops node
1. 0.497 133,168.286 ↓ 126.0 126 1

Subquery Scan on subsidy_hap_request_ar_transcation (cost=25.28..25.32 rows=1 width=291) (actual time=133,167.425..133,168.286 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.203 74.467 ↓ 132.0 132 1

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

4. 0.013 0.013 ↑ 1.0 1 1

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

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

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

6. 23.035 23.035 ↓ 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.027..23.035 rows=13,302 loops=1)

  • Index Cond: ((cid = 15760) AND (property_id = 649300))
7. 26.604 26.604 ↓ 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.002..0.002 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.249 133,167.789 ↓ 546.0 546 1

Sort (cost=15.03..15.04 rows=1 width=972) (actual time=133,167.416..133,167.789 rows=546 loops=1)

  • Sort Key: cl.display_number
  • Sort Method: quicksort Memory: 383kB
9. 1.373 133,166.540 ↓ 546.0 546 1

WindowAgg (cost=14.95..15.02 rows=1 width=972) (actual time=133,164.773..133,166.540 rows=546 loops=1)

10. 1.538 133,165.167 ↓ 546.0 546 1

Sort (cost=14.95..14.95 rows=1 width=831) (actual time=133,164.760..133,165.167 rows=546 loops=1)

  • Sort Key: cl.id, sc.id DESC
  • Sort Method: quicksort Memory: 381kB
11. 36.080 133,163.629 ↓ 546.0 546 1

GroupAggregate (cost=14.82..14.94 rows=1 width=831) (actual time=133,126.740..133,163.629 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. 47.002 133,127.549 ↓ 1,092.0 1,092 1

Sort (cost=14.82..14.83 rows=1 width=750) (actual time=133,126.605..133,127.549 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. 5.324 133,080.547 ↓ 1,092.0 1,092 1

Nested Loop Left Join (cost=5.00..14.81 rows=1 width=750) (actual time=183.704..133,080.547 rows=1,092 loops=1)

14. 6.861 133,074.131 ↓ 1,092.0 1,092 1

Nested Loop (cost=4.87..14.63 rows=1 width=747) (actual time=183.696..133,074.131 rows=1,092 loops=1)

15. 119.367 133,058.534 ↓ 1,092.0 1,092 1

Nested Loop Left Join (cost=4.59..13.90 rows=1 width=751) (actual time=183.680..133,058.534 rows=1,092 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: 143070
16. 70.841 132,742.607 ↓ 1,092.0 1,092 1

Nested Loop (cost=4.59..13.80 rows=1 width=745) (actual time=183.241..132,742.607 rows=1,092 loops=1)

  • Join Filter: (r.unit_type_id = pu.unit_type_id)
  • Rows Removed by Join Filter: 18564
17. 169.714 132,514.518 ↓ 19,656.0 19,656 1

Nested Loop (cost=4.29..11.43 rows=1 width=749) (actual time=87.456..132,514.518 rows=19,656 loops=1)

  • Join Filter: ((sc.subsidy_contract_id = shr.subsidy_contract_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)))
18. 1,233.512 132,226.868 ↓ 19,656.0 19,656 1

Nested Loop (cost=4.29..9.45 rows=1 width=769) (actual time=87.434..132,226.868 rows=19,656 loops=1)

  • Join Filter: (art.lease_id = cl.id)
  • Rows Removed by Join Filter: 1533420
19. 2,552.842 127,140.780 ↓ 19,656.0 19,656 1

Nested Loop (cost=3.87..6.94 rows=1 width=738) (actual time=87.007..127,140.780 rows=19,656 loops=1)

  • Join Filter: (sc.lease_id = art.lease_id)
  • Rows Removed by Join Filter: 2928744
20. 1,842.286 3,422.738 ↓ 140,400.0 140,400 1

Merge Join (cost=3.31..4.30 rows=1 width=729) (actual time=0.297..3,422.738 rows=140,400 loops=1)

  • Merge Cond: (sc.subsidy_contract_id = r.occupancy_type_object_id)
  • Join Filter: ((sc1.subsidy_contract_type_id = r.occupancy_type_reference_id) AND (ac.ar_code_type_id = r.ar_code_type_id))
  • Rows Removed by Join Filter: 1801800
21. 88.190 146.868 ↓ 105.0 53,950 1

Nested Loop (cost=0.83..482.75 rows=514 width=706) (actual time=0.063..146.868 rows=53,950 loops=1)

22. 3.972 15.778 ↓ 108.3 650 1

Nested Loop (cost=0.55..383.42 rows=6 width=690) (actual time=0.042..15.778 rows=650 loops=1)

23. 1.971 7.256 ↓ 108.3 650 1

Nested Loop (cost=0.42..382.17 rows=6 width=674) (actual time=0.033..7.256 rows=650 loops=1)

  • Join Filter: (sc.subsidy_contract_id = sc1.id)
24. 0.011 0.011 ↑ 1.0 1 1

Index Scan using pk_subsidy_contracts on subsidy_contracts sc1 (cost=0.14..2.17 rows=1 width=16) (actual time=0.008..0.011 rows=1 loops=1)

  • Index Cond: (cid = 15760)
25. 5.274 5.274 ↓ 4.5 650 1

Index Scan using idx_subsidy_certifications_cid_appid_certtype_certstatus on subsidy_certifications sc (cost=0.28..375.32 rows=144 width=662) (actual time=0.022..5.274 rows=650 loops=1)

  • Index Cond: ((cid = 15760) 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: 232
26. 4.550 4.550 ↑ 1.0 1 650

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

  • Index Cond: (id = sc.subsidy_certification_type_id)
27. 42.727 42.900 ↑ 1.0 83 650

Materialize (cost=0.28..83.35 rows=83 width=16) (actual time=0.001..0.066 rows=83 loops=650)

28. 0.173 0.173 ↑ 1.0 83 1

Index Scan using pk_ar_codes on ar_codes ac (cost=0.28..82.94 rows=83 width=16) (actual time=0.015..0.173 rows=83 loops=1)

  • Index Cond: (cid = 15760)
29. 1,433.434 1,433.584 ↓ 1,888,269.0 1,888,269 1

Sort (cost=2.48..2.49 rows=1 width=39) (actual time=0.205..1,433.584 rows=1,888,269 loops=1)

  • Sort Key: r.occupancy_type_object_id
  • Sort Method: quicksort Memory: 29kB
30. 0.150 0.150 ↓ 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.059..0.150 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
31. 121,165.200 121,165.200 ↓ 21.0 21 140,400

Index Scan using idx_ar_transactions_ar_codes_join on ar_transactions art (cost=0.56..2.62 rows=1 width=33) (actual time=0.387..0.863 rows=21 loops=140,400)

  • Index Cond: ((cid = 15760) AND (property_id = 649300) AND (ar_code_id = ac.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: 1150
32. 3,852.576 3,852.576 ↓ 79.0 79 19,656

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.011..0.196 rows=79 loops=19,656)

  • 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: 34
33. 117.936 117.936 ↑ 1.0 1 19,656

Seq Scan on subsidy_hap_requests shr (cost=0.00..1.94 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=19,656)

  • Filter: ((cid = 15760) AND (property_id = 649300) AND (id = 1158))
  • Rows Removed by Filter: 17
34. 157.248 157.248 ↑ 1.0 1 19,656

Index Scan using pk_property_units on property_units pu (cost=0.29..2.33 rows=1 width=20) (actual time=0.007..0.008 rows=1 loops=19,656)

  • Index Cond: ((cid = 15760) AND (id = cl.property_unit_id))
  • Filter: (property_id = 649300)
35. 196.560 196.560 ↓ 132.0 132 1,092

CTE Scan on old_hap_transactions oht (cost=0.00..0.07 rows=1 width=38) (actual time=0.002..0.180 rows=132 loops=1,092)

  • Filter: ((cid = 15760) AND (property_id = 649300))
36. 8.736 8.736 ↑ 1.0 1 1,092

Index Scan using uk_application_subsidy_details_cid_application_id on application_subsidy_details asd (cost=0.28..0.72 rows=1 width=12) (actual time=0.008..0.008 rows=1 loops=1,092)

  • Index Cond: ((cid = 15760) AND (application_id = sc.application_id))
37. 1.092 1.092 ↓ 0.0 0 1,092

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=1,092)

  • Index Cond: (id = asd.subsidy_income_exception_type_id)
Planning time : 105.370 ms
Execution time : 133,168.955 ms