explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r0qr

Settings
# exclusive inclusive rows x rows loops node
1. 6.482 10,307.544 ↓ 2,234.0 2,234 1

Sort (cost=19,069.76..19,069.77 rows=1 width=226) (actual time=10,307.447..10,307.544 rows=2,234 loops=1)

  • Sort Key: du.building_name, du.unit_number, du.display_number
  • Sort Method: quicksort Memory: 411kB
2.          

CTE resident_charges

3. 4.172 96.944 ↓ 2,661.0 2,661 1

Sort (cost=17,388.72..17,388.72 rows=1 width=112) (actual time=95.731..96.944 rows=2,661 loops=1)

  • Sort Key: fat.report_event_local_date, dcc.charge_code_name
  • Sort Method: quicksort Memory: 476kB
4. 1.121 92.772 ↓ 2,661.0 2,661 1

Nested Loop (cost=15,106.33..17,388.71 rows=1 width=112) (actual time=48.337..92.772 rows=2,661 loops=1)

5. 5.316 78.346 ↓ 106.4 2,661 1

Nested Loop (cost=15,106.04..17,207.70 rows=25 width=71) (actual time=48.307..78.346 rows=2,661 loops=1)

  • Join Filter: ((fat.report_event_local_date >= dp.start_date) AND (fat.report_event_local_date <= dp.end_date))
  • Rows Removed by Join Filter: 32,286
6. 1.277 1.277 ↑ 1.0 1 1

Index Scan using dim_period_gpr_report1 on dim_period dp (cost=0.56..27.38 rows=1 width=19) (actual time=1.209..1.277 rows=1 loops=1)

  • Index Cond: ((client_id = '16362'::text) AND (property_id = '792518'::text))
  • Filter: (is_current_record AND (ledger_type = 'ar'::text) AND (post_month = '2020-01-01'::date))
  • Rows Removed by Filter: 1,776
7. 25.104 71.753 ↓ 154.6 34,947 1

Bitmap Heap Scan on fact_ar_transaction fat (cost=15,105.48..17,176.93 rows=226 width=63) (actual time=47.016..71.753 rows=34,947 loops=1)

  • Recheck Cond: ((property_id = '792518'::text) AND (client_id = '16362'::text))
  • Filter: ((NOT COALESCE(is_temporary, false)) AND (charge_type_id <> '1'::text))
  • Rows Removed by Filter: 4,666
  • Heap Blocks: exact=3,094
8. 0.538 46.649 ↓ 0.0 0 1

BitmapAnd (cost=15,105.48..15,105.48 rows=527 width=0) (actual time=46.649..46.649 rows=0 loops=1)

9. 2.137 2.137 ↓ 5.3 39,613 1

Bitmap Index Scan on fact_ar_transaction_property_id (cost=0.00..156.59 rows=7,487 width=0) (actual time=2.137..2.137 rows=39,613 loops=1)

  • Index Cond: (property_id = '792518'::text)
10. 43.974 43.974 ↓ 1.0 739,745 1

Bitmap Index Scan on fact_ar_transaction_client_id (cost=0.00..14,948.53 rows=735,479 width=0) (actual time=43.974..43.974 rows=739,745 loops=1)

  • Index Cond: (client_id = '16362'::text)
11. 13.305 13.305 ↑ 1.0 1 2,661

Index Scan using dim_charge_code_charge_code_id on dim_charge_code dcc (cost=0.29..7.23 rows=1 width=26) (actual time=0.004..0.005 rows=1 loops=2,661)

  • Index Cond: (charge_code_id = fat.charge_code_id)
  • Filter: (client_id = '16362'::text)
  • Rows Removed by Filter: 1
12. 19.210 10,301.062 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=222.38..1,681.03 rows=1 width=226) (actual time=98.545..10,301.062 rows=2,234 loops=1)

  • Join Filter: ((rc.end_date)::timestamp without time zone <@ du.fiscal_efficacy)
  • Rows Removed by Join Filter: 109,359
13. 5.172 10,194.726 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=221.95..1,672.55 rows=1 width=191) (actual time=98.503..10,194.726 rows=2,234 loops=1)

14. 1.130 10,162.746 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=221.40..1,663.96 rows=1 width=185) (actual time=98.474..10,162.746 rows=2,234 loops=1)

15. 2.601 10,119.170 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=220.96..1,609.05 rows=1 width=170) (actual time=98.439..10,119.170 rows=2,234 loops=1)

16. 6.447 9,893.169 ↓ 2,234.0 2,234 1

Nested Loop (cost=0.56..1,388.63 rows=1 width=145) (actual time=98.206..9,893.169 rows=2,234 loops=1)

17. 99.564 99.564 ↓ 2,661.0 2,661 1

CTE Scan on resident_charges rc (cost=0.00..0.02 rows=1 width=164) (actual time=95.735..99.564 rows=2,661 loops=1)

18. 9,787.158 9,787.158 ↑ 1.0 1 2,661

Index Scan using pk_fact_fiscal_unit_daily on fact_fiscal_unit_daily ffud (cost=0.56..1,388.60 rows=1 width=24) (actual time=2.071..3.678 rows=1 loops=2,661)

  • Index Cond: ((date_key = rc.end_date) AND (client_id = rc.client_id))
  • Filter: ((lease_id IS NOT NULL) AND (rc.property_id = property_id) AND (rc.lease_id = lease_id))
  • Rows Removed by Filter: 6,361
19. 6.702 223.400 ↑ 1.0 1 2,234

Limit (cost=220.40..220.40 rows=1 width=2,506) (actual time=0.098..0.100 rows=1 loops=2,234)

20. 8.936 216.698 ↑ 11.0 1 2,234

Sort (cost=220.40..220.43 rows=11 width=2,506) (actual time=0.097..0.097 rows=1 loops=2,234)

  • Sort Key: f.report_event_local_date DESC, f.event_id DESC
  • Sort Method: quicksort Memory: 25kB
21. 4.468 207.762 ↑ 11.0 1 2,234

Result (cost=0.43..220.34 rows=11 width=2,506) (actual time=0.026..0.093 rows=1 loops=2,234)

22. 33.510 203.294 ↑ 11.0 1 2,234

Append (cost=0.43..220.23 rows=11 width=38) (actual time=0.024..0.091 rows=1 loops=2,234)

23. 75.956 75.956 ↑ 1.0 1 2,234

Index Scan using fact_lease_event_lease_id on fact_lease_event f (cost=0.43..104.67 rows=1 width=36) (actual time=0.021..0.034 rows=1 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 7
24. 8.936 8.936 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2019_q4_lease_id_idx on fact_lease_event_y2019_q4 f_1 (cost=0.28..8.30 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 0
25. 11.170 11.170 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2020_q1_lease_id_idx on fact_lease_event_y2020_q1 f_2 (cost=0.28..10.33 rows=1 width=38) (actual time=0.005..0.005 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 0
26. 22.340 22.340 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2020_q2_lease_id_idx on fact_lease_event_y2020_q2 f_3 (cost=0.29..16.01 rows=1 width=38) (actual time=0.010..0.010 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 2
27. 8.936 8.936 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2019_q3_lease_id_idx on fact_lease_event_y2019_q3 f_4 (cost=0.28..8.30 rows=1 width=38) (actual time=0.004..0.004 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 0
28. 6.702 6.702 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2019_q1_lease_id_idx on fact_lease_event_y2019_q1 f_5 (cost=0.27..8.30 rows=1 width=38) (actual time=0.003..0.003 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
29. 6.702 6.702 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2019_q2_lease_id_idx on fact_lease_event_y2019_q2 f_6 (cost=0.28..8.30 rows=1 width=38) (actual time=0.003..0.003 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
30. 4.468 4.468 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2018_q4_lease_id_idx on fact_lease_event_y2018_q4 f_7 (cost=0.14..8.17 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
31. 15.638 15.638 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2020_q3_lease_id_idx on fact_lease_event_y2020_q3 f_8 (cost=0.29..31.45 rows=1 width=38) (actual time=0.007..0.007 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
  • Rows Removed by Filter: 0
32. 4.468 4.468 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2018_q3_lease_id_idx on fact_lease_event_y2018_q3 f_9 (cost=0.14..8.17 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
33. 4.468 4.468 ↓ 0.0 0 2,234

Index Scan using fact_lease_event_y2018_q2_lease_id_idx on fact_lease_event_y2018_q2 f_10 (cost=0.14..8.17 rows=1 width=38) (actual time=0.002..0.002 rows=0 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((rc.end_date >= report_event_local_date) AND (rc.end_date <= report_end_event_on) AND (client_id = ffud.client_id) AND (event_source = 'cached_lease_logs'::text))
34. 4.468 42.446 ↑ 1.0 1 2,234

Limit (cost=0.43..54.89 rows=1 width=742) (actual time=0.018..0.019 rows=1 loops=2,234)

35. 37.978 37.978 ↑ 1.0 1 2,234

Index Scan using dim_lease_lease_id on dim_lease d (cost=0.43..54.89 rows=1 width=742) (actual time=0.017..0.017 rows=1 loops=2,234)

  • Index Cond: (lease_id = ffud.lease_id)
  • Filter: ((lease_status <> 'Opening Log'::text) AND (client_id = ffud.client_id) AND ((rc.end_date)::timestamp without time zone <@ fiscal_efficacy))
  • Rows Removed by Filter: 6
36. 26.808 26.808 ↑ 1.0 1 2,234

Index Scan using dim_customer_bk on dim_customer dc (cost=0.56..8.58 rows=1 width=28) (actual time=0.011..0.012 rows=1 loops=2,234)

  • Index Cond: ((client_id = COALESCE(f.client_id, d.client_id)) AND (customer_id = COALESCE(f.primary_customer_id, d.primary_customer_id)))
37. 87.126 87.126 ↓ 50.0 50 2,234

Index Scan using dim_unit_bk on dim_unit du (cost=0.43..8.45 rows=1 width=35) (actual time=0.014..0.039 rows=50 loops=2,234)

  • Index Cond: ((client_id = COALESCE(f.client_id, d.client_id)) AND (unit_space_id = COALESCE(f.unit_space_id, d.unit_space_id)))
Planning time : 4.934 ms
Execution time : 10,308.325 ms