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

[Paul Norman]

Since PostGIS 2.5 and PostgreSQL 11, spgist indexes are available for 2d and 3d geometries. PostGIS performance testing [indicates they are beneficial with manhy overlapping objects.](https://postgis.net/docs/using_postgis_dbmanagement.html#spgist_indexes) This is the situation with most osm2pgsql tables.

I collected some real-world data on the differences between the indexes. To better emulate tile server queries I turned parallelism and jit off.

 Property | GiST | SP-GiST 
----|----|---
polygon Build time | 2h38m | 2h4m
polygon Index size¹ | 71 GB | 21 GB
PIP query | 22.4s | 11.9s
polygon bbox query | 189ms | 188ms 
point build time | 31m18s | 25m51s
point index size | 7.6 GB | 7.0 GB
point tile query | .212ms | .222ms

The difference in polygon performance is likely to be more significant on servers with less RAM or when more queries are running, increasing the size of the working set. Mine has enough RAM to keep the entire GiST index cached in memory.

Most polygon and linestring tables will have objects that bbox overlap, so we should use spgist indexes for those tables. I could go either way on points.

## Queries
Point in polygon (PIP)
```sql
create materialized view vancouver_addresses as
select way from planet_osm_polygon
  where "addr:housenumber" is not null
    and way && st_setsrid( st_makebox2d( st_makepoint(-13749536,6275628), st_makepoint(-13575258,6350078)), 3857);

select *
from vancouver_addresses v
join planet_osm_polygon p on (st_intersects(v.way, p.way))
where p.building is not null;
```

polygon bbox
```sql
select * from planet_osm_polygon where way && st_setsrid( st_makebox2d( st_makepoint(-13749536,6275628), st_makepoint(-13575258,6350078)), 3857);
```

point tile
```sql
select * from planet_osm_point where way && st_setsrid( st_makebox2d( st_makepoint(-13706082,6321652), st_makepoint(-13704853,6322874)), 3857);
```


## Footnotes
1: Index sizes are based on a single index build, but at least GiST index size is variable

-- 
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20201203/86f2764f/attachment.htm>