explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yrOL

Settings
# exclusive inclusive rows x rows loops node
1. 204.038 69,858.146 ↓ 3,325.0 3,325 1

WindowAgg (cost=549,647.40..549,648.73 rows=1 width=572) (actual time=69,657.215..69,858.146 rows=3,325 loops=1)

2. 9.781 69,654.108 ↓ 3,325.0 3,325 1

Sort (cost=549,647.40..549,647.40 rows=1 width=920) (actual time=69,652.237..69,654.108 rows=3,325 loops=1)

  • Sort Key: lc.customer_id, lc.lease_id
  • Sort Method: quicksort Memory: 2,541kB
3. 27.330 69,644.327 ↓ 3,325.0 3,325 1

Nested Loop Semi Join (cost=525,620.55..549,647.39 rows=1 width=920) (actual time=3,086.081..69,644.327 rows=3,325 loops=1)

  • Join Filter: (l.id = cri.lease_id)
4. 24,435.294 69,460.133 ↓ 13,072.0 13,072 1

Nested Loop Left Join (cost=525,620.13..549,646.71 rows=1 width=932) (actual time=3,059.563..69,460.133 rows=13,072 loops=1)

  • Join Filter: (lc.lease_id = lc_1.lease_id)
  • Rows Removed by Join Filter: 57,644,920
5. 1,132.781 8,410.167 ↓ 13,072.0 13,072 1

Nested Loop (cost=490,341.08..513,474.39 rows=1 width=892) (actual time=2,595.845..8,410.167 rows=13,072 loops=1)

  • Join Filter: ((li_term.property_id = pcs.property_id) AND (lt.lease_term_structure_id = pcs.lease_term_structure_id))
  • Rows Removed by Join Filter: 2,614,400
6. 29.374 3,473.434 ↓ 13,072.0 13,072 1

Hash Join (cost=490,334.23..513,454.43 rows=1 width=908) (actual time=2,595.499..3,473.434 rows=13,072 loops=1)

  • Hash Cond: ((lt.id = lsw.lease_term_id) AND (li_term.property_id = lsw.property_id) AND (li_term.lease_start_window_id = lsw.id))
7. 38.180 3,443.772 ↑ 3.4 29,355 1

Hash Join (cost=490,245.97..512,580.46 rows=99,773 width=908) (actual time=2,595.193..3,443.772 rows=29,355 loops=1)

  • Hash Cond: (li_term.lease_term_id = lt.id)
8. 33.902 3,404.548 ↑ 2.7 29,355 1

Hash Left Join (cost=490,199.71..512,327.21 rows=78,264 width=750) (actual time=2,594.133..3,404.548 rows=29,355 loops=1)

  • Hash Cond: ((aa.cid = o.cid) AND (aa.occupation_id = o.id))
9. 246.030 3,370.548 ↑ 2.7 29,355 1

Hash Right Join (cost=490,195.31..511,911.81 rows=78,264 width=739) (actual time=2,594.017..3,370.548 rows=29,355 loops=1)

  • Hash Cond: ((aa.cid = c.cid) AND (aa.customer_id = c.id))
10. 531.140 531.140 ↑ 1.0 332,743 1

Seq Scan on applicants aa (cost=0.00..18,438.29 rows=332,743 width=12) (actual time=0.020..531.140 rows=332,743 loops=1)

  • Filter: (cid = 15,027)
11. 38.060 2,593.378 ↑ 2.7 29,343 1

Hash (cost=489,021.44..489,021.44 rows=78,258 width=735) (actual time=2,593.377..2,593.378 rows=29,343 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 11,840kB
12. 44.585 2,555.318 ↑ 2.7 29,343 1

Hash Join (cost=43,125.54..489,021.44 rows=78,258 width=735) (actual time=1,258.782..2,555.318 rows=29,343 loops=1)

  • Hash Cond: (a.lease_interval_id = li_term.id)
13. 33.766 2,009.293 ↑ 2.7 29,343 1

Hash Left Join (cost=25,916.04..471,606.51 rows=78,258 width=723) (actual time=755.146..2,009.293 rows=29,343 loops=1)

  • Hash Cond: ((c.cid = cpn.cid) AND (c.id = cpn.customer_id))
14. 32.399 1,970.786 ↑ 2.7 29,343 1

Hash Left Join (cost=25,519.89..470,427.69 rows=78,258 width=706) (actual time=750.389..1,970.786 rows=29,343 loops=1)

  • Hash Cond: (c.marital_status_type_id = mst.id)
15. 31.678 1,938.373 ↑ 2.7 29,343 1

Hash Left Join (cost=25,518.78..470,211.43 rows=78,258 width=702) (actual time=750.357..1,938.373 rows=29,343 loops=1)

  • Hash Cond: ((cdet.citizenship_country_code)::text = (cou.code)::text)
16. 57.034 1,906.356 ↑ 2.7 29,343 1

Hash Left Join (cost=25,504.18..469,991.40 rows=78,258 width=493) (actual time=749.937..1,906.356 rows=29,343 loops=1)

  • Hash Cond: ((c.cid = cdet.cid) AND (c.id = cdet.customer_id))
17. 37.636 1,461.453 ↑ 2.7 29,343 1

Hash Left Join (cost=12,846.49..455,964.20 rows=78,258 width=490) (actual time=346.503..1,461.453 rows=29,343 loops=1)

  • Hash Cond: ((c.cid = ci.cid) AND (c.id = ci.customer_id))
18. 68.754 1,307.664 ↑ 2.9 26,743 1

Nested Loop Left Join (cost=7,221.38..446,802.36 rows=78,258 width=481) (actual time=229.783..1,307.664 rows=26,743 loops=1)

19. 72.777 1,078.632 ↑ 1.7 26,713 1

Nested Loop Left Join (cost=7,220.00..202,333.98 rows=45,050 width=484) (actual time=229.741..1,078.632 rows=26,713 loops=1)

20. 41.005 845.577 ↓ 1.0 26,713 1

Nested Loop (cost=7,218.73..61,754.01 rows=26,403 width=305) (actual time=229.698..845.577 rows=26,713 loops=1)

  • Join Filter: (l.id = a.lease_id)
21. 19.472 698.812 ↑ 1.3 13,220 1

Hash Left Join (cost=7,218.31..44,007.00 rows=17,124 width=289) (actual time=229.661..698.812 rows=13,220 loops=1)

  • Hash Cond: ((c.cid = civ.cid) AND (c.id = civ.customer_id))
22. 32.155 450.128 ↑ 1.3 13,220 1

Nested Loop (cost=2.41..36,482.91 rows=17,124 width=170) (actual time=0.306..450.128 rows=13,220 loops=1)

23. 25.388 351.873 ↑ 1.3 13,220 1

Nested Loop (cost=1.98..23,717.70 rows=17,124 width=93) (actual time=0.271..351.873 rows=13,220 loops=1)

24. 13.521 264.185 ↑ 1.2 8,900 1

Nested Loop Left Join (cost=1.56..16,158.53 rows=11,017 width=81) (actual time=0.230..264.185 rows=8,900 loops=1)

25. 90.780 223.964 ↑ 1.2 8,900 1

Hash Join (cost=1.27..11,560.10 rows=11,017 width=77) (actual time=0.220..223.964 rows=8,900 loops=1)

  • Hash Cond: (l.property_id = load_prop.property_id)
26. 133.160 133.160 ↓ 1.4 187,436 1

Seq Scan on cached_leases l (cost=0.00..10,963.24 rows=129,446 width=65) (actual time=0.018..133.160 rows=187,436 loops=1)

  • Filter: ((cid = 15,027) AND (lease_status_type_id = ANY ('{1,2,3,4,5,6}'::integer[])))
27. 0.012 0.024 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=16) (actual time=0.023..0.024 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
28. 0.012 0.012 ↑ 1.0 12 1

Seq Scan on load_prop (cost=0.00..1.12 rows=12 width=16) (actual time=0.006..0.012 rows=12 loops=1)

29. 26.700 26.700 ↑ 1.0 1 8,900

Index Scan using idx_unit_spaces on unit_spaces us (cost=0.29..0.41 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=8,900)

  • Index Cond: (id = l.unit_space_id)
  • Filter: ((cid = 15,027) AND (cid = l.cid))
30. 62.300 62.300 ↑ 2.0 1 8,900

Index Scan using idx_lease_customers_lease_id on lease_customers lc (cost=0.42..0.67 rows=2 width=12) (actual time=0.006..0.007 rows=1 loops=8,900)

  • Index Cond: (lease_id = l.id)
  • Filter: (cid = 15,027)
31. 66.100 66.100 ↑ 1.0 1 13,220

Index Scan using idx_customers_id on customers c (cost=0.42..0.75 rows=1 width=81) (actual time=0.005..0.005 rows=1 loops=13,220)

  • Index Cond: (id = lc.customer_id)
  • Filter: (cid = 15,027)
32. 20.792 229.212 ↓ 2.0 32,414 1

Hash (cost=6,967.92..6,967.92 rows=16,532 width=127) (actual time=229.212..229.212 rows=32,414 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 5,280kB
33. 105.699 208.420 ↓ 2.0 32,414 1

Hash Join (cost=1.37..6,967.92 rows=16,532 width=127) (actual time=0.206..208.420 rows=32,414 loops=1)

  • Hash Cond: (civ.company_identification_type_id = cit.id)
34. 102.708 102.708 ↑ 1.0 190,122 1

Seq Scan on customer_identification_values civ (cost=0.00..6,375.52 rows=190,122 width=131) (actual time=0.016..102.708 rows=190,122 loops=1)

  • Filter: (cid = 15,027)
35. 0.003 0.013 ↑ 2.0 1 1

Hash (cost=1.34..1.34 rows=2 width=8) (actual time=0.013..0.013 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
36. 0.010 0.010 ↑ 2.0 1 1

Seq Scan on company_identification_types cit (cost=0.00..1.34 rows=2 width=8) (actual time=0.008..0.010 rows=1 loops=1)

  • Filter: ((cid = 15,027) AND ((system_code)::text = 'STUD ID'::text))
  • Rows Removed by Filter: 22
37. 105.760 105.760 ↑ 1.5 2 13,220

Index Scan using idx_applications_lease_id on applications a (cost=0.42..1.00 rows=3 width=20) (actual time=0.005..0.008 rows=2 loops=13,220)

  • Index Cond: (lease_id = lc.lease_id)
  • Filter: (cid = 15,027)
38. 53.426 160.278 ↑ 2.0 1 26,713

Bitmap Heap Scan on unit_types ut (cost=1.27..5.30 rows=2 width=195) (actual time=0.006..0.006 rows=1 loops=26,713)

  • Recheck Cond: ((id = us.unit_type_id) OR (id = a.unit_type_id))
  • Filter: (((cid = us.cid) AND (id = us.unit_type_id)) OR (id = a.unit_type_id))
  • Heap Blocks: exact=25,319
39. 53.426 106.852 ↓ 0.0 0 26,713

BitmapOr (cost=1.27..1.27 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=26,713)

40. 26.713 26.713 ↑ 1.0 1 26,713

Bitmap Index Scan on idx_unit_types_id (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=26,713)

  • Index Cond: (id = us.unit_type_id)
41. 26.713 26.713 ↑ 1.0 1 26,713

Bitmap Index Scan on idx_unit_types_id (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=26,713)

  • Index Cond: (id = a.unit_type_id)
42. 53.426 160.278 ↑ 2.0 1 26,713

Bitmap Heap Scan on property_floorplans pf (cost=1.37..5.41 rows=2 width=17) (actual time=0.006..0.006 rows=1 loops=26,713)

  • Recheck Cond: ((id = ut.property_floorplan_id) OR (id = a.property_floorplan_id))
  • Filter: (((cid = ut.cid) AND (id = ut.property_floorplan_id)) OR (id = a.property_floorplan_id))
  • Heap Blocks: exact=25,515
43. 53.426 106.852 ↓ 0.0 0 26,713

BitmapOr (cost=1.37..1.37 rows=2 width=0) (actual time=0.004..0.004 rows=0 loops=26,713)

44. 26.713 26.713 ↑ 1.0 1 26,713

Bitmap Index Scan on idx_property_floorplans (cost=0.00..0.31 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=26,713)

  • Index Cond: (id = ut.property_floorplan_id)
45. 26.713 26.713 ↑ 1.0 1 26,713

Bitmap Index Scan on idx_property_floorplans (cost=0.00..0.29 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=26,713)

  • Index Cond: (id = a.property_floorplan_id)
46. 51.146 116.153 ↑ 1.0 89,604 1

Hash (cost=4,281.05..4,281.05 rows=89,604 width=17) (actual time=116.153..116.153 rows=89,604 loops=1)

  • Buckets: 131,072 Batches: 1 Memory Usage: 5,283kB
47. 65.007 65.007 ↑ 1.0 89,604 1

Seq Scan on customer_incomes ci (cost=0.00..4,281.05 rows=89,604 width=17) (actual time=0.016..65.007 rows=89,604 loops=1)

  • Filter: (cid = 15,027)
48. 190.588 387.869 ↑ 1.0 332,716 1

Hash (cost=7,666.95..7,666.95 rows=332,716 width=11) (actual time=387.869..387.869 rows=332,716 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 17,093kB
49. 197.281 197.281 ↑ 1.0 332,716 1

Seq Scan on customer_details cdet (cost=0.00..7,666.95 rows=332,716 width=11) (actual time=0.014..197.281 rows=332,716 loops=1)

  • Filter: (cid = 15,027)
50. 0.173 0.339 ↑ 1.0 249 1

Hash (cost=11.49..11.49 rows=249 width=215) (actual time=0.339..0.339 rows=249 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 69kB
51. 0.166 0.166 ↑ 1.0 249 1

Seq Scan on countries cou (cost=0.00..11.49 rows=249 width=215) (actual time=0.014..0.166 rows=249 loops=1)

52. 0.006 0.014 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=12) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.008 0.008 ↑ 1.0 5 1

Seq Scan on marital_status_types mst (cost=0.00..1.05 rows=5 width=12) (actual time=0.005..0.008 rows=5 loops=1)

54. 0.029 4.741 ↑ 1.0 38 1

Hash (cost=395.58..395.58 rows=38 width=25) (actual time=4.741..4.741 rows=38 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
55. 4.712 4.712 ↑ 1.0 38 1

Seq Scan on customer_phone_numbers cpn (cost=0.00..395.58 rows=38 width=25) (actual time=0.022..4.712 rows=38 loops=1)

  • Filter: ((deleted_on IS NULL) AND is_primary AND (cid = 15,027))
  • Rows Removed by Filter: 15,368
56. 245.557 501.440 ↑ 1.0 415,820 1

Hash (cost=12,011.75..12,011.75 rows=415,820 width=20) (actual time=501.440..501.440 rows=415,820 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 24,481kB
57. 255.883 255.883 ↑ 1.0 415,820 1

Seq Scan on lease_intervals li_term (cost=0.00..12,011.75 rows=415,820 width=20) (actual time=0.011..255.883 rows=415,820 loops=1)

  • Filter: (cid = 15,027)
58. 0.035 0.098 ↑ 1.0 51 1

Hash (cost=3.64..3.64 rows=51 width=27) (actual time=0.097..0.098 rows=51 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
59. 0.063 0.063 ↑ 1.0 51 1

Seq Scan on occupations o (cost=0.00..3.64 rows=51 width=27) (actual time=0.017..0.063 rows=51 loops=1)

  • Filter: (cid = 15,027)
60. 0.482 1.044 ↑ 1.0 717 1

Hash (cost=37.30..37.30 rows=717 width=162) (actual time=1.044..1.044 rows=717 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 149kB
61. 0.562 0.562 ↑ 1.0 717 1

Seq Scan on lease_terms lt (cost=0.00..37.30 rows=717 width=162) (actual time=0.006..0.562 rows=717 loops=1)

  • Filter: (cid = 15,027)
  • Rows Removed by Filter: 107
62. 0.040 0.288 ↓ 1.2 58 1

Hash (cost=87.38..87.38 rows=50 width=24) (actual time=0.288..0.288 rows=58 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
63. 0.116 0.248 ↓ 1.2 58 1

Bitmap Heap Scan on lease_start_windows lsw (cost=48.69..87.38 rows=50 width=24) (actual time=0.143..0.248 rows=58 loops=1)

  • Recheck Cond: ((cid = 15,027) AND (id = ANY ('{3615,3554,3555,4866,3557,3556,4867,3559,3558,4868,3851,4827,4869,3852,4870,3679,3676,3680,3677,3678,3659,3660,3661,3662,4121,4118,3864,3855,3854,3853,3828,3785,3786,4618,4560,3868,3787,4865,4178,4720,4179,3985,3900,3901,3902,4181,4180,4108,4721,4105,4103,4104,4034,4035,4033,4324,4325,4862}'::integer[])))
  • Heap Blocks: exact=18
64. 0.132 0.132 ↓ 1.2 58 1

Bitmap Index Scan on pk_lease_start_windows (cost=0.00..48.67 rows=50 width=0) (actual time=0.132..0.132 rows=58 loops=1)

  • Index Cond: ((cid = 15,027) AND (id = ANY ('{3615,3554,3555,4866,3557,3556,4867,3559,3558,4868,3851,4827,4869,3852,4870,3679,3676,3680,3677,3678,3659,3660,3661,3662,4121,4118,3864,3855,3854,3853,3828,3785,3786,4618,4560,3868,3787,4865,4178,4720,4179,3985,3900,3901,3902,4181,4180,4108,4721,4105,4103,4104,4034,4035,4033,4324,4325,4862}'::integer[])))
65. 2,483.538 3,803.952 ↑ 1.3 201 13,072

Hash Join (cost=6.85..15.91 rows=270 width=16) (actual time=0.003..0.291 rows=201 loops=13,072)

  • Hash Cond: (pcs.lease_term_structure_id = lts.id)
66. 1,320.272 1,320.272 ↑ 1.0 201 13,072

Seq Scan on property_charge_settings pcs (cost=0.00..8.51 rows=201 width=12) (actual time=0.002..0.101 rows=201 loops=13,072)

  • Filter: (cid = 15,027)
67. 0.060 0.142 ↑ 1.0 113 1

Hash (cost=5.44..5.44 rows=113 width=8) (actual time=0.142..0.142 rows=113 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
68. 0.082 0.082 ↑ 1.0 113 1

Seq Scan on lease_term_structures lts (cost=0.00..5.44 rows=113 width=8) (actual time=0.012..0.082 rows=113 loops=1)

  • Filter: (cid = 15,027)
  • Rows Removed by Filter: 2
69. 36,168.343 36,614.672 ↑ 5.8 4,411 13,072

HashAggregate (cost=35,279.05..35,598.07 rows=25,522 width=44) (actual time=0.036..2.801 rows=4,411 loops=13,072)

  • Group Key: lc_1.lease_id
70. 55.043 446.329 ↑ 1.7 15,069 1

Hash Right Join (cost=29,715.59..34,513.39 rows=25,522 width=17) (actual time=347.069..446.329 rows=15,069 loops=1)

  • Hash Cond: ((ci_1.cid = c_1.cid) AND (ci_1.customer_id = c_1.id))
71. 44.683 44.683 ↑ 1.0 89,604 1

Seq Scan on customer_incomes ci_1 (cost=0.00..4,057.04 rows=89,604 width=17) (actual time=0.007..44.683 rows=89,604 loops=1)

72. 8.300 346.603 ↑ 1.9 13,220 1

Hash (cost=29,332.76..29,332.76 rows=25,522 width=16) (actual time=346.603..346.603 rows=13,220 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 876kB
73. 20.937 338.303 ↑ 1.9 13,220 1

Nested Loop (cost=1.69..29,332.76 rows=25,522 width=16) (actual time=0.073..338.303 rows=13,220 loops=1)

74. 139.395 277.706 ↑ 1.9 13,220 1

Hash Join (cost=1.27..10,051.98 rows=25,522 width=12) (actual time=0.059..277.706 rows=13,220 loops=1)

  • Hash Cond: (lc_1.property_id = load_prop_1.property_id)
75. 138.291 138.291 ↑ 1.0 291,331 1

Seq Scan on lease_customers lc_1 (cost=0.00..8,702.81 rows=291,381 width=16) (actual time=0.005..138.291 rows=291,331 loops=1)

76. 0.009 0.020 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=4) (actual time=0.020..0.020 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
77. 0.011 0.011 ↑ 1.0 12 1

Seq Scan on load_prop load_prop_1 (cost=0.00..1.12 rows=12 width=4) (actual time=0.005..0.011 rows=12 loops=1)

78. 39.660 39.660 ↑ 1.0 1 13,220

Index Scan using idx_customers_id on customers c_1 (cost=0.42..0.75 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=13,220)

  • Index Cond: (id = lc_1.customer_id)
  • Filter: (lc_1.cid = cid)
79. 156.864 156.864 ↓ 0.0 0 13,072

Index Only Scan using uk_customer_roommate_interests_cid_lease_id_customer_id_roommat on customer_roommate_interests cri (cost=0.42..0.67 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=13,072)

  • Index Cond: ((cid = 15,027) AND (lease_id = a.lease_id) AND (customer_id = c.id))
  • Heap Fetches: 3,325
Planning time : 66.356 ms