[Tile-serving] [osm2pgsql-dev/osm2pgsql] Some research on middle performance (Issue #2110)

[Paul Norman]

> * From the numbers here it seems to be significantly faster to create the table without the primary key constraint and add that later. But in another test where I also generated output tables, this did not make a difference. The reason is probably that osm2pgsql was busy so often doing other things, that PostgreSQL had the time to update the indexes while the import ran. So it might be possible to get some improvement here in a real situation, but it is not quite as clear-cut as the numbers here suggest.

We should move to creating the UNIQUE index after loading the data. It might not have sped up your test, but I believe it would on some hardware with a different number of threads. Additionally, the resulting index is properly balanced without dead tuples in it.

> * It looks like using COPY FREEZE can improve the performance. For this to work we have to create the table in the same transaction as we do the COPY. This is not easily possible with the current code, but it is a change we could do.

This would rule out ever having multiple threads writing to the middle at the same time. Do we want to do that?

-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2110#issuecomment-1842569478
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/issues/2110/1842569478 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231206/49598dad/attachment.htm>