[OSM-dev] Postgres 8.4/PostGIS 1.4 performance

Martijn van Oosterhout kleptog at gmail.com
Sun Sep 20 18:11:38 BST 2009

On Sun, Sep 20, 2009 at 6:16 PM, Lars Francke <lars.francke at gmail.com> wrote:
>> 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.

Which it does, but ANALYZE is almost independant of the size of the
table. Certainly much faster than vacuum.

>> 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]:

Sorry, I should have been clearer:

2. *Having osm2pgsql do* a vacuum after a diff is also useless, etc

> 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.

Ofcourse, you need to do one eventually, but it's not a choice
osm2pgsql can make because it cannot know whether one is needed. It is
not approriate to do one all the time because of the time it takes.
The builtin autovacuum will trigger one appropriately (at least when
about 5% of rows have changed). If you decide to disable that then you
are responsible for your own vacuuming (don't forget the catalogs!).
But in all cases it's not osm2pgsql's responsibility.

>> 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.

Again, autovacuum does this, otherwise it's your responsibility.
osm2pgsql is not responsible here.

Have a nice day,
Martijn van Oosterhout <kleptog at gmail.com> http://svana.org/kleptog/

More information about the dev mailing list