explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yf4k

Settings
# exclusive inclusive rows x rows loops node
1. 0.040 28.224 ↓ 5.0 5 1

Sort (cost=3,448.25..3,448.26 rows=1 width=778) (actual time=28.224..28.224 rows=5 loops=1)

  • Sort Key: load_prop.property_name, (func_format_unit_number(NULL::character varying, NULL::character varying, us.building_name, us.unit_number_cache))
  • Sort Method: quicksort Memory: 26kB
2. 1.555 28.184 ↓ 5.0 5 1

Nested Loop Left Join (cost=2,986.46..3,448.24 rows=1 width=778) (actual time=23.623..28.184 rows=5 loops=1)

  • Join Filter: ((lc.cid = cll.cid) AND (lc.lease_id = cll.lease_id))
  • Rows Removed by Join Filter: 147
3. 0.016 24.779 ↓ 5.0 5 1

Nested Loop (cost=2,699.99..3,159.74 rows=1 width=875) (actual time=20.653..24.779 rows=5 loops=1)

4. 1.430 24.698 ↓ 5.0 5 1

Nested Loop (cost=2,699.84..3,159.57 rows=1 width=862) (actual time=20.609..24.698 rows=5 loops=1)

5. 0.076 22.953 ↓ 45.0 45 1

Nested Loop (cost=2,699.70..3,131.35 rows=1 width=843) (actual time=17.743..22.953 rows=45 loops=1)

6. 0.089 22.697 ↓ 45.0 45 1

Nested Loop (cost=2,699.41..3,131.01 rows=1 width=692) (actual time=17.714..22.697 rows=45 loops=1)

7. 0.064 22.068 ↓ 45.0 45 1

Nested Loop Left Join (cost=2,698.99..3,125.20 rows=1 width=676) (actual time=17.667..22.068 rows=45 loops=1)

8. 0.096 21.599 ↓ 45.0 45 1

Nested Loop (cost=2,698.70..3,124.85 rows=1 width=655) (actual time=17.644..21.599 rows=45 loops=1)

  • Join Filter: (cll.primary_customer_id = c.id)
9. 0.057 21.053 ↓ 45.0 45 1

Nested Loop Left Join (cost=2,698.28..3,121.24 rows=1 width=613) (actual time=17.612..21.053 rows=45 loops=1)

10. 0.050 20.906 ↓ 45.0 45 1

Nested Loop Left Join (cost=2,698.14..3,121.06 rows=1 width=591) (actual time=17.595..20.906 rows=45 loops=1)

11. 0.062 20.676 ↓ 45.0 45 1

Nested Loop (cost=2,697.86..3,120.24 rows=1 width=591) (actual time=17.561..20.676 rows=45 loops=1)

12. 0.095 20.524 ↓ 45.0 45 1

Nested Loop (cost=2,697.72..3,120.08 rows=1 width=591) (actual time=17.544..20.524 rows=45 loops=1)

13. 0.026 20.045 ↓ 16.0 48 1

Nested Loop (cost=2,697.43..3,117.72 rows=3 width=558) (actual time=17.515..20.045 rows=48 loops=1)

14. 0.030 0.030 ↑ 1.0 1 1

Index Scan using idx_load_prop_property_id on load_prop (cost=0.12..8.14 rows=1 width=520) (actual time=0.028..0.030 rows=1 loops=1)

15. 2.603 19.989 ↓ 16.0 48 1

Bitmap Heap Scan on cached_lease_logs cll (cost=2,697.31..3,109.55 rows=3 width=42) (actual time=17.479..19.989 rows=48 loops=1)

  • Recheck Cond: ((cid = 235) AND (property_id = load_prop.property_id) AND ('2020-09-09'::date <= apply_through_post_date))
  • Filter: (('2020-09-09'::date >= reporting_post_date) AND (notice_date >= '2018-01-01'::date) AND (notice_date <= '2020-09-09'::date) AND (is_post_date_ignored = 0))
  • Rows Removed by Filter: 479
  • Heap Blocks: exact=342
16. 0.161 17.386 ↓ 0.0 0 1

BitmapAnd (cost=2,697.31..2,697.31 rows=106 width=0) (actual time=17.386..17.386 rows=0 loops=1)

17. 1.089 1.089 ↓ 4.0 4,078 1

Bitmap Index Scan on idx_cached_lease_logs_cid_property_id_lease_id_reporting_month (cost=0.00..30.74 rows=1,031 width=0) (actual time=1.089..1.089 rows=4,078 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = load_prop.property_id))
18. 16.136 16.136 ↓ 1.0 146,488 1

Bitmap Index Scan on idx_cached_lease_logs_apply_through_post_date (cost=0.00..2,665.85 rows=142,590 width=0) (actual time=16.136..16.136 rows=146,488 loops=1)

  • Index Cond: (apply_through_post_date >= '2020-09-09'::date)
19. 0.384 0.384 ↑ 1.0 1 48

Index Scan using idx_cust_milt_details_customer_id on customer_military_details cmd (cost=0.29..0.78 rows=1 width=37) (actual time=0.008..0.008 rows=1 loops=48)

  • Index Cond: (customer_id = cll.primary_customer_id)
  • Filter: (cid = 235)
20. 0.090 0.090 ↑ 1.0 1 45

Index Scan using pk_military_pay_grades on military_pay_grades mpg (cost=0.14..0.16 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=45)

  • Index Cond: (id = cmd.military_pay_grade_id)
21. 0.180 0.180 ↑ 1.0 1 45

Index Scan using uk_property_details_company_property_id on property_details pd (cost=0.28..0.82 rows=1 width=12) (actual time=0.004..0.004 rows=1 loops=45)

  • Index Cond: ((cid = cll.cid) AND (cid = 235) AND (property_id = cll.property_id))
22. 0.090 0.090 ↑ 1.0 1 45

Index Scan using pk_military_installations on military_installations mi (cost=0.14..0.18 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=45)

  • Index Cond: (id = pd.military_installation_id)
23. 0.450 0.450 ↑ 1.0 1 45

Index Scan using idx_customers_id on customers c (cost=0.42..3.60 rows=1 width=46) (actual time=0.010..0.010 rows=1 loops=45)

  • Index Cond: (id = cmd.customer_id)
  • Filter: (cid = 235)
24. 0.405 0.405 ↑ 1.0 1 45

Index Scan using idx_phone_numbers_temp_customer_id on phone_numbers_temp pnt (cost=0.29..0.34 rows=1 width=37) (actual time=0.009..0.009 rows=1 loops=45)

  • Index Cond: (customer_id = c.id)
  • Filter: ((cid = 235) AND (cid = c.cid))
25. 0.540 0.540 ↑ 1.0 1 45

Index Scan using pk_unit_spaces on unit_spaces us (cost=0.42..5.80 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=45)

  • Index Cond: ((cid = 235) AND (id = cll.unit_space_id))
26. 0.180 0.180 ↑ 1.0 1 45

Index Scan using idx_unit_types_id on unit_types ut (cost=0.29..0.34 rows=1 width=167) (actual time=0.004..0.004 rows=1 loops=45)

  • Index Cond: (id = us.unit_type_id)
  • Filter: (cid = 235)
27. 0.315 0.315 ↓ 0.0 0 45

Index Scan using pk_military_installations on military_installations mi_future (cost=0.14..27.91 rows=1 width=27) (actual time=0.007..0.007 rows=0 loops=45)

  • Index Cond: ((id = cmd.future_military_installation_id) AND (id = ANY ('{88,49,32,30,7,13,100,43,97,52,131,114,115,113,116,112,34,46,5,17,104,55,8,18,1,54,2,64,66,105,76,101,92,98,84,75,89,87,93,106,90,77,94,102,71,73,99,85,82,107,91,110,111,154,108,86,67,83,69,103,96,78,109,68,56,39,28,31,129,25,62,44,79,19,186,123,65,42,35,45,57,63,58,4,9,6,20,36,10,118,127,124,119,125,117,126,120,122,130,128,121,3,29,188,40,23,26,164,158,142,165,151,143,132,156,133,166,167,152,144,170,145,134,168,135,171,172,173,136,137,138,157,141,161,174,139,175,150,146,160,169,149,148,140,153,163,147,159,162,47,184,41,21,183,14,37,74,59,60,70,24,80,15,27,38,53,61,50,11,81,22,16,95,155,51,12,185,33,48,72}'::integer[])))
28. 0.065 0.065 ↑ 1.0 1 5

Index Scan using pk_military_ranks on military_ranks mr (cost=0.14..0.17 rows=1 width=21) (actual time=0.013..0.013 rows=1 loops=5)

  • Index Cond: (id = cmd.military_rank_id)
29. 0.090 1.850 ↑ 1.1 30 5

GroupAggregate (cost=286.47..287.15 rows=34 width=16) (actual time=0.353..0.370 rows=30 loops=5)

  • Group Key: lc.cid, lc.lease_id
30. 0.063 1.760 ↓ 1.3 43 5

Sort (cost=286.47..286.55 rows=34 width=12) (actual time=0.349..0.352 rows=43 loops=5)

  • Sort Key: lc.lease_id
  • Sort Method: quicksort Memory: 31kB
31. 0.023 1.697 ↓ 3.9 134 1

Nested Loop (cost=0.42..285.60 rows=34 width=12) (actual time=0.070..1.697 rows=134 loops=1)

32. 0.006 0.006 ↑ 1.0 1 1

Seq Scan on load_prop load_prop_1 (cost=0.00..1.01 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=1)

33. 1.668 1.668 ↓ 3.9 134 1

Index Scan using idx_lease_customers_cid_property_id on lease_customers lc (cost=0.42..284.25 rows=34 width=16) (actual time=0.061..1.668 rows=134 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = load_prop_1.property_id))
  • Filter: ((customer_type_id <> 4) AND (lease_status_type_id = ANY ('{4,5,6}'::integer[])))
  • Rows Removed by Filter: 557