[Tile-serving] [osm2pgsql-dev/osm2pgsql] Add grouped-linemerge generalization (PR #2482)

[leijurv]

@leijurv commented on this pull request.



> +    }
+}
+
+void gen_grouped_linemerge_t::process_create()
+{
+    if (get_params().get_bool("create_indexes", true)) {
+        log_gen("Creating endpoint indexes on source table...");
+        dbexec(
+            R"(CREATE INDEX IF NOT EXISTS "{idx_startpt}" ON {src} USING btree)"
+            R"( (ST_X(ST_StartPoint("{geom_column}")),)"
+            R"( ST_Y(ST_StartPoint("{geom_column}"))) {index_predicate})");
+        dbexec(
+            R"(CREATE INDEX IF NOT EXISTS "{idx_endpt}" ON {src} USING btree)"
+            R"( (ST_X(ST_EndPoint("{geom_column}")),)"
+            R"( ST_Y(ST_EndPoint("{geom_column}"))) {index_predicate})");
+    }

> Is it possible to put a ST_Point in a btree and get equality comparison?

Yes. I ran a quick test just now. Using the same data as this comment https://github.com/osm2pgsql-dev/osm2pgsql/pull/2482#issuecomment-4605092533 where I expire a Z13-size area ([this tile](https://tile.openstreetmap.org/13/1405/3271.png)) and explore 4430 total endpoints (some of those roads extend quite far)

It's interesting that hot vs cold doesn't matter much for GiST - my interpretation is that the btree index is more I/O-bound, while the GiST index does more nontrivial CPU work while traversing to its goal. The speedup from cold to warm is similar though, in total time (not relative time). So I would suspect that the total number of disk reads is about the same between btree and gist, it's just that gist is doing way more computations along the way.

| Variant | cold (median) | cold (min) | warm | warm per-probe (×4430 loops) | index disk space |
|---|---|---|---|---|---|
| A — (x,y) btree | ~247 ms | 235 ms | 67 ms | 0.002 ms | 364MB |
| B — point btree | ~282 ms | 229 ms | 110 ms | 0.007 ms | 576MB |
| C — point gist | ~1070 ms | 1033 ms | 928 ms | 0.09 ms | 479MB |

I have committed https://github.com/osm2pgsql-dev/osm2pgsql/pull/2482/changes/b0dd0994730a6b870ed7e5134b100dd808392ce6

It is an improvement to readability but a bit slower, compare to https://github.com/osm2pgsql-dev/osm2pgsql/pull/2482#issuecomment-4605092533

| expired tile  | region | walk | collect | delete | insert | total |
|---|---|---|---|---|---|---|
| z13  |  71 | 303 |  69 | 155 |  90 | **688 ms** |
| z14           |  70 | 158 |  33 |  72 |  36 | **368 ms** |
| z15           |  74 |  71 |  16 |  40 |  14 | **215 ms** |
| z16           |  73 |  73 |   8 |  32 |  12 | **197 ms** |
| z17           |  84 |  66 |  11 |  27 |  16 | **203 ms** |
| z18   |  71 |  52 |   7 |  24 |   7 | **161 ms** |
| z19           |  82 |  71 |  16 |  36 |  13 | **218 ms** |
| z20    |  71 |  59 |   6 |  29 |   7 | **172 ms** |


-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/pull/2482#discussion_r3365138163
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/pull/2482/review/4439367530 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20260605/6bf31c9d/attachment.htm>