[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

Lars Francke lars.francke at gmail.com
Sun Sep 20 17:16:32 BST 2009


> 1. Doing a vacuum immediately after the import isn't useful, there is
> nothing to vacuum by definition.

While this is true an ANALYZE should still be done to initialize the statistics.

> 2. Doing a vacuum after a diff is also useless, since the vacuum will
> probably takes much much longer than applying the diff. (This I noted
> when appliyng an empty diff took minutes on my machine). If you leave
> it in you won't be able to apply diffs fast enough.

I will have to disagree here (and agree with Jochen):
>From the documentation[1]:
> 1. To recover or reuse disk space occupied by updated or deleted rows.
Which in my opinion _only_ makes sense after a diff as it is the only
time anything is updated or deleted. Depending on the type of diffs
(minute, hour, daily) of course. I import the daily diffs and schedule
a VACUUM ANALYZE after each import.

> 2. To update data statistics used by the PostgreSQL query planner.
Very important, too, depending on the type of queries you run. For me
it is a lot of work on the tags tables.
On a related note: There was a change between PostgreSQL 8.3 and 8.4
which increased the default_statistics_target from 10 to 100 and its
maximum from 1.000 to 10.000 which makes ANALYZE runs take longer but
at least for me it helps a lot.

Cheers,
Lars

[1]: http://www.postgresql.org/docs/8.4/static/routine-vacuuming.html




More information about the dev mailing list