@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/[email protected]>_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
