Обсуждение: Optimize update of tables with generated columns
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
Вложения
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
Вложения
č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
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