explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Tl5xF

Settings
# exclusive inclusive rows x rows loops node
1. 0.511 500.300 ↓ 126.0 126 1

Subquery Scan on subsidy_hap_request_ar_transcation (cost=29.65..29.69 rows=1 width=291) (actual time=499.422..500.300 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.231 73.104 ↓ 132.0 132 1

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

4. 0.010 0.010 ↑ 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.010 rows=1 loops=1)

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

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

6. 21.607 21.607 ↓ 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.017..21.607 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.135 499.789 ↓ 546.0 546 1

Sort (cost=19.40..19.41 rows=1 width=972) (actual time=499.413..499.789 rows=546 loops=1)

  • Sort Key: cl.display_number
  • Sort Method: quicksort Memory: 383kB
9. 1.328 498.654 ↓ 546.0 546 1

WindowAgg (cost=19.32..19.39 rows=1 width=972) (actual time=496.953..498.654 rows=546 loops=1)

10. 1.333 497.326 ↓ 546.0 546 1

Sort (cost=19.32..19.32 rows=1 width=831) (actual time=496.942..497.326 rows=546 loops=1)

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

GroupAggregate (cost=19.19..19.31 rows=1 width=831) (actual time=460.168..495.993 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. 42.915 460.962 ↓ 1,092.0 1,092 1

Sort (cost=19.19..19.20 rows=1 width=750) (actual time=460.040..460.962 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. 2.556 418.047 ↓ 1,092.0 1,092 1

Nested Loop Left Join (cost=2.95..19.18 rows=1 width=750) (actual time=31.224..418.047 rows=1,092 loops=1)

14. 3.457 414.399 ↓ 1,092.0 1,092 1

Nested Loop (cost=2.81..19.00 rows=1 width=747) (actual time=31.217..414.399 rows=1,092 loops=1)

15. 107.867 407.666 ↓ 1,092.0 1,092 1

Nested Loop Left Join (cost=2.53..18.27 rows=1 width=751) (actual time=31.197..407.666 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. 25.520 113.067 ↓ 1,092.0 1,092 1

Nested Loop (cost=2.53..18.17 rows=1 width=745) (actual time=0.264..113.067 rows=1,092 loops=1)

  • Join Filter: ((shr.subsidy_contract_id = r.occupancy_type_object_id) AND (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: 28392
17. 1.738 32.401 ↓ 546.0 546 1

Nested Loop (cost=2.11..15.64 rows=1 width=766) (actual time=0.150..32.401 rows=546 loops=1)

  • Join Filter: (shr.subsidy_contract_id = sc1.id)
18. 1.815 29.571 ↓ 546.0 546 1

Nested Loop (cost=1.97..15.41 rows=1 width=754) (actual time=0.139..29.571 rows=546 loops=1)

19. 1.754 26.118 ↓ 546.0 546 1

Nested Loop (cost=1.68..12.06 rows=1 width=750) (actual time=0.126..26.118 rows=546 loops=1)

20. 1.137 23.272 ↓ 546.0 546 1

Nested Loop (cost=1.55..11.82 rows=1 width=734) (actual time=0.119..23.272 rows=546 loops=1)

  • Join Filter: (shr.subsidy_contract_id = sc.subsidy_contract_id)
21. 0.629 19.237 ↓ 126.0 126 1

Nested Loop (cost=1.27..9.45 rows=1 width=84) (actual time=0.076..19.237 rows=126 loops=1)

22. 0.429 17.726 ↓ 126.0 126 1

Nested Loop (cost=1.27..7.49 rows=1 width=76) (actual time=0.061..17.726 rows=126 loops=1)

23. 0.517 16.793 ↓ 126.0 126 1

Nested Loop (cost=0.98..5.12 rows=1 width=68) (actual time=0.050..16.793 rows=126 loops=1)

24. 0.916 0.916 ↓ 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.018..0.916 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
25. 15.360 15.360 ↑ 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.021..0.080 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
26. 0.504 0.504 ↑ 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.004..0.004 rows=1 loops=126)

  • Index Cond: (id = art.ar_code_id)
  • Filter: (cid = 15760)
27. 0.882 0.882 ↑ 1.0 1 126

Seq Scan on subsidy_hap_requests shr (cost=0.00..1.94 rows=1 width=16) (actual time=0.004..0.007 rows=1 loops=126)

  • Filter: ((cid = 15760) AND (property_id = 649300) AND (id = 1158))
  • Rows Removed by Filter: 24
28. 2.898 2.898 ↓ 4.0 4 126

Index Scan using idx_subsidy_certifications_lease_id on subsidy_certifications sc (cost=0.28..2.33 rows=1 width=662) (actual time=0.009..0.023 rows=4 loops=126)

  • Index Cond: ((cid = 15760) AND (lease_id = cl.id))
  • Filter: ((deleted_on IS NULL) AND (subsidy_certification_status_type_id >= 4) 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: 1
29. 1.092 1.092 ↑ 1.0 1 546

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

  • Index Cond: (id = sc.subsidy_certification_type_id)
30. 1.638 1.638 ↑ 1.0 1 546

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

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

Index Scan using pk_subsidy_contracts on subsidy_contracts sc1 (cost=0.14..0.20 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=546)

  • Index Cond: ((cid = 15760) AND (id = sc.subsidy_contract_id))
32. 55.146 55.146 ↓ 54.0 54 546

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.022..0.101 rows=54 loops=546)

  • 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
33. 186.732 186.732 ↓ 132.0 132 1,092

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

  • Filter: ((cid = 15760) AND (property_id = 649300))
34. 3.276 3.276 ↑ 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.003..0.003 rows=1 loops=1,092)

  • Index Cond: ((cid = 15760) AND (application_id = sc.application_id))
35. 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 : 20.300 ms
Execution time : 500.874 ms