Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Rewrite batch update into "update from" query #888

Open
vlsi opened this issue Jul 27, 2017 · 4 comments
Open

Rewrite batch update into "update from" query #888

vlsi opened this issue Jul 27, 2017 · 4 comments

Comments

@vlsi
Copy link
Member

vlsi commented Jul 27, 2017

For instance, TechEmpower has special case for batch update.

The query is as follows (see https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/frameworks/Java/servlet/src/main/java/hello/PostgresUpdateServlet.java#L26 ):

UPDATE World SET randomNumber = ? WHERE id = ?

The query is executed in a batch like

UPDATE World SET randomNumber = ? WHERE id = ?
UPDATE World SET randomNumber = ? WHERE id = ?
UPDATE World SET randomNumber = ? WHERE id = ?
...

It might be a nice (crazy?) idea to rewrite the query as follows:

UPDATE World SET randomNumber = v.c1 FROM (VALUES(?,?),(?,?),...) as v(c1, c2) WHERE id = v.c2

That would reduce the number of wire messages, and improve the performance of the batch update.

@davecramer
Copy link
Member

You want to open this can of worms ????

@vlsi
Copy link
Member Author

vlsi commented Jul 28, 2017

Why not? =)

PS. This batch seems to be common in ORMs, so it might be a great win.

vlsi added a commit to vlsi/pgjdbc that referenced this issue Jul 28, 2017
It looks like UPDATE ... FROM (VALUES...) makes sense (see (batch_sort-get_worlds) vs (update_from_values-get_worlds))

see pgjdbc#888
see TechEmpower/FrameworkBenchmarks#2684
vlsi added a commit to pgjdbc/benchmarks that referenced this issue Jun 30, 2018
It looks like UPDATE ... FROM (VALUES...) makes sense (see (batch_sort-get_worlds) vs (update_from_values-get_worlds))

see pgjdbc/pgjdbc#888
see TechEmpower/FrameworkBenchmarks#2684
@idkw
Copy link

idkw commented Jun 18, 2020

I'm using the Hibernate ORM and it doesn't write actual insert on update batched statements (with multiple values), I can only instruct it to order identical statements to let the pgjdbc rewrite them when reWriteBatchedInserts=true is configured

I would really love if there was a similar feature for update statements like described in the original message of this issue.

Which is to say a feature to rewrite consecutive statements :

UPDATE World SET randomNumber = ? WHERE id = ?
UPDATE World SET randomNumber = ? WHERE id = ?
UPDATE World SET randomNumber = ? WHERE id = ?

into a single statement:

UPDATE World SET randomNumber = v.c1 FROM (VALUES(?,?),(?,?),...) as v(c1, c2) WHERE id = v.c2

@davecramer Initially, regarding this feature request you said a few years ago :

You want to open this can of worms ????

Do you have any insights to share like a simple counter example where it would not be generally possible to implement such a feature reliably ? I'm trying to understand what would be the major pain points if there are any.

Thanks a lot !

@davecramer
Copy link
Member

@idkw seems doable with the example above. Nobody has found any cycles to do it.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants