explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3rDy

Settings
# exclusive inclusive rows x rows loops node
1. 3.322 23,078.338 ↓ 1,379.0 1,379 1

WindowAgg (cost=44,980.95..44,981.06 rows=1 width=1,824) (actual time=23,074.914..23,078.338 rows=1,379 loops=1)

2.          

CTE nodes

3. 10.440 80.728 ↓ 3,875.4 19,377 1

Nested Loop (cost=10,383.12..14,809.22 rows=5 width=3,857) (actual time=24.208..80.728 rows=19,377 loops=1)

  • Join Filter: (n.zoneid = tbl_visual_zone.id)
4. 6.304 31.534 ↓ 130.9 19,377 1

Hash Join (cost=10,382.69..14,246.13 rows=148 width=115) (actual time=24.189..31.534 rows=19,377 loops=1)

  • Hash Cond: ((sub.code)::text = (z.zipcode)::text)
5. 1.079 1.079 ↓ 1.0 1,149 1

Index Scan using tbl_visual_zone_layerid_idx on tbl_visual_zone sub (cost=0.42..3,737.11 rows=1,126 width=19) (actual time=0.012..1.079 rows=1,149 loops=1)

  • Index Cond: (layerid = 102)
6. 3.901 24.151 ↑ 1.0 19,377 1

Hash (cost=10,140.06..10,140.06 rows=19,377 width=103) (actual time=24.151..24.151 rows=19,377 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2864kB
7. 6.834 20.250 ↑ 1.0 19,377 1

Hash Join (cost=4,344.98..10,140.06 rows=19,377 width=103) (actual time=9.689..20.250 rows=19,377 loops=1)

  • Hash Cond: (z.id = n.zoneid)
8. 3.942 3.942 ↑ 4.4 19,782 1

Seq Scan on tbl_zones z (cost=0.00..5,271.22 rows=88,022 width=9) (actual time=0.187..3.942 rows=19,782 loops=1)

9. 3.129 9.474 ↑ 1.0 19,377 1

Hash (cost=4,102.77..4,102.77 rows=19,377 width=94) (actual time=9.474..9.474 rows=19,377 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2678kB
10. 6.345 6.345 ↑ 1.0 19,377 1

Seq Scan on proj_snd_locations n (cost=0.00..4,102.77 rows=19,377 width=94) (actual time=2.585..6.345 rows=19,377 loops=1)

11. 38.754 38.754 ↑ 1.0 1 19,377

Index Scan using tbl_visual_zone_id_idx on tbl_visual_zone (cost=0.42..3.79 rows=1 width=3,766) (actual time=0.002..0.002 rows=1 loops=19,377)

  • Index Cond: (id = z.id)
  • Filter: (layerid = 101)
12.          

CTE zones

13. 9.680 52.363 ↑ 4.4 19,782 1

Hash Right Join (cost=8,465.66..8,913.35 rows=88,022 width=60) (actual time=34.529..52.363 rows=19,782 loops=1)

  • Hash Cond: (proj_snd_turnover.zoneid = z_1.id)
14. 22.393 31.927 ↓ 1.2 15,645 1

HashAggregate (cost=2,094.16..2,226.82 rows=13,265 width=12) (actual time=23.487..31.927 rows=15,645 loops=1)

  • Group Key: proj_snd_turnover.zoneid
15. 9.534 9.534 ↑ 1.0 84,238 1

Seq Scan on proj_snd_turnover (cost=0.00..1,672.98 rows=84,238 width=12) (actual time=0.011..9.534 rows=84,238 loops=1)

  • Filter: (iteration = 0)
16. 2.662 10.756 ↑ 4.4 19,782 1

Hash (cost=5,271.22..5,271.22 rows=88,022 width=52) (actual time=10.756..10.756 rows=19,782 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 2725kB
17. 8.094 8.094 ↑ 4.4 19,782 1

Seq Scan on tbl_zones z_1 (cost=0.00..5,271.22 rows=88,022 width=52) (actual time=0.190..8.094 rows=19,782 loops=1)

18.          

CTE sub

19. 1,550.116 5,539.568 ↓ 737.0 3,212,400 1

Hash Join (cost=3,314.93..5,449.04 rows=4,359 width=360) (actual time=3,961.931..5,539.568 rows=3,212,400 loops=1)

  • Hash Cond: (z_2.zoneid = dt.zoneid)
20. 62.065 62.065 ↑ 4.4 19,782 1

CTE Scan on zones z_2 (cost=0.00..1,760.44 rows=88,022 width=60) (actual time=34.530..62.065 rows=19,782 loops=1)

21. 3,030.951 3,927.387 ↓ 3,573.3 3,212,400 1

Hash (cost=3,303.69..3,303.69 rows=899 width=304) (actual time=3,927.387..3,927.387 rows=3,212,400 loops=1)

  • Buckets: 4194304 (originally 1024) Batches: 1 (originally 1) Memory Usage: 6251509kB
22. 382.462 896.436 ↓ 3,573.3 3,212,400 1

Nested Loop (cost=5.82..3,303.69 rows=899 width=304) (actual time=0.027..896.436 rows=3,212,400 loops=1)

23. 10.172 10.172 ↓ 3,875.4 19,377 1

CTE Scan on nodes n_1 (cost=0.00..0.10 rows=5 width=292) (actual time=0.001..10.172 rows=19,377 loops=1)

24. 290.655 503.802 ↑ 1.1 166 19,377

Bitmap Heap Scan on proj_snd_edt_terrscan_v2 dt (cost=5.82..658.92 rows=180 width=16) (actual time=0.015..0.026 rows=166 loops=19,377)

  • Recheck Cond: (nodeid = n_1.nodeid)
  • Heap Blocks: exact=55886
25. 213.147 213.147 ↑ 1.1 166 19,377

Bitmap Index Scan on proj_snd_edt_terrscan_v2_nodeid_zoneid_idx (cost=0.00..5.78 rows=180 width=0) (actual time=0.011..0.011 rows=166 loops=19,377)

  • Index Cond: (nodeid = n_1.nodeid)
26.          

CTE sub2

27. 235.857 844.001 ↓ 40.0 347,830 1

Nested Loop (cost=0.45..14,312.99 rows=8,696 width=814) (actual time=111.031..844.001 rows=347,830 loops=1)

28. 112.790 260.313 ↓ 40.0 347,831 1

Hash Join (cost=0.16..11,055.80 rows=8,696 width=304) (actual time=111.023..260.313 rows=347,831 loops=1)

  • Hash Cond: (dt_1.id = n_2.nodeid)
29. 36.512 36.512 ↑ 1.0 347,831 1

Seq Scan on proj_snd_edt_terrscan_bike dt_1 (cost=0.00..9,664.31 rows=347,831 width=20) (actual time=0.006..36.512 rows=347,831 loops=1)

30. 10.609 111.011 ↓ 3,875.4 19,377 1

Hash (cost=0.10..0.10 rows=5 width=292) (actual time=111.011..111.011 rows=19,377 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 39536kB
31. 100.402 100.402 ↓ 3,875.4 19,377 1

CTE Scan on nodes n_2 (cost=0.00..0.10 rows=5 width=292) (actual time=24.211..100.402 rows=19,377 loops=1)

32. 347.831 347.831 ↑ 1.0 1 347,831

Index Scan using tbl_zones_full_pkey on tbl_zones z_3 (cost=0.29..0.36 rows=1 width=514) (actual time=0.001..0.001 rows=1 loops=347,831)

  • Index Cond: (id = dt_1.zoneid_join)
33.          

CTE kpibike

34. 7.601 1,594.231 ↓ 96.7 19,343 1

Sort (cost=293.26..293.76 rows=200 width=72) (actual time=1,590.676..1,594.231 rows=19,343 loops=1)

  • Sort Key: sub2.nodeid
  • Sort Method: quicksort Memory: 2258kB
35. 154.561 1,586.630 ↓ 96.7 19,343 1

HashAggregate (cost=282.62..285.62 rows=200 width=72) (actual time=1,580.058..1,586.630 rows=19,343 loops=1)

  • Group Key: sub2.nodeid
36. 1,432.069 1,432.069 ↓ 40.0 347,830 1

CTE Scan on sub2 (cost=0.00..173.92 rows=8,696 width=20) (actual time=111.035..1,432.069 rows=347,830 loops=1)

37.          

CTE kpi

38. 18.973 20,795.196 ↓ 96.9 19,377 1

Sort (cost=1,132.70..1,133.20 rows=200 width=968) (actual time=20,790.729..20,795.196 rows=19,377 loops=1)

  • Sort Key: sub_1.nodeid
  • Sort Method: quicksort Memory: 8447kB
39. 7,367.469 20,776.223 ↓ 96.9 19,377 1

HashAggregate (cost=1,057.06..1,125.06 rows=200 width=968) (actual time=20,248.951..20,776.223 rows=19,377 loops=1)

  • Group Key: sub_1.nodeid
40. 13,408.754 13,408.754 ↓ 737.0 3,212,400 1

CTE Scan on sub sub_1 (cost=0.00..87.18 rows=4,359 width=72) (actual time=3,961.937..13,408.754 rows=3,212,400 loops=1)

41.          

CTE kpi_nodes

42. 19.050 20,837.953 ↓ 3,875.4 19,377 1

Hash Join (cost=0.16..4.99 rows=5 width=1,284) (actual time=20,810.180..20,837.953 rows=19,377 loops=1)

  • Hash Cond: (kpi.nodeid = nodes.nodeid)
43. 20,799.475 20,799.475 ↓ 96.9 19,377 1

CTE Scan on kpi (cost=0.00..4.00 rows=200 width=968) (actual time=20,790.731..20,799.475 rows=19,377 loops=1)

44. 12.216 19.428 ↓ 3,875.4 19,377 1

Hash (cost=0.10..0.10 rows=5 width=292) (actual time=19.428..19.428 rows=19,377 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 39536kB
45. 7.212 7.212 ↓ 3,875.4 19,377 1

CTE Scan on nodes (cost=0.00..0.10 rows=5 width=292) (actual time=0.002..7.212 rows=19,377 loops=1)

46.          

CTE default_dna

47. 0.028 0.047 ↑ 1.0 1 1

Aggregate (cost=4.49..4.50 rows=1 width=128) (actual time=0.047..0.047 rows=1 loops=1)

48. 0.019 0.019 ↑ 1.0 124 1

Seq Scan on proj_dna p (cost=0.00..3.24 rows=124 width=16) (actual time=0.012..0.019 rows=124 loops=1)

49.          

CTE subx

50. 0.000 23,054.932 ↓ 3,868.6 19,343 1

Sort (cost=59.57..59.58 rows=5 width=1,768) (actual time=23,053.160..23,054.932 rows=19,343 loops=1)

  • Sort Key: n_3.nodeid
  • Sort Method: quicksort Memory: 10742kB
51.          

Initplan (forSort)

52. 0.048 0.048 ↑ 1.0 1 1

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

53. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

54. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

55. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

56. 0.040 0.070 ↑ 1.0 1 1

Aggregate (cost=25.55..25.56 rows=1 width=32) (actual time=0.070..0.070 rows=1 loops=1)

57. 0.030 0.030 ↑ 1.0 124 1

Seq Scan on tbl_nodes (cost=0.00..25.24 rows=124 width=5) (actual time=0.017..0.030 rows=124 loops=1)

58. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

59. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_5 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

60. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_6 (cost=0.00..0.03 rows=1 width=32) (actual time=0.003..0.003 rows=1 loops=1)

61. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on default_dna default_dna_7 (cost=0.00..0.03 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1)

62. 0.035 0.049 ↑ 1.0 1 1

Aggregate (cost=25.55..25.57 rows=1 width=32) (actual time=0.049..0.049 rows=1 loops=1)

63. 0.014 0.014 ↑ 1.0 124 1

Seq Scan on tbl_nodes tbl_nodes_1 (cost=0.00..25.24 rows=124 width=5) (actual time=0.004..0.014 rows=124 loops=1)

64. 5.566 13.056 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=13.055..13.056 rows=1 loops=1)

65. 7.490 7.490 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.490 rows=19,377 loops=1)

66. 5.475 12.941 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.941..12.941 rows=1 loops=1)

67. 7.466 7.466 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_1 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.466 rows=19,377 loops=1)

68. 5.656 12.810 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.810..12.810 rows=1 loops=1)

69. 7.154 7.154 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_2 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.154 rows=19,377 loops=1)

70. 5.544 12.737 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.737..12.737 rows=1 loops=1)

71. 7.193 7.193 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_3 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.193 rows=19,377 loops=1)

72. 5.515 12.785 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.785..12.785 rows=1 loops=1)

73. 7.270 7.270 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_4 (cost=0.00..0.10 rows=5 width=32) (actual time=0.000..7.270 rows=19,377 loops=1)

74. 5.589 12.783 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.783..12.783 rows=1 loops=1)

75. 7.194 7.194 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_5 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.194 rows=19,377 loops=1)

76. 5.504 12.856 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=12.856..12.856 rows=1 loops=1)

77. 7.352 7.352 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_6 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.352 rows=19,377 loops=1)

78. 5.541 13.046 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=13.046..13.046 rows=1 loops=1)

79. 7.505 7.505 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_7 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.505 rows=19,377 loops=1)

80. 5.580 13.050 ↑ 1.0 1 1

Aggregate (cost=0.11..0.12 rows=1 width=32) (actual time=13.049..13.050 rows=1 loops=1)

81. 7.470 7.470 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes kpi_nodes_8 (cost=0.00..0.10 rows=5 width=32) (actual time=0.001..7.470 rows=19,377 loops=1)

82. 537.816 23,030.123 ↓ 3,868.6 19,343 1

Hash Join (cost=0.16..7.10 rows=5 width=1,768) (actual time=22,602.155..23,030.123 rows=19,343 loops=1)

  • Hash Cond: (b.nodeid = n_3.nodeid)
83. 1,597.241 1,597.241 ↓ 96.7 19,343 1

CTE Scan on kpibike b (cost=0.00..4.00 rows=200 width=72) (actual time=1,590.677..1,597.241 rows=19,343 loops=1)

84. 15.620 20,895.066 ↓ 3,875.4 19,377 1

Hash (cost=0.10..0.10 rows=5 width=1,152) (actual time=20,895.066..20,895.066 rows=19,377 loops=1)

  • Buckets: 32768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 6993kB
85. 20,879.446 20,879.446 ↓ 3,875.4 19,377 1

CTE Scan on kpi_nodes n_3 (cost=0.00..0.10 rows=5 width=1,152) (actual time=20,810.186..20,879.446 rows=19,377 loops=1)

86. 2.687 23,075.016 ↓ 1,379.0 1,379 1

Sort (cost=0.30..0.31 rows=1 width=1,776) (actual time=23,074.895..23,075.016 rows=1,379 loops=1)

  • Sort Key: subx.zipcode, subx."final score" DESC
  • Sort Method: quicksort Memory: 760kB
87. 0.722 23,072.329 ↓ 1,379.0 1,379 1

WindowAgg (cost=0.27..0.29 rows=1 width=1,776) (actual time=23,071.449..23,072.329 rows=1,379 loops=1)

88. 1.582 23,071.607 ↓ 1,379.0 1,379 1

Sort (cost=0.27..0.28 rows=1 width=1,768) (actual time=23,071.443..23,071.607 rows=1,379 loops=1)

  • Sort Key: subx."final score" DESC
  • Sort Method: quicksort Memory: 760kB
89. 23,070.025 23,070.025 ↓ 1,379.0 1,379 1

CTE Scan on subx (cost=0.00..0.26 rows=1 width=1,768) (actual time=23,053.175..23,070.025 rows=1,379 loops=1)

  • Filter: (("final score" IS NOT NULL) AND ("Average wealth index within 5 minutes drive time" > 0.85) AND ((replace("Local destination Perfect Neighbour score", ','::text, '.'::text))::double precision > '0'::double precision) AND CASE WHEN ((((COALESCE(bike5min, '0'::numeric) + COALESCE(bike10min, '0'::numeric)))::double precision / '2'::double precision) <= '15750'::double precision) THEN ((replace("Retail Perfect Neighbour score", ','::text, '.'::text))::double precision >= '2.5'::double precision) ELSE true END)
  • Rows Removed by Filter: 17964
Planning time : 2.193 ms
Execution time : 24,233.796 ms