explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fQNw

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 25.834 ↓ 5.0 5 1

Sort (cost=4,795.53..4,795.53 rows=1 width=779) (actual time=25.834..25.834 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.079 25.814 ↓ 5.0 5 1

Nested Loop Left Join (cost=3,411.74..4,795.52 rows=1 width=779) (actual time=19.209..25.814 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.011 23.855 ↓ 5.0 5 1

Nested Loop (cost=3,125.28..4,507.01 rows=1 width=876) (actual time=18.081..23.855 rows=5 loops=1)

4. 0.904 23.824 ↓ 5.0 5 1

Nested Loop (cost=3,125.13..4,506.84 rows=1 width=863) (actual time=18.072..23.824 rows=5 loops=1)

5. 0.046 22.740 ↓ 45.0 45 1

Nested Loop (cost=3,124.99..4,478.62 rows=1 width=844) (actual time=17.315..22.740 rows=45 loops=1)

6. 0.035 22.604 ↓ 45.0 45 1

Nested Loop (cost=3,124.70..4,478.28 rows=1 width=693) (actual time=17.304..22.604 rows=45 loops=1)

7. 0.047 22.389 ↓ 45.0 45 1

Nested Loop Left Join (cost=3,124.28..4,472.48 rows=1 width=677) (actual time=17.288..22.389 rows=45 loops=1)

8. 0.038 22.297 ↓ 45.0 45 1

Nested Loop Left Join (cost=3,124.14..4,472.30 rows=1 width=655) (actual time=17.279..22.297 rows=45 loops=1)

9. 0.019 22.169 ↓ 45.0 45 1

Nested Loop (cost=3,123.85..4,471.48 rows=1 width=655) (actual time=17.266..22.169 rows=45 loops=1)

10. 4.934 22.060 ↓ 45.0 45 1

Hash Right Join (cost=3,123.72..4,471.33 rows=1 width=655) (actual time=17.249..22.060 rows=45 loops=1)

  • Hash Cond: ((pnt.cid = c.cid) AND (pnt.customer_id = c.id))
11. 6.759 6.759 ↑ 1.0 50,730 1

Seq Scan on phone_numbers_temp pnt (cost=0.00..967.12 rows=50,730 width=38) (actual time=0.015..6.759 rows=50,730 loops=1)

  • Filter: (cid = 235)
12. 0.032 10.367 ↓ 45.0 45 1

Hash (cost=3,123.70..3,123.70 rows=1 width=633) (actual time=10.366..10.367 rows=45 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
13. 0.071 10.335 ↓ 45.0 45 1

Nested Loop (cost=2,698.14..3,123.70 rows=1 width=633) (actual time=9.142..10.335 rows=45 loops=1)

  • Join Filter: (cll.primary_customer_id = c.id)
14. 0.055 10.084 ↓ 45.0 45 1

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

15. 0.013 9.933 ↓ 16.0 48 1

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

16. 0.025 0.025 ↑ 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.024..0.025 rows=1 loops=1)

17. 0.913 9.895 ↓ 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=9.061..9.895 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
18. 0.130 8.982 ↓ 0.0 0 1

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

19. 0.483 0.483 ↓ 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=0.483..0.483 rows=4,078 loops=1)

  • Index Cond: ((cid = 235) AND (property_id = load_prop.property_id))
20. 8.369 8.369 ↓ 1.0 146,483 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=8.369..8.369 rows=146,483 loops=1)

  • Index Cond: (apply_through_post_date >= '2020-09-09'::date)
21. 0.096 0.096 ↑ 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.002..0.002 rows=1 loops=48)

  • Index Cond: (customer_id = cll.primary_customer_id)
  • Filter: (cid = 235)
22. 0.180 0.180 ↑ 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.004..0.004 rows=1 loops=45)

  • Index Cond: (id = cmd.customer_id)
  • Filter: (cid = 235)
23. 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)
24. 0.090 0.090 ↑ 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.002..0.002 rows=1 loops=45)

  • Index Cond: ((cid = cll.cid) AND (cid = 235) AND (property_id = cll.property_id))
25. 0.045 0.045 ↑ 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.001..0.001 rows=1 loops=45)

  • Index Cond: (id = pd.military_installation_id)
26. 0.180 0.180 ↑ 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.004..0.004 rows=1 loops=45)

  • Index Cond: ((cid = 235) AND (id = cll.unit_space_id))
27. 0.090 0.090 ↑ 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.002..0.002 rows=1 loops=45)

  • Index Cond: (id = us.unit_type_id)
  • Filter: (cid = 235)
28. 0.180 0.180 ↓ 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.004..0.004 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[])))
29. 0.020 0.020 ↑ 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.004..0.004 rows=1 loops=5)

  • Index Cond: (id = cmd.military_rank_id)
30. 0.070 0.880 ↑ 1.1 30 5

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

  • Group Key: lc.cid, lc.lease_id
31. 0.052 0.810 ↓ 1.3 43 5

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

  • Sort Key: lc.lease_id
  • Sort Method: quicksort Memory: 31kB
32. 0.018 0.758 ↓ 3.9 134 1

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

33. 0.005 0.005 ↑ 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.005 rows=1 loops=1)

34. 0.735 0.735 ↓ 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.020..0.735 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