Up speed flex output with --create

[Arhat109]

Now, I see the work osm2pgsql with --create and flex output into my own tables and have question:
Why osm2pgsql created planet_osm_* tables and are copiing data into both: planet_osm_nodes and my table 'nodes' simultaneously?
This is requires x2 copy-query to postgresql and x2 place for data.

[Arhat109]

Way.. all planet.osm.pbf started writing way'a table! Nodes was writed 10 hours. Now, I see this:

102469 | 07:24:38.128423 | user_admin | COPY "public"."planet_osm_ways" FROM STDIN
 104002 | 00:00:00.000784 | user_admin | SELECT id, lon, lat FROM "public"."planet_osm_nodes" WHERE id = ANY($1::int8[])
 104025 | 00:01:00.250268 | user_admin | COPY "public"."ways" ("id","imported_id","version","user_id","tstamp","changeset_id","tags","linestring","nodes_hash","highway","building") FROM STDIN

It means that SELECT selecting only 1 node and COPY are storing only one way at once too.. brainly. :(

[lonvia]

The SELECT selects on an array of nodes, not a single node. The COPY command stores all ways eventually.

[Arhat109]

Yes, I am mistaked, but inserting 0.12k ways per second is equal "never" for all planet data. )

[mboeringa]

Yes, I am mistaked, but inserting 0.12k ways per second is equal "never" for all planet data. )

Yes, with flat-nodes it will write nodes on to disk, but it needing about 200gb for all planet. I have't this amount on my notebook, and tried this option early.

If you only get 0.12k ways per second, you really should look at both your hardware and PostgreSQL configuration. Based on the second comment, it sounds to me you may be using a secondary HDD instead of NVMe SSD due to lack of space on the probably NVMe SSD based system disk of your laptop.

I get 70k ways/s on 2016 hardware, albeit upgraded with NVMe SSDs.

Alternatively, there may be an issue with some inappropriate or hugely complex Lua Flex style processing for your particular style, that simply makes it slow. With all the flexibility, you can introduce performance problems of your own making.

[lonvia]

To be able to do its processing, osm2pgsql needs a copy of the original OSM data in its raw format. That is what is stored in the "middle tables". They are created on top of your output table. Read more about middle tables in the osm2pgsql documentation.

[Arhat109]

Thanks, but I wrote: Why need copy nodes data into create mode? 700gb disk space for both tables on all planet data.. Why not use postgis from nodes.geom POINT?

[pnorman]

A flex table contains a subset of data, as specified by your lua.

You should generally be using flat nodes if you're loading the entire planet.

[Arhat109]

Yes, with flat-nodes it will write nodes on to disk, but it needing about 200gb for all planet. I have't this amount on my notebook, and tried this option early.
My misunderstanding is related to use planet_osm_* where work with --create, and when in lua are created table define_node_table with geometry field as point. Why not to get this geom field in parsing ways? More than, you may store all ways before in the table and only then add geometry field to it with postgis .. or not?

[pnorman]

Updating requires the raw OSM data. A flex table only has a subset of data.