Corrected patch attached (without my custom change to default.style).
> 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
> _______________________________________________
> dev mailing list
> [email protected]
> http://lists.openstreetmap.org/listinfo/dev
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
