Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Allow flex backend to specify additional indexes to create #1691

Closed
pnorman opened this issue Jul 9, 2022 · 2 comments
Closed

Allow flex backend to specify additional indexes to create #1691

pnorman opened this issue Jul 9, 2022 · 2 comments
Labels
flex Issues about the flex output

Comments

@pnorman
Copy link
Collaborator

pnorman commented Jul 9, 2022

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.

@pnorman pnorman added the flex Issues about the flex output label Jul 9, 2022
@mmd-osm
Copy link
Contributor

mmd-osm commented Jul 10, 2022

FYI: we've been discussing this use case as part of a more generic post processing some time ago: #1211 (comment)

@joto
Copy link
Collaborator

joto commented Dec 7, 2022

It is now possible to create any kind of indexes you want with the flex output. See https://osm2pgsql.org/doc/manual.html#defining-indexes for details.

@joto joto closed this as completed Dec 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
flex Issues about the flex output
Projects
None yet
Development

No branches or pull requests

3 participants