explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oK1Y

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=1,529,630,912,919.20..1,529,630,913,094.26 rows=70,021 width=76) (actual rows= loops=)

  • Sort Key: (cardinality(tree.rs)) DESC, tree.r_size DESC
2.          

CTE axis

3. 0.000 0.000 ↓ 0.0

CTE Scan on tree (cost=0.00..1,575.47 rows=70,021 width=76) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=4) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Unique (cost=3,002,886.03..3,011,638.66 rows=70,021 width=72) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Sort (cost=3,681,281.55..3,723,719.55 rows=16,975,200 width=32) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Recursive Union (cost=1,855.89..634,436,834.76 rows=700,210 width=72) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Recursive Union (cost=112.86..38,976,540.26 rows=43,013 width=112) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Recursive Union (cost=0.02..2,999,343.79 rows=2,641 width=68) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.01..6,257,445.32 rows=16,975,200 width=84) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2,673,594.00 rows=84,876,000 width=44) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Limit (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,528,938,823,080.70..1,528,938,823,480.70 rows=40,000 width=64) (actual rows= loops=)

  • Sort Key: ((n1.*)::text)
  • Join Filter: ((face.face <> tilt.tilt) AND ((CASE WHEN (tilt.tilt = 'x'::text) THEN x.v WHEN (tilt.tilt = 'y'::text) THEN y.v WHEN (tilt.tilt = 'z'::text) THEN z.v ELSE NULL::integer END + tiltdirection.tiltdirection) >= 1) AND ((CASE WHEN (tilt.tilt = 'x'::text) THEN x.v WHEN (tilt.tilt = 'y'::text) THEN y.v WHEN (tilt.tilt = 'z'::text) THEN z.v ELSE NULL::integer END + tiltdirection.tiltdirection) <= 5))
  • Group Key: spatialid.id, fil.id
14. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,897,449.20..3,897,556.70 rows=4,300 width=112) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Values Scan on "*VALUES*_1" (cost=0.00..0.06 rows=5 width=12) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Unique (cost=1,855.89..1,858.04 rows=210 width=72) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Unique (cost=112.86..113.02 rows=13 width=112) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Sort (cost=3,002,886.03..3,004,636.56 rows=700,210 width=72) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=906.26..63,442,097.25 rows=70,000 width=72) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.01..84,419.32 rows=15,432 width=48) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..239,258.60 rows=264 width=68) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Merge Join (cost=333,439,541.46..1,146,788,061,222.34 rows=76,430,152,371,672 width=64) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Materialize (cost=0.01..251.00 rows=10,000 width=36) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

CTE Scan on spatialid spatialid_1 (cost=0.00..339,504.00 rows=16,975,200 width=32) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

CTE Scan on spatial n1_1 (cost=0.00..339,504.00 rows=16,975,200 width=192) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

CTE Scan on spatial n1 (cost=0.00..424,380.00 rows=16,975,200 width=32) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

CTE Scan on firstmove firstmove_1 (cost=0.02..606,704.99 rows=1 width=68) (actual rows= loops=)

  • Sort Key: bloom25_1.iter, ((SubPlan 17)), bloom25_1.remainder, bloom25_1.r_size
  • Merge Cond: (spatialid.id = spatial2.id)
  • Join Filter: (((CASE WHEN (face.face = 'x'::text) THEN x.v WHEN (face.face = 'y'::text) THEN y.v WHEN (face.face = 'z'::text) THEN z.v ELSE NULL::integer END - facedirection.facedirection) >= 1) AND ((CASE WHEN (face.face = 'x'::text) THEN x.v WHEN (face.face = 'y'::text) THEN y.v WHEN (face.face = 'z'::text) THEN z.v ELSE NULL::integer END - facedirection.facedirection) <= 5) AND ((CASE WHEN (face.face = 'x'::text) THEN x.v WHEN (face.face = 'y'::text) THEN y.v WHEN (face.face = 'z'::text) THEN z.v ELSE NULL::integer END + (2 * facedirection.facedirection)) >= 1) AND ((CASE WHEN (face.face = 'x'::text) THEN x.v WHEN (face.face = 'y'::text) THEN y.v WHEN (face.face = 'z'::text) THEN z.v ELSE NULL::integer END + (2 * facedirection.facedirection)) <= 5))
28.          

Initplan (forCTE Scan)

29. 0.000 0.000 ↓ 0.0

WorkTable Scan on seed4 (cost=0.00..0.22 rows=3 width=68) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Sort (cost=3,897,449.20..3,897,459.95 rows=4,300 width=104) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=3,596,405.55..3,638,843.55 rows=16,975,200 width=32) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Sort (cost=1,855.89..1,856.42 rows=215 width=72) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=112.86..112.89 rows=13 width=112) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Result (cost=906.26..906.27 rows=1 width=32) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.01..201.00 rows=10,000 width=36) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..32.96 rows=2,500 width=12) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..1,447.25 rows=70,000 width=104) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Materialize (cost=329,843,135.91..334,345,595.52 rows=900,491,922 width=64) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..12.60 rows=500 width=36) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Function Scan on unnest r (cost=0.00..1.25 rows=88 width=32) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

CTE Scan on bloom25 bloom25_1 (cost=0.00..2,877,462.65 rows=700,210 width=72) (actual rows= loops=)

  • Sort Key: spatialid.id
  • Sort Key: (cardinality(($24))) DESC
  • Sort Key: ((SubPlan 14)), prune20_1.remainder, (cardinality(prune20_1.remainder))
  • Sort Key: ((SubPlan 11)), seed4_1.remainder, (cardinality(seed4_1.remainder)), (seed4_1.stack[1])
  • One-Time Filter: (bloom25.r_size > (24 - bloom25.iter))
  • Filter: (r !~ '^(?:.*(?:3|4|5).*)$'::text)
  • Filter: (iter < 4)
  • Hash Cond: (remainder.id = filter.x)
  • Hash Cond: (n.n = filter_1.x)
42.          

Initplan (forCTE Scan)

43. 0.000 0.000 ↓ 0.0

WorkTable Scan on bloom25 (cost=0.00..47.25 rows=700 width=72) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=329,843,135.91..332,094,365.72 rows=900,491,922 width=64) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=906.29..3,897,189.69 rows=4,300 width=104) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..10.10 rows=500 width=36) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..0.78 rows=25 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Function Scan on unnest tiltdirection (cost=0.00..1.00 rows=100 width=4) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Function Scan on unnest tilt (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Function Scan on unnest r_2 (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Function Scan on unnest facedirection (cost=0.00..1.00 rows=100 width=4) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

CTE Scan on spatialid (cost=0.00..339,504.00 rows=16,975,200 width=32) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

CTE Scan on seed4 seed4_1 (cost=0.00..112.62 rows=13 width=112) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

CTE Scan on prune20 prune20_1 (cost=0.00..1,847.56 rows=215 width=72) (actual rows= loops=)

  • Sort Key: spatial2.id
  • Filter: (iter = 4)
  • Filter: (iter = 16)
  • Filter: (iter < 25)
  • Sort Key: (unnest(bloom25_1.stack))
  • Hash Cond: (n_2.n = filter_3.x)
55. 0.000 0.000 ↓ 0.0

Sort (cost=3.84..4.09 rows=100 width=32) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=902.50..906.25 rows=1 width=32) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Hash (cost=900.00..900.00 rows=200 width=32) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=900.00..900.00 rows=200 width=32) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Function Scan on unnest n (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

CTE Scan on spatialid remainder (cost=0.00..339,504.00 rows=16,975,200 width=32) (actual rows= loops=)

61.          

SubPlan (forCTE Scan)

62. 0.000 0.000 ↓ 0.0

Result (cost=906.27..906.28 rows=1 width=32) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.02..88.94 rows=4,300 width=140) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..0.65 rows=25 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Merge Join (cost=41,416,006.15..60,486,794.59 rows=900,491,922 width=64) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Function Scan on unnest face (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

CTE Scan on axis x (cost=0.00..0.10 rows=5 width=4) (actual rows= loops=)

  • One-Time Filter: ((prune20.c1 <= 7) AND CASE WHEN (prune20.prev !~~ '%x%'::text) THEN (r_1.r ~~ '%x%'::text) WHEN (prune20.prev !~~ '%y%'::text) THEN (r_1.r ~~ '%y%'::text) WHEN (prune20.prev !~~ '%z%'::text) THEN (r_1.r ~~ '%z%'::text) ELSE NULL::boolean END)
  • Merge Cond: ((spatial2.x = fil.x) AND (spatial2.y = fil.y) AND (spatial2.z = fil.z))
  • Sort Key: (unnest(seed4_1.stack))
  • Sort Key: (unnest(prune20_1.stack))
  • Filter: (r.r = f)
  • Filter: (firstmove_1.r = f)
68.          

Initplan (forCTE Scan)

69. 0.000 0.000 ↓ 0.0

WorkTable Scan on prune20 (cost=0.00..2.92 rows=43 width=112) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Sort (cost=20,708,003.08..20,920,193.08 rows=84,876,000 width=44) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Materialize (cost=20,708,003.08..21,132,383.08 rows=84,876,000 width=44) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Function Scan on unnest r_1 (cost=0.02..1.01 rows=100 width=32) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

CTE Scan on axis z (cost=0.00..0.10 rows=5 width=4) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

CTE Scan on axis y (cost=0.00..0.10 rows=5 width=4) (actual rows= loops=)

  • Sort Key: spatial2.x, spatial2.y, spatial2.z
  • Filter: (iter < 16)
75. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=902.50..906.25 rows=1 width=32) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

CTE Scan on filter filter_3 (cost=0.00..900.00 rows=200 width=32) (actual rows= loops=)

  • Hash Cond: (n_1.n = filter_2.x)
  • Filter: ($32 = f)
80. 0.000 0.000 ↓ 0.0

Sort (cost=20,708,003.08..20,920,193.08 rows=84,876,000 width=44) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

CTE Scan on spatial2 (cost=0.00..1,697,520.00 rows=84,876,000 width=44) (actual rows= loops=)

  • Sort Key: fil.x, fil.y, fil.z
82. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=0) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Hash (cost=900.00..900.00 rows=200 width=32) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Function Scan on unnest n_1 (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

CTE Scan on spatial2 fil (cost=0.00..1,697,520.00 rows=84,876,000 width=44) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

CTE Scan on filter filter_2 (cost=0.00..900.00 rows=200 width=32) (actual rows= loops=)

  • Filter: ($22 = f)
88. 0.000 0.000 ↓ 0.0

Sort (cost=3.84..4.09 rows=100 width=32) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Sort (cost=3.84..4.09 rows=100 width=32) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash (cost=900.00..900.00 rows=200 width=32) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Function Scan on unnest n_2 (cost=0.00..1.00 rows=100 width=32) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

CTE Scan on filter filter_1 (cost=0.00..900.00 rows=200 width=32) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

CTE Scan on filter (cost=0.00..900.00 rows=200 width=32) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=902.50..906.25 rows=1 width=32) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Hash Anti Join (cost=902.50..606,704.95 rows=8,487,600 width=32) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

CTE Scan on firstmove (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)