Postgres and multiple updates in one statement
Hi there
We have a function which runs a set of update clauses and we are considering
putting all the update clauses into one statement.
I would like to understand how postgres handles multiple updates. If we were
to send 5 update statements in one sql statement to the db would it:
Do 5 passes on the table, on each pass, retrieve the id then update the row
Do 1 pass to retrieve the 5 primary keys, then update all rows in parallel
Apart from saving on the overhead of having to open up 5 separate
connections, what are the benefits to passing in multiple updates in one
statement?
Many thanks,
Jamie
On 27/07/2009 09:10, Jamie Lawrence-Jenner wrote:
Apart from saving on the overhead of having to open up 5 separate
connections, what are the benefits to passing in multiple updates in one
statement?
If you do them all within one transaction -
begin;
update....
update...
...
commit;
- then you save on the overhead associated with beginning and committing
a transaction for each update.
Ray.
------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
On Mon, Jul 27, 2009 at 2:10 AM, Jamie
Lawrence-Jenner<jamie.jenner@autovhc.co.uk> wrote:
Hi there
We have a function which runs a set of update clauses and we are considering
putting all the update clauses into one statement.I would like to understand how postgres handles multiple updates. If we were
to send 5 update statements in one sql statement to the db would it:Do 5 passes on the table, on each pass, retrieve the id then update the row
Do 1 pass to retrieve the 5 primary keys, then update all rows in parallel
I would do 5 passes. Better to have one update statement to reduce bloat.
Hello,
Le 27/07/09 15:07, Raymond O'Donnell a �crit :
On 27/07/2009 09:10, Jamie Lawrence-Jenner wrote:
Apart from saving on the overhead of having to open up 5 separate
connections, what are the benefits to passing in multiple updates in one
statement?If you do them all within one transaction -
begin;
update....
update...
...
commit;- then you save on the overhead associated with beginning and committing
a transaction for each update.
Next to the transaction way suggested by Raymond O'Donnell, I would add
that performance would depend on FROM and WHERE clauses specified in the
original UPDATE statements.
In the case of multiple UPDATE statements following a quite "similar"
schema (ie. similar FROM (optional) and WHERE clauses), it might be
clearer (for the source code) and faster (for the database engine) to
merge them in a single UPDATE statement. Otherwise (ie. UPDATE
statements with not so much FROM and/or WHERE clauses in common),
transaction 1-block statement as suggested by Raymond O'Donnell would
certainly be the more appropriate.
Improvements on the merging UPDATE statements may thence be advised if
some pieces of original statements could be given--without compromising
confidential data.
Regards.
--
nha / Lyon / France.
On Mon, Jul 27, 2009 at 07:31:37AM -0600, Scott Marlowe wrote:
On Mon, Jul 27, 2009 at 2:10 AM, Jamie
Lawrence-Jenner<jamie.jenner@autovhc.co.uk> wrote:Do 1 pass to retrieve the 5 primary keys, then update all rows in parallel
I would do 5 passes. Better to have one update statement to reduce bloat.
You could possibly use UNION or UNION ALL to consolidate your 5 passes into
one pass. You could also possibly use UPDATE FROM to avoid having to return
the primary keys at all, and get the whole thing done in one query.
--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com
Hi There
Our update statements are as follows
Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;
Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.
Many thanks
Jamie
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of nha
Sent: 27 July 2009 14:35
To: Jamie Lawrence-Jenner
Cc: PgSQL-general
Subject: Re: [GENERAL] Postgres and multiple updates in one statement
Hello,
Le 27/07/09 15:07, Raymond O'Donnell a écrit :
On 27/07/2009 09:10, Jamie Lawrence-Jenner wrote:
Apart from saving on the overhead of having to open up 5 separate
connections, what are the benefits to passing in multiple updates in one
statement?If you do them all within one transaction -
begin;
update....
update...
...
commit;- then you save on the overhead associated with beginning and committing
a transaction for each update.
Next to the transaction way suggested by Raymond O'Donnell, I would add
that performance would depend on FROM and WHERE clauses specified in the
original UPDATE statements.
In the case of multiple UPDATE statements following a quite "similar"
schema (ie. similar FROM (optional) and WHERE clauses), it might be
clearer (for the source code) and faster (for the database engine) to
merge them in a single UPDATE statement. Otherwise (ie. UPDATE
statements with not so much FROM and/or WHERE clauses in common),
transaction 1-block statement as suggested by Raymond O'Donnell would
certainly be the more appropriate.
Improvements on the merging UPDATE statements may thence be advised if
some pieces of original statements could be given--without compromising
confidential data.
Regards.
--
nha / Lyon / France.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hello again,
Le 27/07/09 16:48, Jamie Lawrence-Jenner a �crit :
Hi There
Our update statements are as follows
Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.Many thanks
Jamie
[...]
Improvements on the merging UPDATE statements may thence be advised if
some pieces of original statements could be given--without compromising
confidential data.
Thanks for the examples.
Comparison between "explain analyze" outputs from the two ways (multiple
statements vs. one statement) should help choosing the faster.
For the one-statement schema, the rewritten query could be:
UPDATE yTable SET col1=x, col2=y WHERE pkID IN (1, 2, 3);
Lists of pkID would rather be expressed in terms of enumeration when in
WHERE clause. In a more general situation, I would recommand to
determine pkIDs list before building UPDATE statement(s) if possible.
This hint would surely save runtime.
With regards.
--
nha / Lyon / France.
On Mon, Jul 27, 2009 at 8:48 AM, Jamie
Lawrence-Jenner<jamie.jenner@autovhc.co.uk> wrote:
Hi There
Our update statements are as follows
Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.
Might as well fire them each as separate statements inside one
transaction, since pkid is unique and non-repeated in the updates.
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Mon, Jul 27, 2009 at 8:48 AM, Jamie
Lawrence-Jenner<jamie.jenner@autovhc.co.uk> wrote:Our update statements are �as follows
Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.
Might as well fire them each as separate statements inside one
transaction, since pkid is unique and non-repeated in the updates.
If they're all exactly the same pattern like that, it might be worth the
trouble to set up a prepared statement.
regards, tom lane
On Mon, 2009-07-27 at 11:50 -0400, Tom Lane wrote:
Scott Marlowe <scott.marlowe@gmail.com> writes:
On Mon, Jul 27, 2009 at 8:48 AM, Jamie
Lawrence-Jenner<jamie.jenner@autovhc.co.uk> wrote:Our update statements are as follows
Update table set col1=x,col2=y where pkid=1;
Update table set col1=x,col2=y where pkid=2;
Update table set col1=x,col2=y where pkid=3;Very simple and straight forward. Sometimes there could be as many as 50
update statements to process.Might as well fire them each as separate statements inside one
transaction, since pkid is unique and non-repeated in the updates.If they're all exactly the same pattern like that, it might be worth the
trouble to set up a prepared statement.
Seems like an opportunity for the use of a function.
Joshua D. Drake
regards, tom lane
--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997