Ensure Postgres maps have proper indexes
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Yurik
	Jun 21 2016, 3:59 AM

Description

The index sql file is being maintained at https://github.com/kartotherian/osm-bright.tm2source/blob/master/sql/create-indexes.sql

Comparing maps-test2001 and maps2001 showed these index differences:

maps-test2001

Yuri added this index for the geoshape featuer T134084 - needs to be added

CREATE INDEX planet_osm_polygon_wikidata ON planet_osm_polygon USING btree (((tags -> 'wikidata'::text))) WHERE (tags ? 'wikidata'::text);

@MaxSem, is this index needed?

CREATE INDEX planet_osm_rels_idx ON planet_osm_rels USING btree (id) WHERE pending;

@MaxSem, is this index needed?

CREATE INDEX planet_osm_ways_idx ON planet_osm_ways USING btree (id) WHERE pending;

maps2001

This index exists on the new servers, but it appears to be almost an exact duplicate of the other index that exists on both:

CREATE INDEX water_polygons_index ON water_polygons USING gist (way) WITH (fillfactor='100');
-- exists on both:
CREATE INDEX water_polygons_way_idx ON water_polygons USING gist (way);

Related Objects
Search...

		Status	Subtype	Assigned	Task
		Declined		None	T137616 Epic: cultivating the Maps garden
		Resolved		Gehel	T138277 Ensure Postgres maps have proper indexes

Event Timeline

Yurik created this task.Jun 21 2016, 3:59 AM

Yurik updated the task description. (Show Details)Jun 21 2016, 4:07 AM

@Pnorman on IRC posted links for osm2pgsql-related DBs script and a config for it, which

allows indexes to be cleanly defined and then you can generate index statements with fillfactor set, concurrently, rebuild them, etc
the pgsql backend schema is documented at https://github.com/openstreetmap/osm2pgsql/blob/master/docs/pgsql.md

@Pnorman commented on maps-l list:

In addition to what tilerator requires, most styles will benefit from custom partial indexes which should be specified by the style.

You want to consider the need to issue statements to

Create indexes with fillfactor 100 for imports that are not updated, e.g.

CREATE INDEX IF NOT EXISTS planet_osm_roads_admin
  ON planet_osm_roads USING GIST (way)
  WITH (fillfactor=100)
  WHERE boundary = 'administrative';

Create indexes concurrently e.g.

CREATE INDEX CONCURRENTLY planet_osm_roads_admin
  ON planet_osm_roads USING GIST (way)
  WHERE boundary = 'administrative';

Reindex e.g.

REINDEX planet_osm_roads_admin;

Reindex concurrently e.g.

ALTER INDEX planet_osm_roads_admin RENAME TO planet_osm_roads_admin_old;
CREATE INDEX CONCURRENTLY planet_osm_roads_admin
  ON planet_osm_roads USING GIST (way)
  WHERE boundary = 'administrative';
DROP INDEX planet_osm_roads_admin_old;

All of these are tasks which will need to be done by developers, when setting up new databases, or periodically as part of normal maintenance.

planet_osm_polygon_wikidata has been manually created (took about 3-4 minutes)

Yurik moved this task from All map-related tasks to Maps-data on the Maps board.Jun 26 2016, 8:18 PM

Yurik added a project: Maps (Maps-data).Jun 26 2016, 8:24 PM

All known required indexes have been created. DDL for those indexes is versionned and scripted. This issue is resolved.

Ensure Postgres maps have proper indexesClosed, ResolvedPublicActions