question about execute_values performance

[jerch]

I am trying to use execute_values to do an UPDATE FROM VALUES like update. I wonder if I am using it wrong, as it performs worse than multiple plain execute sql invocations with own batching beforehand.

Here are some runtime perf numbers:

• 100k records to be updated at one integer column
• table itself has only one index on pk column
• sql: UPDATE table SET intfield=d.intfield FROM (VALUES %s) AS d (pk, intfield) WHERE table.pk=d.pk
• each batch size tested in 10 runs, results are min(runs)

batch size	own batching	execute_values
32	24.20	23.72
64	13.24	13.03
128	7.57	8.21
256	4.37	4.81
512	3.54	3.55
1024	2.75	3.74
2048	2.08	2.67
4096	1.98	2.59
8192	1.73	1.86
16384	1.49	1.76

My own batching simply does multiple execute invocations with values fully laid out (like (VALUES (%s, %s), ...)). It does not use any additional prepare statement trickery, its just a bunch of equally sized execute commands and a last smaller one.

So my questions are:

• Is there an obvious reason, why execute_values would run slower than a bunch of execute commands? I assume, that the small batches run faster with execute_values due to the query planner overhead getting more dominant. But why does execute_values fall behind after that?
• "You are holding it wrong"? Maybe, I simply put the batch size in the page_size argument.
• What are the benefits of execute_values, if it cannot compete with a bunch of handcrafted batches? I see that the memory usage stays lower with execute_values (due to respecting postgres buffer limits?), but for my rather simple test case that was like 256MB vs. 160MB on the postgres process (not really tracked that, it's what I saw on top output).

Edit:
This was tested with:

• psycopg2 2.9.3
• postgres 14

[dvarrazzo]

If you look at execute_batch code, it doesn't do much different than what you do. I don't know if the difference is in the client crafting the batches, in the time to send them over the network, or the time for the server to apply them: there is too much variability in that.

Maybe we are doing something silly, or maybe we are doing something more generic than you are, because I assume that, if you are only handling numbers, you are not taking all the care we need to take with users possibly trying to send values to escape.

You are in a much better position than us to profile the difference between your code and ours (which is just a handful of python statements, no more than that)) and you might tell us if we can do anything better.

[jerch]

You are in a much better position than us to profile the difference between your code and ours (which is just a handful of python statements, no more than that)) and you might tell us if we can do anything better.

True, will try to get more profiling insights (will take some time).

Maybe we are doing something silly, or maybe we are doing something more generic than you are, because I assume that, if you are only handling numbers, you are not taking all the care we need to take with users possibly trying to send values to escape.

Oh well, yes the value escaping might be it. I currently do the escaping/cast on the SET field=d.field::... part, which might not be safe for all circumstances? (Edit: Tested that beforehand with json and arrays, which seems to work...)

[dvarrazzo]

If you know the data you are passing to the server, even f"set x ={x}" is ok, if you know that x is a number. Our position must be more generic than that.

This also assumes that the difference is in the time spent on the client, rather than elsewhere, such as giving different interpretations to the limits and sending larger batches. There might be stuff we could learn out of your experience :)

Cheers

[jerch]

Oh well, found the culprit, its not execute_values at all, but the way I pull the cursor from Django. Django's standard cursor is some enhanced proxy thingy, that logs called sql statements, which seems to be expensive in the execute_values case (not sure yet why). With using the real cursor object provided by psycopg2, things are equal (only +- 5% runtime difference with no clear trend).

So yes, its all client related with hidden goodies slowing things down. Sorry for bothering you on Sunday ❤️

[dvarrazzo]

No worries, thank you for checking this out with us! Have a good weekend!