explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XyS8 : x

Settings
# exclusive inclusive rows x rows loops node
1. 16,638.591 16,638.591 ↑ 1.0 1 1

CTE Scan on cteproperty (cost=89.37..89.39 rows=1 width=128) (actual time=16,638.591..16,638.591 rows=1 loops=1)

2.          

CTE ctepropertydata

3. 0.024 0.180 ↓ 5.0 5 1

Nested Loop (cost=0.00..37.79 rows=1 width=24) (actual time=0.137..0.180 rows=5 loops=1)

  • Join Filter: (p.propcode = pp.propcode)
  • Rows Removed by Join Filter: 211
4. 0.048 0.048 ↑ 1.0 6 1

Seq Scan on prop p (cost=0.00..10.88 rows=6 width=20) (actual time=0.014..0.048 rows=6 loops=1)

  • Filter: (propcode = ANY ('{163634,42889,239591,4189,235396,248546}'::integer[]))
  • Rows Removed by Filter: 121
5. 0.015 0.108 ↓ 12.0 36 6

Materialize (cost=0.00..26.64 rows=3 width=8) (actual time=0.007..0.018 rows=36 loops=6)

6. 0.093 0.093 ↓ 12.0 36 1

Seq Scan on propertyparameter pp (cost=0.00..26.62 rows=3 width=8) (actual time=0.037..0.093 rows=36 loops=1)

  • Filter: (siteactive AND forecastproperty AND (propertytype <> 9))
  • Rows Removed by Filter: 91
7.          

CTE ctemonthstouse

8. 0.012 0.012 ↓ 5.0 5 1

CTE Scan on ctepropertydata p_1 (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.012 rows=5 loops=1)

9.          

CTE ctepropertyandfloorplandata

10. 0.104 0.593 ↓ 17.0 17 1

Nested Loop (cost=5.14..17.44 rows=1 width=146) (actual time=0.189..0.593 rows=17 loops=1)

  • -> Index Scan using floorplanparameter_idx on floorplanparameter fpp (cost=0.28..1.97 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=17
11. 0.095 0.489 ↓ 17.0 17 1

Nested Loop (cost=4.86..15.45 rows=1 width=173) (actual time=0.180..0.489 rows=17 loops=1)

  • -> Index Scan using floorplanparameter_idx on floorplanparameter fpp2 (cost=0.28..1.97 rows=1 width=15) (actual time=0.004..0.004 rows=1 l
  • Index Cond: ((propcode = fp.propcode) AND ((propfp)::text = (fp.propfp)::text) AND (ratetype = 1))
  • Filter: forecastfloorplan
12. 0.095 0.394 ↓ 17.0 17 1

Nested Loop (cost=4.58..13.47 rows=1 width=158) (actual time=0.172..0.394 rows=17 loops=1)

  • -> Index Scan using floorplanrenewalparameter_pkey on floorplanrenewalparameter fprp (cost=0.28..0.52 rows=1 width=16) (actual time=
  • Index Cond: ((propcode = fp.propcode) AND ((propfp)::text = (fp.propfp)::text) AND (ratetype = 2))
13. 0.032 0.299 ↓ 4.2 17 1

Nested Loop (cost=4.31..11.34 rows=4 width=142) (actual time=0.162..0.299 rows=17 loops=1)

  • Index Cond: ((propcode = fp.propcode) AND (propfp = (fp.propfp)::text))
14. 0.187 0.187 ↓ 5.0 5 1

CTE Scan on ctepropertydata p_2 (cost=0.00..0.02 rows=1 width=126) (actual time=0.138..0.187 rows=5 loops=1)

15. 0.040 0.080 ↑ 1.3 3 5

Bitmap Heap Scan on fp (cost=4.31..11.28 rows=4 width=16) (actual time=0.012..0.016 rows=3 loops=5)

  • Recheck Cond: (propcode = p_2.property_code)
  • Filter: ((NOT deleted) AND currec)
  • Heap Blocks: exact=11
16. 0.040 0.040 ↑ 1.3 3 5

Bitmap Index Scan on fp_pkey (cost=0.00..4.31 rows=4 width=0) (actual time=0.008..0.008 rows=3 loops=5)

  • Index Cond: (propcode = p_2.property_code)
17.          

CTE ctepropertyandfloorplanandunitdata

18. 0.150 2.304 ↓ 1,101.0 1,101 1

Nested Loop (cost=0.41..8.47 rows=1 width=212) (actual time=0.006..2.304 rows=1,101 loops=1)

19. 0.420 0.420 ↓ 17.0 17 1

CTE Scan on ctepropertyandfloorplandata pfp (cost=0.00..0.02 rows=1 width=204) (actual time=0.000..0.420 rows=17 loops=1)

20. 1.734 1.734 ↓ 65.0 65 17

Index Scan using fpunitinv_pkey on fpunitinv ui (cost=0.41..8.44 rows=1 width=20) (actual time=0.007..0.102 rows=65 loops=17)

  • Index Cond: ((propcode = pfp.property_code) AND ((propfp)::text = (pfp.floor_plan)::text))
  • Filter: (status <> 'W'::bpchar)
  • Rows Removed by Filter: 2
21.          

CTE cteleaselistall

22. 2.488 28.496 ↓ 6,510.0 6,510 1

Nested Loop (cost=0.42..8.49 rows=1 width=326) (actual time=0.032..28.496 rows=6,510 loops=1)

23. 2.887 2.887 ↓ 1,101.0 1,101 1

CTE Scan on ctepropertyandfloorplanandunitdata pfpu (cost=0.00..0.02 rows=1 width=302) (actual time=0.007..2.887 rows=1,101 loops=1)

24. 23.121 23.121 ↓ 6.0 6 1,101

Index Scan using fpulind4 on fpunitlease ul (cost=0.42..8.45 rows=1 width=61) (actual time=0.006..0.021 rows=6 loops=1,101)

  • Index Cond: ((propcode = pfpu.property_code) AND ((propfp)::text = (pfpu.floor_plan)::text) AND ((propbldg)::text = (pfpu.building)::text) A
  • Filter: ((NOT deleted) AND (canceldate IS NULL))
  • Rows Removed by Filter: 1
25.          

CTE ctepropertyandfloorplandatawithmonth

26. 0.124 0.434 ↓ 85.0 85 1

Nested Loop (cost=0.00..0.05 rows=1 width=212) (actual time=0.192..0.434 rows=85 loops=1)

27. 0.208 0.208 ↓ 17.0 17 1

CTE Scan on ctepropertyandfloorplandata pfp_1 (cost=0.00..0.02 rows=1 width=204) (actual time=0.190..0.208 rows=17 loops=1)

28. 0.102 0.102 ↓ 5.0 5 17

CTE Scan on ctemonthstouse (cost=0.00..0.02 rows=1 width=8) (actual time=0.000..0.006 rows=5 loops=17)

29.          

CTE ctepropertyandfloorplanandunitdatawithmonth

30. 2.345 170.783 ↓ 5,505.0 5,505 1

Nested Loop (cost=0.41..8.47 rows=1 width=86) (actual time=0.016..170.783 rows=5,505 loops=1)

31. 0.393 0.393 ↓ 85.0 85 1

CTE Scan on ctepropertyandfloorplandatawithmonth pfpmnth (cost=0.00..0.02 rows=1 width=78) (actual time=0.000..0.393 rows=85 loops=1)

32. 168.045 168.045 ↓ 65.0 65 85

Index Scan using fpunitinv_pkey on fpunitinv ui_1 (cost=0.41..8.44 rows=1 width=20) (actual time=0.565..1.977 rows=65 loops=85)

  • Index Cond: ((propcode = pfpmnth.property_code) AND ((propfp)::text = (pfpmnth.floor_plan)::text))
  • Filter: (status <> 'W'::bpchar)
  • Rows Removed by Filter: 2
33.          

CTE cteleaselist

34. 211.764 16,634.000 ↓ 395.0 395 1

Nested Loop Left Join (cost=0.29..8.43 rows=1 width=110) (actual time=81.599..16,634.000 rows=395 loops=1)

  • Join Filter: ((ulr.leasestart > ul_1.leaseout) AND (ulr.property_code = ul_1.property_code) AND ((ulr.floor_plan)::text = (ul_1.floor_plan)::text)
  • Rows Removed by Join Filter: 1,285,101
35. 7.042 15,612.881 ↓ 395.0 395 1

Nested Loop Left Join (cost=0.29..8.38 rows=1 width=392) (actual time=81.536..15,612.881 rows=395 loops=1)

  • -> Index Scan using renewalprobabilitysimple_pkey on renewalprobabilitysimple rps (cost=0.29..8.31 rows=1 width=16) (actual time=0.013..0.
36. 9,632.914 15,605.839 ↓ 395.0 395 1

Nested Loop (cost=0.00..0.07 rows=1 width=388) (actual time=81.466..15,605.839 rows=395 loops=1)

  • Join Filter: ((ul_1.leaseout >= pfpumnth.monthstart_date) AND (ul_1.leaseout <= pfpumnth.monthend_date) AND (pfpumnth.property_code =
  • Rows Removed by Join Filter: 35,837,155
  • -> CTE Scan on ctepropertyandfloorplanandunitdatawithmonth pfpumnth (cost=0.00..0.02 rows=1 width=314) (actual time=0.016..176.689 r
  • Index Cond: ((propcode = ul_1.property_code) AND (fpulkey = ul_1.fpulkey))
37. 5,972.925 5,972.925 ↓ 6,510.0 6,510 5,505

CTE Scan on cteleaselistall ul_1 (cost=0.00..0.02 rows=1 width=310) (actual time=0.000..1.085 rows=6,510 loops=5,505)

38. 809.355 809.355 ↓ 3,254.0 3,254 395

CTE Scan on cteleaselistall ulr (cost=0.00..0.02 rows=1 width=306) (actual time=0.001..2.049 rows=3,254 loops=395)

  • Filter: (numrenewals > 0)
  • Rows Removed by Filter: 3,256
39.          

CTE cteleasecounts

40. 3.005 16,637.712 ↓ 31.0 31 1

HashAggregate (cost=0.04..0.05 rows=1 width=110) (actual time=16,637.702..16,637.712 rows=31 loops=1)

  • Group Key: cteleaselist.property_code, cteleaselist.floor_plan, cteleaselist.monthstart_date, cteleaselist.monthend_date
41. 16,634.707 16,634.707 ↓ 395.0 395 1

CTE Scan on cteleaselist (cost=0.00..0.02 rows=1 width=90) (actual time=81.601..16,634.707 rows=395 loops=1)

42.          

CTE ctefinalstats

43. 16,637.757 16,637.757 ↓ 31.0 31 1

CTE Scan on cteleasecounts (cost=0.00..0.05 rows=1 width=134) (actual time=16,637.717..16,637.757 rows=31 loops=1)

44.          

CTE ctefloorplan

45. 0.309 16,638.512 ↓ 85.0 85 1

Nested Loop Left Join (cost=0.00..0.06 rows=1 width=60) (actual time=16,637.948..16,638.512 rows=85 loops=1)

  • Join Filter: ((fs.property_code = pfpmnth_1.property_code) AND ((fs.floor_plan)::text = (pfpmnth_1.floor_plan)::text) AND (fs.monthstart_date = pf
  • Rows Removed by Join Filter: 2,553
46. 0.218 0.218 ↓ 85.0 85 1

CTE Scan on ctepropertyandfloorplandatawithmonth pfpmnth_1 (cost=0.00..0.02 rows=1 width=70) (actual time=0.193..0.218 rows=85 loops=1)

47. 16,637.985 16,637.985 ↓ 31.0 31 85

CTE Scan on ctefinalstats fs (cost=0.00..0.02 rows=1 width=114) (actual time=195.738..195.741 rows=31 loops=85)

48.          

CTE cteproperty

49. 0.042 16,638.590 ↑ 1.0 1 1

Aggregate (cost=0.04..0.05 rows=1 width=128) (actual time=16,638.590..16,638.590 rows=1 loops=1)

50. 16,638.548 16,638.548 ↓ 85.0 85 1

CTE Scan on ctefloorplan (cost=0.00..0.02 rows=1 width=32) (actual time=16,637.950..16,638.548 rows=85 loops=1)