[Tile-serving] [openstreetmap/osm2pgsql] Allow flex backend to specify additional indexes to create (Issue #1691)

[Paul Norman]

Many transforms need additional indexes to be used. Currently this is commonly done as a .sql file distributed alongside the transforms, but this is not ideal for a few reasons

1. It is an extra process that might be forgotten
2. The create index statements will need to vary with the FILLFACTOR depending on if the database is updatable or not
3. The naïve way of running `osm2pgsql && psql -f indexes.sql` will lead to a period of time when only one index is being built. This is especially bad for slim imports with the way node index.
4. Index builds should be run in parallel, but `psql -f` can't handle this, requiring more elaborate SQL runners
5. VACUUM ANALYZE should be run after index building, not before

Instead, the index builds should be run, in parallel, for each table once the table has been sorted

With the flex backend it should be possible to specify multiple indexes for each table. It needs to be possible to specify the name (e.g. `admin`), index type (e.g. `GIST`), columns or expression (e.g. `ST_PointOnSurface(way)`), and partial index predicate (e.g. `name IS NOT NULL AND boundary = 'administrative' AND admin_level IN ('0', '1', '2', '3', '4')`). osm2pgsql would supply the `storage_parameter` based on if it is updatable and tablespace_name, if a tablespace is set.



-- 
Reply to this email directly or view it on GitHub:
https://github.com/openstreetmap/osm2pgsql/issues/1691
You are receiving this because you are subscribed to this thread.

Message ID: <openstreetmap/osm2pgsql/issues/1691 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20220709/53f38088/attachment.htm>