[Tile-serving] [openstreetmap/osm2pgsql] Use SP-GiST indexes when available (#1357)

[Christian Quest]

Here is a comparison, done on an idle server with a fresh planet import:

I kept the best EXPLAIN ANALYZE time of 10 runs.

## GIST

```
osm=# create index on planet_osm_roads using gist(way);
CREATE INDEX
Time: 275889,369 ms (04:35,889)

public.planet_osm_roads_way_idx          | 1913 MB (index)


EXPLAIN ANALYZE select count(*) from planet_osm_roads where way && st_transform(st_envelope(st_buffer(st_geogfromtext('SRID=4326;POINT(2.35 48.85)'), 239062.5)::geometry), 3857);

 Aggregate  (cost=454451.80..454451.81 rows=1 width=8) (actual time=188.350..188.351 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=44022
   ->  Bitmap Heap Scan on public.planet_osm_roads  (cost=11925.94..453188.08 rows=505487 width=0) (actual time=61.695..164.610 rows=467675 loops=1)
         Recheck Cond: (planet_osm_roads.way && '0103000020110F000001000000050000008A984F390992F8C0A8995B9EFC7A56418A984F390992F8C055909B831C42594127357A78A00C234155909B831C42594127357A78A00C2341A8995B9EFC7A56418A984F390992F8C0A8995B9EFC7A5641'::geometry)
         Heap Blocks: exact=36496
         Buffers: shared hit=44022
         ->  Bitmap Index Scan on planet_osm_roads_way_idx  (cost=0.00..11799.57 rows=505487 width=0) (actual time=56.365..56.366 rows=467675 loops=1)
               Index Cond: (planet_osm_roads.way && '0103000020110F000001000000050000008A984F390992F8C0A8995B9EFC7A56418A984F390992F8C055909B831C42594127357A78A00C234155909B831C42594127357A78A00C2341A8995B9EFC7A56418A984F390992F8C0A8995B9EFC7A5641'::geometry)
               Buffers: shared hit=7526
 Planning Time: 0.545 ms
 Execution Time: 188.387 ms

````
## SP-GIST

````
osm=# create index on planet_osm_roads using spgist(way);
CREATE INDEX
Time: 116051,852 ms (01:56,052)

public.planet_osm_roads_way_idx       | 731 MB (index)


EXPLAIN ANALYZE select count(*) from planet_osm_roads where way && st_transform(st_envelope(st_buffer(st_geogfromtext('SRID=4326;POINT(2.35 48.85)'), 239062.5)::geometry), 3857);

 Aggregate  (cost=449537.00..449537.01 rows=1 width=8) (actual time=205.947..205.948 rows=1 loops=1)
   Output: count(*)
   Buffers: shared hit=71597
   ->  Bitmap Heap Scan on public.planet_osm_roads  (cost=7011.14..448273.28 rows=505487 width=0) (actual time=88.011..182.291 rows=467675 loops=1)
         Recheck Cond: (planet_osm_roads.way && '0103000020110F000001000000050000008A984F390992F8C0A8995B9EFC7A56418A984F390992F8C055909B831C42594127357A78A00C234155909B831C42594127357A78A00C2341A8995B9EFC7A56418A984F390992F8C0A8995B9EFC7A5641'::geometry)
         Heap Blocks: exact=36496
         Buffers: shared hit=71597
         ->  Bitmap Index Scan on planet_osm_roads_way_idx  (cost=0.00..6884.77 rows=505487 width=0) (actual time=82.911..82.911 rows=467675 loops=1)
               Index Cond: (planet_osm_roads.way && '0103000020110F000001000000050000008A984F390992F8C0A8995B9EFC7A56418A984F390992F8C055909B831C42594127357A78A00C234155909B831C42594127357A78A00C2341A8995B9EFC7A56418A984F390992F8C0A8995B9EFC7A5641'::geometry)
               Buffers: shared hit=35101
 Planning Time: 0.344 ms
 Execution Time: 206.379 ms
```

There is a small 10% impact on a single query but of course all the data is in the cache and possible benefit of have more index/data in the cache thanks to small spgist index is lost in this single test.
A real world test with a full speed rendering run is needed to have a better idea. I'll try to do that soon...

-- 
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1357#issuecomment-910085966
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20210901/af92cd29/attachment-0001.htm>