Hi All,
I was having great difficulty with applying the daily diffs to my
PostgreSQL database (8.4 and 9.0). I would get good performance right
after a vacuum, but after a few hours of updates, it would be running at
less than real-time. After a great deal of experimentation, I found that
the GIN indexes on the ways and rels tables were the cause.
In PostgreSQL 8.4, the "fastupdate" feature was introduced for GIN
indexes:
http://www.postgresql.org/docs/8.4/static/release-8-4.html
This feature is on by default and delays updates of GIN indexes until
the next vacuum, speeding individual index updates by adding them to a
temporary space.
I found that this causes slowdowns after a few hours of diffs. This is
likely due to each subsequent index reads/updates having to read through
the entire temporary update space.
If the index is created with "with (fastupdate=off)", applying diffs is
much faster overall, and stable in terms of performance from one diff
application to another.
Attached is a patch to osm2pgsql's middle-pgsql.c that adds the above
term to the GIN index creation statements.
This command WILL FAIL if used on PostgreSQL databases earlier than 8.4.
Thanks!
-Erik Burrows
Index: default.style
===================================================================
--- default.style (revision 25113)
+++ default.style (working copy)
@@ -81,7 +81,20 @@
node,way wood text linear
node,way z_order int4 linear # This is calculated during import
way way_area real # This is calculated during import
-
+node,way tiger:reviewed text linear
+node,way piste:type text linear
+node,way piste:status text linear
+node,way piste:oneway text linear
+node,way piste:certifications text linear
+node,way piste:lit text linear
+node,way piste:abandoned text linear
+node,way gladed text linear
+node,way patrolled text linear
+node,way piste:lift text linear
+node,way piste:priority text linear
+node,way piste:difficulty text linear
+node,way piste:grooming text linear
+node,way piste:lift:occupancy text linear
# If you're interested in bicycle routes, you may want the following fields
# To make these work you need slim mode or the necessary data won't be remembered.
#way lcn_ref text linear
Index: middle-pgsql.c
===================================================================
--- middle-pgsql.c (revision 25113)
+++ middle-pgsql.c (working copy)
@@ -86,7 +86,7 @@
.start = "BEGIN;\n",
.create = "CREATE TABLE %s_ways (id int4 PRIMARY KEY, nodes int4[] not null, tags text[], pending boolean not null);\n",
.create_index = "CREATE INDEX %s_ways_idx ON %s_ways (id) TABLESPACE %s WHERE pending;\n",
-.array_indexes = "CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) TABLESPACE %s;\n",
+.array_indexes = "CREATE INDEX %s_ways_nodes ON %s_ways USING gin (nodes gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n",
.prepare = "PREPARE insert_way (int4, int4[], text[], boolean) AS INSERT INTO %s_ways VALUES ($1,$2,$3,$4);\n"
"PREPARE get_way (int4) AS SELECT nodes, tags, array_upper(nodes,1) FROM %s_ways WHERE id = $1;\n"
"PREPARE way_done(int4) AS UPDATE %s_ways SET pending = false WHERE id = $1;\n"
@@ -103,7 +103,7 @@
.start = "BEGIN;\n",
.create = "CREATE TABLE %s_rels(id int4 PRIMARY KEY, way_off int2, rel_off int2, parts int4[], members text[], tags text[], pending boolean not null);\n",
.create_index = "CREATE INDEX %s_rels_idx ON %s_rels (id) TABLESPACE %s WHERE pending;\n",
-.array_indexes = "CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) TABLESPACE %s;\n",
+.array_indexes = "CREATE INDEX %s_rels_parts ON %s_rels USING gin (parts gin__int_ops) WITH (FASTUPDATE=OFF) TABLESPACE %s;\n",
.prepare = "PREPARE insert_rel (int4, int2, int2, int[], text[], text[]) AS INSERT INTO %s_rels VALUES ($1,$2,$3,$4,$5,$6,false);\n"
"PREPARE get_rel (int4) AS SELECT members, tags, array_upper(members,1)/2 FROM %s_rels WHERE id = $1;\n"
"PREPARE rel_done(int4) AS UPDATE %s_rels SET pending = false WHERE id = $1;\n"
_______________________________________________
dev mailing list
[email protected]
http://lists.openstreetmap.org/listinfo/dev
