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 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 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 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 and introduce new concepts such as covering indexes.