Optimize update of tables with generated columns

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimize update of tables with generated columns

Peter Eisentraut-6
When updating a table row with generated columns, we only need to
recompute those generated columns whose base columns have changed in
this update and keep the rest unchanged.  This can result in a
significant performance benefit (easy to reproduce for example with a
tsvector column).  The required information was already kept in
RangeTblEntry.extraUpdatedCols; we just have to make use of it.

A small problem is that right now ExecSimpleRelationUpdate() does not
populate extraUpdatedCols.  That needs fixing first.  This is also
related to the issue discussed in "logical replication does not fire
per-column triggers"[0].  I'll leave my patch here while that issue is
being resolved.


[0]:
https://www.postgresql.org/message-id/flat/21673e2d-597c-6afe-637e-e8b10425b240%402ndquadrant.com

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

0001-Optimize-update-of-tables-with-generated-columns.patch (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Optimize update of tables with generated columns

Peter Eisentraut-6
On 2019-12-21 07:47, Peter Eisentraut wrote:
> When updating a table row with generated columns, we only need to
> recompute those generated columns whose base columns have changed in
> this update and keep the rest unchanged.  This can result in a
> significant performance benefit (easy to reproduce for example with a
> tsvector column).  The required information was already kept in
> RangeTblEntry.extraUpdatedCols; we just have to make use of it.
>
> A small problem is that right now ExecSimpleRelationUpdate() does not
> populate extraUpdatedCols.  That needs fixing first.

Here is an updated patch set that contains a fix for the issue above
(should be backpatched IMO) and the actual performance patch as before.

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

v2-0001-Fill-in-extraUpdatedCols-in-logical-replication.patch (4K) Download Attachment
v2-0002-Optimize-update-of-tables-with-generated-columns.patch (10K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Optimize update of tables with generated columns

Pavel Stehule


čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut <[hidden email]> napsal:
On 2019-12-21 07:47, Peter Eisentraut wrote:
> When updating a table row with generated columns, we only need to
> recompute those generated columns whose base columns have changed in
> this update and keep the rest unchanged.  This can result in a
> significant performance benefit (easy to reproduce for example with a
> tsvector column).  The required information was already kept in
> RangeTblEntry.extraUpdatedCols; we just have to make use of it.
>
> A small problem is that right now ExecSimpleRelationUpdate() does not
> populate extraUpdatedCols.  That needs fixing first.

Here is an updated patch set that contains a fix for the issue above
(should be backpatched IMO) and the actual performance patch as before.

+ 1

I tested check-world without problems, and changes of patch has sense for me.

Regards

Pavel


--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: Optimize update of tables with generated columns

Peter Eisentraut-6
On 2020-02-13 16:16, Pavel Stehule wrote:

> čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut
> <[hidden email]
> <mailto:[hidden email]>> napsal:
>
>     On 2019-12-21 07:47, Peter Eisentraut wrote:
>      > When updating a table row with generated columns, we only need to
>      > recompute those generated columns whose base columns have changed in
>      > this update and keep the rest unchanged.  This can result in a
>      > significant performance benefit (easy to reproduce for example with a
>      > tsvector column).  The required information was already kept in
>      > RangeTblEntry.extraUpdatedCols; we just have to make use of it.
>      >
>      > A small problem is that right now ExecSimpleRelationUpdate() does not
>      > populate extraUpdatedCols.  That needs fixing first.
>
>     Here is an updated patch set that contains a fix for the issue above
>     (should be backpatched IMO) and the actual performance patch as before.
>
>
> + 1
>
> I tested check-world without problems, and changes of patch has sense
> for me.

committed, thanks

--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services