explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mrFw

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

Sort (cost=19,068.90..19,068.91 rows=1 width=226) (actual time=10,359.359..10,359.458 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. 5.710 102.786 ↓ 2,661.0 2,661 1

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

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

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

5. 5.949 80.839 ↓ 106.4 2,661 1

Nested Loop (cost=15,106.04..17,207.70 rows=25 width=71) (actual time=47.652..80.839 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.310 1.310 ↑ 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.216..1.310 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. 27.588 73.580 ↓ 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=46.357..73.580 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.490 45.992 ↓ 0.0 0 1

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

9. 2.088 2.088 ↓ 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.088..2.088 rows=39,613 loops=1)

  • Index Cond: (property_id = '792518'::text)
10. 43.414 43.414 ↓ 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.414..43.414 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.005..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. 20.693 10,352.886 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=222.38..1,680.17 rows=1 width=226) (actual time=105.555..10,352.886 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. 6.286 10,245.067 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=221.95..1,671.69 rows=1 width=191) (actual time=105.499..10,245.067 rows=2,234 loops=1)

14. 1.845 10,211.973 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=221.40..1,663.10 rows=1 width=185) (actual time=105.464..10,211.973 rows=2,234 loops=1)

15. 3.133 10,167.682 ↓ 2,234.0 2,234 1

Nested Loop Left Join (cost=220.96..1,608.19 rows=1 width=170) (actual time=105.419..10,167.682 rows=2,234 loops=1)

16. 7.212 9,934.447 ↓ 2,234.0 2,234 1

Nested Loop (cost=0.56..1,387.77 rows=1 width=145) (actual time=105.128..9,934.447 rows=2,234 loops=1)

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

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

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

Index Scan using pk_fact_fiscal_unit_daily on fact_fiscal_unit_daily ffud (cost=0.56..1,387.74 rows=1 width=17) (actual time=2.078..3.691 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.lease_id = lease_id))
  • Rows Removed by Filter: 6,361
19. 4.468 230.102 ↑ 1.0 1 2,234

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

20. 8.936 225.634 ↑ 11.0 1 2,234

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

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

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

22. 37.978 209.996 ↑ 11.0 1 2,234

Append (cost=0.43..220.23 rows=11 width=38) (actual time=0.024..0.094 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.022..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. 11.170 11.170 ↓ 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.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
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.012..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.015..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.879 ms
Execution time : 10,360.270 ms