[Tile-serving] [osm2pgsql-dev/osm2pgsql] Daylight Ingest and Slow Deletions (Discussion #2088)

[pyoung-sc]

Hello!  

I've been running an ingest of the Daylight v1.30 release (including a buildings sidecar that has been merged into the planet file )which has a ton more buildings in it than previous releases.

>From what I can tell, the data has been inserted into postgres, but when I look at what queries are still going, I see a handful of idle queries like this
```
 pid  |        duration        |                                            query                                             | state
------+------------------------+----------------------------------------------------------------------------------------------+-------
 3252 | 3 days 06:06:02.449547 | ANALYZE "planet_osm_rels"                                                                    | idle
 3256 | 5 days 04:29:04.091938 | PREPARE get_wkb(bigint) AS SELECT "way" FROM "public"."planet_osm_point" WHERE "osm_id" = $1 | idle
 3257 | 3 days 06:05:56.190388 | CREATE INDEX ON "public"."planet_osm_line" USING BTREE (osm_id)                              | idle
 3258 | 3 days 04:45:54.122027 | CREATE INDEX ON "public"."planet_osm_polygon" USING BTREE (osm_id)                           | idle
 3259 | 3 days 04:26:22.868305 | CREATE INDEX ON "public"."planet_osm_roads" USING BTREE (osm_id)                             | idle
 ```

and these slow running deletes 
```
3255 | 00:30:10.531937        | DELETE FROM "public"."planet_osm_line" WHERE osm_id IN (1229976490,1229977130,...)
```
These delete queries change as time goes on, but each one takes quite a while to finish, which I presume is holding up the idle queries from above.

**I was curious if anyone knew what part of the workflow makes these deletes get issued, and if there's something to be done to speed things up?**  

The call to osm2pgsql looks like
```
osm2pgsql --slim --hstore-all -C 61440 -S osm2pgsql.lua -O flex  --flat-nodes flat.nodes --number-processes 16 ...
```  

where the lua file is from [tilezen](https://github.com/tilezen/vector-datasource/blob/master/osm2pgsql.lua).

Many thanks for any info/suggestions!


-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/discussions/2088
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/repo-discussions/2088 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231002/86ca4b04/attachment.htm>