explain.depesz.com

PostgreSQL's explain analyze made readable

Result: dxpS

Settings
# exclusive inclusive rows x rows loops node
1. 0.041 1,211.755 ↓ 89.5 179 1

Unique (cost=1,164,500.35..1,164,500.37 rows=2 width=72) (actual time=1,211.698..1,211.755 rows=179 loops=1)

  • Output: l.link_id, l.tile_num, no.named_object_id, no.named_object_class, no.admin_level, no.country_id, no.is_long_road, no.level, no.tile_num, no.morton_code, no.boundrect, no.occurrence_level, no.occurrence_tile_num, no.sw_rule_used, no.unique_id, no.is_exit_road, no.is_ramp, no_1.unique_id
  • Functions: 141
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 10.553 ms, Inlining 93.364 ms, Optimization 556.647 ms, Emission 384.367 ms, Total 1044.930 ms
2.          

CTE duble

3. 127.500 1,200.367 ↓ 2.3 767 1

Gather (cost=1,933.94..1,164,438.45 rows=338 width=22) (actual time=775.732..1,200.367 rows=767 loops=1)

  • Output: l_2.link_id, l_2.tile_num, n.name, no_3.unique_id
  • Workers Planned: 1
  • Workers Launched: 1
  • JIT for worker 0:
  • Functions: 35
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 2.855 ms, Inlining 46.787 ms, Optimization 130.335 ms, Emission 89.543 ms, Total 269.520 ms
4. 0.192 1,072.867 ↓ 1.9 384 2 / 2

Nested Loop (cost=933.94..1,163,404.65 rows=199 width=22) (actual time=530.495..1,072.867 rows=384 loops=2)

  • Output: l_2.link_id, l_2.tile_num, n.name, no_3.unique_id
  • Inner Unique: true
  • Worker 0: actual time=287.756..948.418 rows=338 loops=1
5. 0.538 1,071.141 ↓ 1.9 384 2 / 2

Nested Loop (cost=933.51..1,163,295.24 rows=199 width=16) (actual time=530.410..1,071.141 rows=384 loops=2)

  • Output: l_2.link_id, l_2.tile_num, no_3.unique_id, non_2.name_id
  • Worker 0: actual time=287.651..946.882 rows=338 loops=1
6. 1.117 986.417 ↑ 1.2 1,559 2 / 2

Nested Loop (cost=933.08..1,162,404.46 rows=1,859 width=20) (actual time=520.497..986.417 rows=1,559 loops=2)

  • Output: l_2.link_id, l_2.tile_num, no_3.unique_id, no_3.named_object_id
  • Worker 0: actual time=271.314..874.734 rows=1,912 loops=1
7. 0.659 972.588 ↓ 1.1 1,412 2 / 2

Nested Loop Left Join (cost=24.71..4,724.83 rows=1,273 width=24) (actual time=520.428..972.588 rows=1,412 loops=2)

  • Output: l_2.link_id, l_2.tile_num, rl_2.road_id, lrr_1.long_road_id
  • Worker 0: actual time=271.232..857.875 rows=1,775 loops=1
8. 0.720 963.454 ↓ 1.1 1,412 2 / 2

Nested Loop (cost=24.29..4,152.45 rows=1,271 width=16) (actual time=520.382..963.454 rows=1,412 loops=2)

  • Output: l_2.link_id, l_2.tile_num, rl_2.road_id
  • Worker 0: actual time=271.161..846.488 rows=1,775 loops=1
9. 523.151 523.438 ↓ 1.5 1,404 2 / 2

Parallel Bitmap Heap Scan on nds.link l_2 (cost=23.85..1,825.39 rows=961 width=16) (actual time=518.282..523.438 rows=1,404 loops=2)

  • Output: l_2.link_id, l_2.discriminator, l_2.nndb_id, l_2.level, l_2.tile_num, l_2.seq_num, l_2.geom_id
  • Recheck Cond: ((l_2.tile_num >= 8,506,195) AND (l_2.tile_num <= 8,506,204))
  • Filter: (l_2.level = 13)
  • Heap Blocks: exact=521
  • Worker 0: actual time=267.062..273.135 rows=1,832 loops=1
10. 0.286 0.286 ↓ 1.6 2,807 1 / 2

Bitmap Index Scan on nx_link_tilenum_linkid (cost=0.00..23.45 rows=1,801 width=0) (actual time=0.573..0.573 rows=2,807 loops=1)

  • Index Cond: ((l_2.tile_num >= 8,506,195) AND (l_2.tile_num <= 8,506,204))
11. 439.296 439.296 ↑ 1.0 1 2,807 / 2

Index Scan using nx_roadlink_linkid on nndb.road_link rl_2 (cost=0.43..2.41 rows=1 width=16) (actual time=0.247..0.313 rows=1 loops=2,807)

  • Output: rl_2.road_link_id, rl_2.road_id, rl_2.link_id, rl_2.left_range_id, rl_2.right_range_id, rl_2.is_exit, rl_2.explicatable, rl_2.junction, rl_2.postal, rl_2.road_sign, rl_2.scenic, rl_2.vanity
  • Index Cond: (rl_2.link_id = l_2.nndb_id)
  • Worker 0: actual time=0.231..0.312 rows=1 loops=1,832
12. 8.475 8.475 ↓ 0.0 0 2,825 / 2

Index Scan using nx_longtosimpleroadnamedobject_simpleroadid on nds.long_to_simple_road_named_object lrr_1 (cost=0.42..0.44 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=2,825)

  • Output: lrr_1.long_road_id, lrr_1.simple_road_id
  • Index Cond: (lrr_1.simple_road_id = rl_2.road_id)
  • Worker 0: actual time=0.006..0.006 rows=0 loops=1,775
13. 2.825 12.712 ↑ 1.0 1 2,825 / 2

Bitmap Heap Scan on nds.named_object no_3 (cost=908.37..909.40 rows=1 width=12) (actual time=0.008..0.009 rows=1 loops=2,825)

  • Output: no_3.named_object_id, no_3.named_object_class, no_3.admin_level, no_3.country_id, no_3.is_long_road, no_3.level, no_3.tile_num, no_3.morton_code, no_3.boundrect, no_3.occurrence_level, no_3.occurrence_tile_num, no_3.sw_rule_used, no_3.unique_id, no_3.is_exit_road, no_3.is_ramp
  • Recheck Cond: ((no_3.named_object_id = lrr_1.long_road_id) OR (no_3.named_object_id = rl_2.road_id))
  • Filter: (no_3.country_id = 20,147,700)
  • Heap Blocks: exact=1,189
  • Worker 0: actual time=0.008..0.009 rows=1 loops=1,775
14. 0.000 9.888 ↓ 0.0 0 2,825 / 2

BitmapOr (cost=908.37..908.37 rows=2 width=0) (actual time=0.007..0.007 rows=0 loops=2,825)

  • Worker 0: actual time=0.007..0.007 rows=0 loops=1,775
15. 1.413 1.413 ↓ 0.0 0 2,825 / 2

Bitmap Index Scan on nx_namedobject_namedobjectid (cost=0.00..0.46 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=2,825)

  • Index Cond: (no_3.named_object_id = lrr_1.long_road_id)
  • Worker 0: actual time=0.000..0.000 rows=0 loops=1,775
16. 8.475 8.475 ↑ 1.0 1 2,825 / 2

Bitmap Index Scan on nx_namedobject_namedobjectid (cost=0.00..0.44 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=2,825)

  • Index Cond: (no_3.named_object_id = rl_2.road_id)
  • Worker 0: actual time=0.006..0.006 rows=1 loops=1,775
17. 84.186 84.186 ↓ 0.0 0 3,118 / 2

Index Scan using nx_namedobjectname_namedobjectid on nds.named_object_name non_2 (cost=0.43..0.47 rows=1 width=12) (actual time=0.054..0.054 rows=0 loops=3,118)

  • Output: non_2.named_object_id, non_2.name_id, non_2.name_type, non_2.format, non_2.prefix, non_2.suffix, non_2.base_name, non_2.street_type, non_2.admin_class, non_2.nndb_name_id, non_2.nndb_table_name
  • Index Cond: (non_2.named_object_id = no_3.named_object_id)
  • Filter: (non_2.format = 3)
  • Rows Removed by Filter: 1
  • Worker 0: actual time=0.037..0.037 rows=0 loops=1,912
18. 1.534 1.534 ↑ 1.0 1 767 / 2

Index Scan using pk_name on nds.name n (cost=0.43..0.55 rows=1 width=18) (actual time=0.004..0.004 rows=1 loops=767)

  • Output: n.name_id, n.nndb_language_id, n.name
  • Index Cond: (n.name_id = non_2.name_id)
  • Worker 0: actual time=0.004..0.004 rows=1 loops=338
19.          

CTE tt

20. 0.383 1,201.703 ↓ 179.0 179 1

Merge Join (cost=41.91..48.69 rows=1 width=8) (actual time=1,201.189..1,201.703 rows=179 loops=1)

  • Output: d1.link_id, d1.tile_num
  • Merge Cond: ((d1.link_id = d2.link_id) AND (d1.tile_num = d2.tile_num) AND ((d1.name)::text = (d2.name)::text))
  • Join Filter: (d1.unique_id < d2.unique_id)
  • Rows Removed by Join Filter: 946
21. 0.402 1,200.998 ↓ 2.3 767 1

Sort (cost=20.96..21.80 rows=338 width=528) (actual time=1,200.926..1,200.998 rows=767 loops=1)

  • Output: d1.link_id, d1.tile_num, d1.name, d1.unique_id
  • Sort Key: d1.link_id, d1.tile_num, d1.name
  • Sort Method: quicksort Memory: 83kB
22. 1,200.596 1,200.596 ↓ 2.3 767 1

CTE Scan on duble d1 (cost=0.00..6.76 rows=338 width=528) (actual time=775.736..1,200.596 rows=767 loops=1)

  • Output: d1.link_id, d1.tile_num, d1.name, d1.unique_id
23. 0.253 0.322 ↓ 3.3 1,125 1

Sort (cost=20.96..21.80 rows=338 width=528) (actual time=0.227..0.322 rows=1,125 loops=1)

  • Output: d2.link_id, d2.tile_num, d2.name, d2.unique_id
  • Sort Key: d2.link_id, d2.tile_num, d2.name
  • Sort Method: quicksort Memory: 83kB
24. 0.069 0.069 ↓ 2.3 767 1

CTE Scan on duble d2 (cost=0.00..6.76 rows=338 width=528) (actual time=0.001..0.069 rows=767 loops=1)

  • Output: d2.link_id, d2.tile_num, d2.name, d2.unique_id
25. 0.273 1,211.714 ↓ 179.0 358 1

Sort (cost=13.22..13.22 rows=2 width=72) (actual time=1,211.697..1,211.714 rows=358 loops=1)

  • Output: l.link_id, l.tile_num, no.named_object_id, no.named_object_class, no.admin_level, no.country_id, no.is_long_road, no.level, no.tile_num, no.morton_code, no.boundrect, no.occurrence_level, no.occurrence_tile_num, no.sw_rule_used, no.unique_id, no.is_exit_road, no.is_ramp, no_1.unique_id
  • Sort Key: l.link_id, l.tile_num, no.is_long_road DESC, no.unique_id
  • Sort Method: quicksort Memory: 75kB
26. 0.270 1,211.441 ↓ 179.0 358 1

Nested Loop (cost=2.16..13.21 rows=2 width=72) (actual time=1,201.315..1,211.441 rows=358 loops=1)

  • Output: l.link_id, l.tile_num, no.named_object_id, no.named_object_class, no.admin_level, no.country_id, no.is_long_road, no.level, no.tile_num, no.morton_code, no.boundrect, no.occurrence_level, no.occurrence_tile_num, no.sw_rule_used, no.unique_id, no.is_exit_road, no.is_ramp, no_1.unique_id
  • Inner Unique: true
27. 0.047 1,210.455 ↓ 179.0 358 1

Append (cost=1.73..8.31 rows=2 width=12) (actual time=1,201.276..1,210.455 rows=358 loops=1)

28. 0.151 1,206.720 ↓ 181.0 181 1

Nested Loop (cost=1.73..3.86 rows=1 width=12) (actual time=1,201.275..1,206.720 rows=181 loops=1)

  • Output: l.link_id, l.tile_num, no_1.unique_id
  • Inner Unique: true
29. 0.001 1,206.207 ↓ 181.0 181 1

Nested Loop (cost=1.30..3.41 rows=1 width=24) (actual time=1,201.265..1,206.207 rows=181 loops=1)

  • Output: l.link_id, l.tile_num, rl.road_id, non.named_object_id
30. 0.192 1,205.255 ↓ 317.0 317 1

Nested Loop (cost=0.87..2.94 rows=1 width=16) (actual time=1,201.250..1,205.255 rows=317 loops=1)

  • Output: l.link_id, l.tile_num, rl.road_id
31. 0.081 1,204.347 ↓ 179.0 179 1

Nested Loop (cost=0.43..2.47 rows=1 width=16) (actual time=1,201.239..1,204.347 rows=179 loops=1)

  • Output: l.link_id, l.tile_num, l.nndb_id
  • Inner Unique: true
32. 1,201.760 1,201.760 ↓ 179.0 179 1

CTE Scan on tt (cost=0.00..0.02 rows=1 width=4) (actual time=1,201.191..1,201.760 rows=179 loops=1)

  • Output: tt.link_id, tt.tile_num
33. 2.506 2.506 ↑ 1.0 1 179

Index Scan using pk_link on nds.link l (cost=0.43..2.45 rows=1 width=16) (actual time=0.014..0.014 rows=1 loops=179)

  • Output: l.link_id, l.discriminator, l.nndb_id, l.level, l.tile_num, l.seq_num, l.geom_id
  • Index Cond: (l.link_id = tt.link_id)
34. 0.716 0.716 ↓ 2.0 2 179

Index Scan using nx_roadlink_linkid on nndb.road_link rl (cost=0.43..0.46 rows=1 width=16) (actual time=0.003..0.004 rows=2 loops=179)

  • Output: rl.road_link_id, rl.road_id, rl.link_id, rl.left_range_id, rl.right_range_id, rl.is_exit, rl.explicatable, rl.junction, rl.postal, rl.road_sign, rl.scenic, rl.vanity
  • Index Cond: (rl.link_id = l.nndb_id)
35. 0.951 0.951 ↑ 1.0 1 317

Index Scan using nx_namedobjectname_namedobjectid on nds.named_object_name non (cost=0.43..0.45 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=317)

  • Output: non.named_object_id, non.name_id, non.name_type, non.format, non.prefix, non.suffix, non.base_name, non.street_type, non.admin_class, non.nndb_name_id, non.nndb_table_name
  • Index Cond: (non.named_object_id = rl.road_id)
  • Filter: (non.format = 3)
  • Rows Removed by Filter: 0
36. 0.362 0.362 ↑ 1.0 1 181

Index Scan using nx_namedobject_namedobjectid on nds.named_object no_1 (cost=0.43..0.45 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=181)

  • Output: no_1.named_object_id, no_1.named_object_class, no_1.admin_level, no_1.country_id, no_1.is_long_road, no_1.level, no_1.tile_num, no_1.morton_code, no_1.boundrect, no_1.occurrence_level, no_1.occurrence_tile_num, no_1.sw_rule_used, no_1.unique_id, no_1.is_exit_road, no_1.is_ramp
  • Index Cond: (no_1.named_object_id = rl.road_id)
37. 0.167 3.688 ↓ 177.0 177 1

Nested Loop (cost=2.15..4.42 rows=1 width=12) (actual time=0.075..3.688 rows=177 loops=1)

  • Output: l_1.link_id, l_1.tile_num, no_2.unique_id
38. 0.168 3.059 ↓ 231.0 231 1

Nested Loop (cost=1.72..3.95 rows=1 width=28) (actual time=0.064..3.059 rows=231 loops=1)

  • Output: l_1.link_id, l_1.tile_num, lrr.long_road_id, no_2.unique_id, no_2.named_object_id
  • Inner Unique: true
39. 0.225 2.429 ↓ 231.0 231 1

Nested Loop (cost=1.29..3.40 rows=1 width=16) (actual time=0.054..2.429 rows=231 loops=1)

  • Output: l_1.link_id, l_1.tile_num, lrr.long_road_id
40. 0.172 1.570 ↓ 317.0 317 1

Nested Loop (cost=0.87..2.94 rows=1 width=16) (actual time=0.039..1.570 rows=317 loops=1)

  • Output: l_1.link_id, l_1.tile_num, rl_1.road_id
41. 0.103 0.861 ↓ 179.0 179 1

Nested Loop (cost=0.43..2.47 rows=1 width=16) (actual time=0.025..0.861 rows=179 loops=1)

  • Output: l_1.link_id, l_1.tile_num, l_1.nndb_id
  • Inner Unique: true
42. 0.042 0.042 ↓ 179.0 179 1

CTE Scan on tt tt_1 (cost=0.00..0.02 rows=1 width=4) (actual time=0.001..0.042 rows=179 loops=1)

  • Output: tt_1.link_id, tt_1.tile_num
43. 0.716 0.716 ↑ 1.0 1 179

Index Scan using pk_link on nds.link l_1 (cost=0.43..2.45 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=179)

  • Output: l_1.link_id, l_1.discriminator, l_1.nndb_id, l_1.level, l_1.tile_num, l_1.seq_num, l_1.geom_id
  • Index Cond: (l_1.link_id = tt_1.link_id)
44. 0.537 0.537 ↓ 2.0 2 179

Index Scan using nx_roadlink_linkid on nndb.road_link rl_1 (cost=0.43..0.46 rows=1 width=16) (actual time=0.003..0.003 rows=2 loops=179)

  • Output: rl_1.road_link_id, rl_1.road_id, rl_1.link_id, rl_1.left_range_id, rl_1.right_range_id, rl_1.is_exit, rl_1.explicatable, rl_1.junction, rl_1.postal, rl_1.road_sign, rl_1.scenic, rl_1.vanity
  • Index Cond: (rl_1.link_id = l_1.nndb_id)
45. 0.634 0.634 ↑ 1.0 1 317

Index Scan using nx_longtosimpleroadnamedobject_simpleroadid on nds.long_to_simple_road_named_object lrr (cost=0.42..0.44 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=317)

  • Output: lrr.long_road_id, lrr.simple_road_id
  • Index Cond: (lrr.simple_road_id = rl_1.road_id)
46. 0.462 0.462 ↑ 1.0 1 231

Index Scan using nx_namedobject_namedobjectid on nds.named_object no_2 (cost=0.43..0.55 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=231)

  • Output: no_2.named_object_id, no_2.named_object_class, no_2.admin_level, no_2.country_id, no_2.is_long_road, no_2.level, no_2.tile_num, no_2.morton_code, no_2.boundrect, no_2.occurrence_level, no_2.occurrence_tile_num, no_2.sw_rule_used, no_2.unique_id, no_2.is_exit_road, no_2.is_ramp
  • Index Cond: (no_2.named_object_id = lrr.long_road_id)
47. 0.462 0.462 ↑ 1.0 1 231

Index Scan using nx_namedobjectname_namedobjectid on nds.named_object_name non_1 (cost=0.43..0.46 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=231)

  • Output: non_1.named_object_id, non_1.name_id, non_1.name_type, non_1.format, non_1.prefix, non_1.suffix, non_1.base_name, non_1.street_type, non_1.admin_class, non_1.nndb_name_id, non_1.nndb_table_name
  • Index Cond: (non_1.named_object_id = no_2.named_object_id)
  • Filter: (non_1.format = 3)
  • Rows Removed by Filter: 0
48. 0.716 0.716 ↑ 1.0 1 358

Index Scan using nx_namedobject_uniqueid on nds.named_object no (cost=0.43..2.45 rows=1 width=60) (actual time=0.002..0.002 rows=1 loops=358)

  • Output: no.named_object_id, no.named_object_class, no.admin_level, no.country_id, no.is_long_road, no.level, no.tile_num, no.morton_code, no.boundrect, no.occurrence_level, no.occurrence_tile_num, no.sw_rule_used, no.unique_id, no.is_exit_road, no.is_ramp
  • Index Cond: (no.unique_id = no_1.unique_id)
Execution time : 1,258.082 ms