pqxx::stream_to

[mmd-osm]

Follow up for OpenHistoricalMap/issues#783

One of the suggestions in the OHM issue (besides fixing PostgreSQL parameter settings) was to use COPY to load table contents. No doubt, COPY will be faster than the current approach. Library support for COPY was rather limited 5 years ago, stream_to was only added in libpqxx 7.x.x (IIRC). Since we cannot switch to a more recent libpqxx right away, this would introduce a bit of code duplication to support both 6.4.x and 7.8.x.

I tested this idea on a more recent libpqxx 7.8.1 with 1 million table entries (3 bigint columns). Performance improvement was about 20%.

auto cn = pqxx::stream_to::raw_table(work, "current_way_nodes", "way_id, node_id, sequence_id");
...
cn.write_values(way_id, node_id, sequence_id);
...
cn.complete();

Relevant methods:

• ApiDB_Node_Updater

  • insert_new_current_node_tags

• ApiDB_Way_Updater

  • insert_new_current_way_tags
  • insert_new_current_way_nodes

• ApiDB_Relation_Updater

  • insert_new_current_relation_tags
  • insert_new_current_relation_members

[mmd-osm]

Further tests with https://github.com/mmd-osm/openstreetmap-cgimap/tree/feature/stream_to have shown no significant improvement. We'll keep the issue open for the time being, but likely won't use this approach.

[mmd-osm]

@Woazboat : maybe you could review or try out this change, if you can see any significant performance difference. Maybe also check out the recommendation given in OpenHistoricalMap/issues#783 (comment)

As a side note, it would be interesting to try this out on the osm dev server. As the server is still running on Ubuntu 22.04, a 7.8.x libpqxx version isn't available at the moment without additional manual install effort.

[Woazboat]

Sure, I can take a look. I'm not too familiar with SQL optimization but maybe I can find something.

[mmd-osm]

So in the end I merged the PR, after it became clear that some other (unrelated) query was dominating the overall runtime. Unfortunately, there's no zero effort way to test this code on the dev instance, as long as the server is still running on Ubuntu 22.04.