Horton

Thread

Commits

  1. Fill in extraUpdatedCols in logical replication

  2. Optimize update of tables with generated columns

  1. Optimize update of tables with generated columns

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2019-12-21T06:47:29Z

    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

  2. Re: Optimize update of tables with generated columns

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-02-13T13:39:41Z

    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

  3. Re: Optimize update of tables with generated columns

    Pavel Stehule <pavel.stehule@gmail.com> — 2020-02-13T15:16:46Z

    čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> 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
>

  4. Re: Optimize update of tables with generated columns

    Peter Eisentraut <peter.eisentraut@2ndquadrant.com> — 2020-02-17T15:16:40Z

    On 2020-02-13 16:16, Pavel Stehule wrote:
> čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut 
> <peter.eisentraut@2ndquadrant.com 
> <mailto:peter.eisentraut@2ndquadrant.com>> 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