Figure out a way to use fastupdate on indexes about osm2pgsql HOT 5 OPEN

What if you lower work_mem just in the session that is doing the updates?

from osm2pgsql.

What if you lower work_mem just in the session that is doing the updates?

I'm not sure - if there's any sorts or bitmap scans being done, it'll have negative impacts there.

from osm2pgsql.

Do you have a specific use case in mind? e.g. an example command line to use?

There is a proposed change in PostgreSQL to allow the fast update pending list to be set separately from work_mem (https://commitfest.postgresql.org/action/patch_view?id=1536). I tried testing it with various settings that I thought were reasonable, but I couldn't find meaningful improvement, because it seems like the pending list wasn't much of a bottleneck.

But I only tried initial loads, not incremental loads, because that is what I am familiar with.

from osm2pgsql.

Do you have a specific use case in mind?

The current reason we can't use fastupdate is that with the high work_mem required on a rendering server the pending-entry list grows very large, and updates become extremely slow because scanning a 128MB pending-entry list is slow.

Ideally, we'd keep the pending list small, and after an osm2pgsql command has completed, force it to process the pending list, even if it's small.

This won't impact imports at all, because the GIN index isn't created until the very end and no updates occur to that table after the index is created. That column also isn't used in importing at all, just populated.

e.g. an example command line to use?

Just normal slim mode updates.

from osm2pgsql.

Ideally, we'd keep the pending list small, and after an osm2pgsql command has completed, force it to process the pending list, even if it's small.

And with 9.6 we will have a way to do this!

My thoughts are

• Detect PostgreSQL version
• On 9.6 create GIN indexes with fastupdate on
• On 9.6 when finishing processing during updates run gin_clean_pending_list

If running on a DB created by an earlier osm2pgsql version (with fastupdate=off indexes) this would still work because gin_clean_pending_list can still be safely called.

The migration is also easy.

from osm2pgsql.