explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cOct

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 10,812.977 ↑ 1.0 1 1

Limit (cost=2.39..349.49 rows=1 width=955) (actual time=10,812.977..10,812.977 rows=1 loops=1)

2.          

Initplan (for Limit)

3. 0.001 0.021 ↑ 1.0 1 1

Limit (cost=1.53..1.54 rows=1 width=9) (actual time=0.021..0.021 rows=1 loops=1)

4. 0.006 0.020 ↑ 1.0 1 1

Sort (cost=1.53..1.54 rows=1 width=9) (actual time=0.020..0.020 rows=1 loops=1)

  • Sort Key: property_subtypes.active DESC
  • Sort Method: quicksort Memory: 25kB
5. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on property_subtypes (cost=0.00..1.52 rows=1 width=9) (actual time=0.010..0.014 rows=1 loops=1)

  • Filter: (((code)::text ~~ 'article'::text) AND (property_typesid = 1))
  • Rows Removed by Filter: 34
6. 21.274 10,812.977 ↑ 3,063,407,907,291.0 1 1

Nested Loop Left Join (cost=0.85..1,063,298,274,286,816.75 rows=3,063,407,907,291 width=955) (actual time=10,812.977..10,812.977 rows=1 loops=1)

  • Join Filter: (ps2.propertyid = p.id)
  • Rows Removed by Join Filter: 316849
7. 21.136 10,493.186 ↑ 3,063,407,907,291.0 1 1

Nested Loop Left Join (cost=0.85..605,946,790,693,416.62 rows=3,063,407,907,291 width=955) (actual time=10,493.186..10,493.186 rows=1 loops=1)

  • Join Filter: (ps1.propertyid = p.id)
  • Rows Removed by Join Filter: 316849
8. 0.003 10,118.603 ↑ 3,063,407,907,291.0 1 1

Nested Loop Left Join (cost=0.85..150,341,449,607,172.34 rows=3,063,407,907,291 width=951) (actual time=10,118.603..10,118.603 rows=1 loops=1)

  • Join Filter: (cplv_quant.picklistvalue = pt.currentquantitytype)
  • Rows Removed by Join Filter: 12
9. 10.139 10,117.736 ↑ 3,063,407,907,291.0 1 1

Nested Loop Left Join (cost=0.85..149,146,720,523,092.31 rows=3,063,407,907,291 width=983) (actual time=10,117.736..10,117.736 rows=1 loops=1)

  • Join Filter: (pt.propertyid = p.id)
  • Rows Removed by Join Filter: 149824
10. 0.003 9,751.479 ↑ 3,063,407,907,291.0 1 1

Nested Loop Left Join (cost=0.85..9,133,662,065,863.18 rows=3,063,407,907,291 width=887) (actual time=9,751.479..9,751.479 rows=1 loops=1)

11. 70.479 9,751.475 ↑ 1,556,392,221,947.0 1 1

Nested Loop Left Join (cost=0.42..7,392,025,875,661.89 rows=1,556,392,221,947 width=887) (actual time=9,751.475..9,751.475 rows=1 loops=1)

  • Join Filter: (pr.id = pp.personid)
  • Rows Removed by Join Filter: 1101048
12. 0.004 8,802.633 ↑ 1,309,234,864.0 1 1

Nested Loop Left Join (cost=0.42..1,202,827,451,456.18 rows=1,309,234,864 width=883) (actual time=8,802.633..8,802.633 rows=1 loops=1)

13. 75.028 8,802.628 ↑ 1,309,234,864.0 1 1

Nested Loop Left Join (cost=0.00..1,202,217,318,611.50 rows=1,309,234,864 width=891) (actual time=8,802.628..8,802.628 rows=1 loops=1)

  • Join Filter: (l.id = p.acquisitionlocationid)
  • Rows Removed by Join Filter: 1241015
14. 0.005 7,837.085 ↑ 27,124,789.0 1 1

Nested Loop Left Join (cost=0.00..812,343,946,984.92 rows=27,124,789 width=887) (actual time=7,837.085..7,837.085 rows=1 loops=1)

  • Join Filter: (cplv3.picklistvalue = p.color3)
  • Rows Removed by Join Filter: 37
15. 0.002 7,836.368 ↑ 27,124,789.0 1 1

Nested Loop Left Join (cost=0.00..812,333,368,080.64 rows=27,124,789 width=857) (actual time=7,836.368..7,836.368 rows=1 loops=1)

  • Join Filter: (cplv2.picklistvalue = p.color2)
  • Rows Removed by Join Filter: 37
16. 0.006 7,835.644 ↑ 27,124,789.0 1 1

Nested Loop Left Join (cost=0.00..812,322,789,176.37 rows=27,124,789 width=829) (actual time=7,835.644..7,835.644 rows=1 loops=1)

  • Join Filter: (cplv1.picklistvalue = p.color1)
  • Rows Removed by Join Filter: 37
17. 46.276 7,834.733 ↑ 27,124,789.0 1 1

Nested Loop (cost=0.00..812,312,210,272.09 rows=27,124,789 width=800) (actual time=7,834.733..7,834.733 rows=1 loops=1)

  • Join Filter: (i.incident_zid = stg_cc.convertedid)
  • Rows Removed by Join Filter: 693719
18. 85.443 7,315.960 ↑ 27,124,789.0 1 1

Nested Loop (cost=0.00..1,306,558,980.64 rows=27,124,789 width=800) (actual time=7,315.960..7,315.960 rows=1 loops=1)

  • Join Filter: (isp.incidentid = i.id)
  • Rows Removed by Join Filter: 1235611
19. 3.716 6,111.822 ↑ 405,756.0 1 1

Nested Loop (cost=0.00..1,225,023,820.98 rows=405,756 width=792) (actual time=6,111.822..6,111.822 rows=1 loops=1)

  • Join Filter: (ispr.incidentsupplementid = isp.id)
  • Rows Removed by Join Filter: 33810
20. 1.415 5,886.050 ↑ 3,505.0 1 1

Nested Loop Left Join (cost=0.00..1,223,650,090.45 rows=3,505 width=792) (actual time=5,886.050..5,886.050 rows=1 loops=1)

  • Join Filter: (pp.propertyid = p.id)
  • Rows Removed by Join Filter: 19076
21. 591.029 5,849.648 ↑ 3,505.0 1 1

Nested Loop Left Join (cost=0.00..1,223,643,626.76 rows=3,505 width=788) (actual time=5,849.648..5,849.648 rows=1 loops=1)

  • Join Filter: (n.id = p.noteid)
  • Rows Removed by Join Filter: 8702012
22. 0.062 55.189 ↑ 3,505.0 1 1

Nested Loop Left Join (cost=0.00..16,996,069.13 rows=3,505 width=632) (actual time=55.189..55.189 rows=1 loops=1)

  • Join Filter: (p.recoveredofficerid = e.id)
  • Rows Removed by Join Filter: 1081
23. 0.595 54.184 ↑ 3,505.0 1 1

Nested Loop (cost=0.00..16,965,801.91 rows=3,505 width=616) (actual time=54.184..54.184 rows=1 loops=1)

  • Join Filter: (p.id = ispr.propertyid)
  • Rows Removed by Join Filter: 7642
24. 1.345 1.345 ↑ 318,528.0 1 1

Seq Scan on property p (cost=0.00..52,122.95 rows=318,528 width=612) (actual time=1.345..1.345 rows=1 loops=1)

  • Filter: (recordstatus = 0)
25. 0.963 52.244 ↓ 2.2 7,643 1

Materialize (cost=0.00..18,962.68 rows=3,536 width=8) (actual time=49.472..52.244 rows=7,643 loops=1)

26. 51.281 51.281 ↓ 2.2 7,643 1

Seq Scan on incidentsupplementproperty ispr (cost=0.00..18,945.00 rows=3,536 width=8) (actual time=49.470..51.281 rows=7,643 loops=1)

  • Filter: (z_rank = 1)
  • Rows Removed by Filter: 1
27. 0.157 0.943 ↓ 1.9 1,081 1

Materialize (cost=0.00..90.61 rows=574 width=24) (actual time=0.239..0.943 rows=1,081 loops=1)

28. 0.786 0.786 ↓ 1.9 1,081 1

Seq Scan on employee e (cost=0.00..87.74 rows=574 width=24) (actual time=0.237..0.786 rows=1,081 loops=1)

29. 2,549.592 5,203.430 ↑ 1.0 8,702,012 1

Materialize (cost=0.00..803,442.45 rows=8,730,430 width=172) (actual time=0.043..5,203.430 rows=8,702,012 loops=1)

30. 2,653.838 2,653.838 ↑ 1.0 8,702,012 1

Seq Scan on note n (cost=0.00..546,644.30 rows=8,730,430 width=172) (actual time=0.042..2,653.838 rows=8,702,012 loops=1)

31. 2.644 34.987 ↓ 3,815.2 19,076 1

Materialize (cost=0.00..6,200.82 rows=5 width=8) (actual time=14.387..34.987 rows=19,076 loops=1)

32. 32.343 32.343 ↓ 3,815.2 19,076 1

Seq Scan on propertyperson pp (cost=0.00..6,200.80 rows=5 width=8) (actual time=14.384..32.343 rows=19,076 loops=1)

  • Filter: ((persontype = 'Owner'::text) AND (z_rank = 1))
  • Rows Removed by Filter: 80220
33. 5.675 222.056 ↓ 1.5 33,811 1

Materialize (cost=0.00..156,519.44 rows=23,153 width=8) (actual time=103.766..222.056 rows=33,811 loops=1)

34. 216.381 216.381 ↓ 1.5 33,811 1

Seq Scan on incidentsupplement isp (cost=0.00..156,403.67 rows=23,153 width=8) (actual time=103.763..216.381 rows=33,811 loops=1)

  • Filter: (recordstatus = 0)
  • Rows Removed by Filter: 1
35. 198.733 1,118.695 ↓ 92.4 1,235,612 1

Materialize (cost=0.00..160,827.29 rows=13,370 width=16) (actual time=418.932..1,118.695 rows=1,235,612 loops=1)

36. 919.962 919.962 ↓ 92.4 1,235,612 1

Seq Scan on incident i (cost=0.00..160,760.44 rows=13,370 width=16) (actual time=418.928..919.962 rows=1,235,612 loops=1)

  • Filter: (recordstatus = 0)
  • Rows Removed by Filter: 297
37. 105.210 472.497 ↑ 2.2 693,720 1

Materialize (cost=0.00..104,219.40 rows=1,503,737 width=16) (actual time=0.008..472.497 rows=693,720 loops=1)

38. 367.287 367.287 ↑ 2.2 693,720 1

Seq Scan on stg_combined_cases stg_cc (cost=0.00..89,357.71 rows=1,503,737 width=16) (actual time=0.007..367.287 rows=693,720 loops=1)

  • Filter: (wtrun = 1)
39. 0.008 0.905 ↓ 1.4 37 1

Materialize (cost=0.00..236.63 rows=26 width=64) (actual time=0.592..0.905 rows=37 loops=1)

40. 0.897 0.897 ↓ 1.4 37 1

Seq Scan on configpicklistvalues cplv1 (cost=0.00..236.50 rows=26 width=64) (actual time=0.590..0.897 rows=37 loops=1)

  • Filter: (configpicklistid = 49)
  • Rows Removed by Filter: 5511
41. 0.007 0.722 ↓ 1.4 37 1

Materialize (cost=0.00..236.63 rows=26 width=64) (actual time=0.457..0.722 rows=37 loops=1)

42. 0.715 0.715 ↓ 1.4 37 1

Seq Scan on configpicklistvalues cplv2 (cost=0.00..236.50 rows=26 width=64) (actual time=0.455..0.715 rows=37 loops=1)

  • Filter: (configpicklistid = 49)
  • Rows Removed by Filter: 5511
43. 0.006 0.712 ↓ 1.4 37 1

Materialize (cost=0.00..236.63 rows=26 width=64) (actual time=0.438..0.712 rows=37 loops=1)

44. 0.706 0.706 ↓ 1.4 37 1

Seq Scan on configpicklistvalues cplv3 (cost=0.00..236.50 rows=26 width=64) (actual time=0.437..0.706 rows=37 loops=1)

  • Filter: (configpicklistid = 49)
  • Rows Removed by Filter: 5511
45. 182.695 890.515 ↓ 1.7 1,241,015 1

Materialize (cost=0.00..104,734.32 rows=722,888 width=12) (actual time=160.407..890.515 rows=1,241,015 loops=1)

46. 707.820 707.820 ↓ 1.7 1,241,015 1

Seq Scan on location l (cost=0.00..97,589.88 rows=722,888 width=12) (actual time=160.404..707.820 rows=1,241,015 loops=1)

47. 0.001 0.001 ↓ 0.0 0 1

Index Only Scan using pt_addresses_convertedid_wtrun_idx on pt_addresses pt_a (cost=0.42..0.46 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((convertedid = l.location_zid) AND (wtrun = 1))
  • Heap Fetches: 0
48. 165.349 878.363 ↓ 4.6 1,101,048 1

Materialize (cost=0.00..83,979.34 rows=237,756 width=12) (actual time=0.013..878.363 rows=1,101,048 loops=1)

49. 713.014 713.014 ↓ 4.6 1,101,048 1

Seq Scan on person pr (cost=0.00..81,629.56 rows=237,756 width=12) (actual time=0.011..713.014 rows=1,101,048 loops=1)

50. 0.001 0.001 ↓ 0.0 0 1

Index Scan using idx_pt_people_names_convertedid_wtrun on pt_people_names pt_pn (cost=0.43..1.10 rows=2 width=12) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((convertedid = pr.person_zid) AND (wtrun = 1))
51. 26.847 356.118 ↓ 49.2 149,824 1

Materialize (cost=0.00..54,501.61 rows=3,047 width=100) (actual time=64.564..356.118 rows=149,824 loops=1)

52. 329.271 329.271 ↓ 49.2 149,824 1

Seq Scan on propertytracking pt (cost=0.00..54,486.38 rows=3,047 width=100) (actual time=64.561..329.271 rows=149,824 loops=1)

  • Filter: (z_rank = 1)
  • Rows Removed by Filter: 397086
53. 0.003 0.864 ↑ 2.2 12 1

Materialize (cost=0.00..236.63 rows=26 width=32) (actual time=0.860..0.864 rows=12 loops=1)

54. 0.861 0.861 ↑ 2.2 12 1

Seq Scan on configpicklistvalues cplv_quant (cost=0.00..236.50 rows=26 width=32) (actual time=0.859..0.861 rows=12 loops=1)

  • Filter: (configpicklistid = 50)
  • Rows Removed by Filter: 5536
55. 43.971 353.447 ↓ 32.0 316,849 1

Materialize (cost=0.00..74,415.07 rows=9,915 width=8) (actual time=124.001..353.447 rows=316,849 loops=1)

56. 309.476 309.476 ↓ 32.0 316,849 1

Seq Scan on propertystatus ps1 (cost=0.00..74,365.50 rows=9,915 width=8) (actual time=123.998..309.476 rows=316,849 loops=1)

  • Filter: (z_rank_cat = 1)
  • Rows Removed by Filter: 276659
57. 45.010 298.517 ↓ 32.0 316,849 1

Materialize (cost=0.00..74,415.07 rows=9,915 width=8) (actual time=72.714..298.517 rows=316,849 loops=1)

58. 253.507 253.507 ↓ 32.0 316,849 1

Seq Scan on propertystatus ps2 (cost=0.00..74,365.50 rows=9,915 width=8) (actual time=72.712..253.507 rows=316,849 loops=1)

  • Filter: (z_rank_status = 1)
  • Rows Removed by Filter: 276659
Planning time : 50.482 ms
Execution time : 11,052.094 ms