Thread: 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
Attachment
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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