I am using libpqxx to communicate with a PostgreSQL 9.3 server.
For multiple table inserts, if they are split into individual prepared statements, they cost approximately 2ms each. If they are all wrapped up into a single CTE, the entire prepared statement costs approximately 2ms.
In fact, nearly all individual prepared statements cost approximately 2ms, 1ms for unlogged tables.
What explains this exactly?
I first broke down my most expensive set of 14 prepared statements into two CTEs to avoid the single row write rule.
This dropped the average time consumed from below 40ms to approximately 10ms.
To avoid the single row write rule, I tried a function with input parameters. This was a disaster despite its convenience, driving time consumed to approximately 500ms.
I just completed the single CTE, and I have found no difference between using two CTEs in halves and the single, but I think there may be some caveats.
To merge the two CTEs, I had to work a variant of this solution because of how I'm moving data between tables, copying from one table to another, deleting that source data, and rebuilding it.
Keys that are not present in the new data are deleted, coexisting ones are updated if they have new data, and keys not present in the old data are inserted, converting a delete+insert into a delete+update+insert.
This prepared CTE is now almost 25 queries long vs the original 14; however, very few rows are being moved. In the future, many more rows should be moved.
It seems strange that the single CTE would consume as much time as the two-CTE since all queries are running more or less concurrently, and less rows are being altered. My guess is that since this CTE is much larger, it's taking more trials for the server to come up with an optimal query plan.
Single CTE now only consumes 5ms on average
As I ran the query more, the average started dropping. It's currently dropped to a 5ms average. This is on an i7 laptop. I still haven't ran the query that many times, so there may be room for more improvement.
Since I have no knowledge of the PostgreSQL source code and can't determine how much of the performance was due to roundtrips between database and application despite them being on the same box, my best guess is that nearly all of the performance increase is due to the efficiency of PostgreSQL CTEs, considering the default concurrent execution of the sub-statements and main query.
Hopefully, an expert can give a full explanation, but as for me and for performance, I will try to cram every process into a single CTE.