[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})");
+    }

Yes you are correct. `GiST(ST_StartPoint(geometry))` is workable to replace `btree(ST_X(ST_StartPoint(geometry)), ST_Y(ST_StartPoint(geometry)))`, however the btree is significantly faster. The reason is that we are only ever doing exact lookups with `==`, so the GiST overhead to support range lookups `&&` is unnecessary.

I do not have any concrete numbers for this use case, but back when I was doing a very similar recursive CTE  here https://github.com/openstreetmap-carto/openstreetmap-carto/issues/951#issuecomment-4524503245 I found that the GiST made the recursive CTE overall about 2 or 3 times slower than the btree

I am happy to go either way but first I think I need a more up-to-date performance answer in tradeoff against what I completely agree is a loss to readability.

In order to get a real performance answer I think I will need to set up a whole planet osm in Postgres, and apply real osmchanges, which I have been putting off but might do this weekend

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

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