Indexing OSM data in your PostGIS database for fast spatial queires is not as straight foreward as one migth hope. And with each release of PostgreSQL / PostGIS there are more options to try out. This talk will explain different spatial indexing concepts and best practices in PostGIS and present some benchmarking results.
When working with OpenStreetMap data in a database you learn pretty soon that you need to index the columns (and rows) which you use for filtering in order to have fast running queries. PostgreSQL offers a variety of these [table access methods](https://youtu.be/W6B8-srOsrs) but when it comes to its spatial extension PostGIS, developers could only use one for the geometry fields for years: The GiST index - an implementation of the [R-tree](https://en.wikipedia.org/wiki/R-tree) search tree concept. But during the last releases new methods were made available, namely BRIN and sp-GiST. Not many resources are yet available to figure out which index strategy to apply for which data processing or analytical workflows. Therefore, I created a simple [benchmark](https://github.com/FxKu/postgis_indexing) to find it out. So far, only for artifical data, but for this years StoM it is planned to extend the experiments to OSM datasets.
This talk will explain the different characteristics of each spatial index and present some performance comparisons in terms of query speed, overhead on writes, index building time and index size. It will also cover general indexing best practices such as subdividing geometries, [partial indexing](https://wiki.openstreetmap.org/wiki/User:Species/PostGIS_Tuning#Indices) and introduce new concepts such as [covering indexes](https://info.crunchydata.com/blog/why-covering-indexes-are-incredibly-helpful).
