| Lists: | pgsql-hackers |
|---|
| From: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Optimize update of tables with generated columns |
| Date: | 2019-12-21 06:47:29 |
| Message-ID: | b05e781a-fa16-6b52-6738-761181204567@2ndquadrant.com |
| Views: | Whole Thread |Raw Message |Download mbox |Resend email |
| Lists: | pgsql-hackers |
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 Eisentrauthttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Optimize-update-of-tables-with-generated-columns.patch | text/plain | 7.4 KB |
| From: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Optimize update of tables with generated columns |
| Date: | 2020-02-13 13:39:41 |
| Message-ID: | 65fb745d-4c1a-7582-d61b-1686d8c5c85d@2ndquadrant.com |
| Views: | Whole Thread |Raw Message |Download mbox |Resend email |
| Lists: | pgsql-hackers |
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 Eisentrauthttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
| Attachment | Content-Type | Size |
|---|---|---|
| v2-0001-Fill-in-extraUpdatedCols-in-logical-replication.patch | text/plain | 3.1 KB |
| v2-0002-Optimize-update-of-tables-with-generated-columns.patch | text/plain | 7.3 KB |
| From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
|---|---|
| To: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
| Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Optimize update of tables with generated columns |
| Date: | 2020-02-13 15:16:46 |
| Message-ID: | CAFj8pRA417+4wrHMSXLY_D==Ca7fRd0kNHOT6DaLc906AyKiJg@mail.gmail.com |
| Views: | Whole Thread |Raw Message |Download mbox |Resend email |
| Lists: | pgsql-hackers |
čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)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 Eisentrauthttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
| From: | Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> |
|---|---|
| To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
| Cc: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Subject: | Re: Optimize update of tables with generated columns |
| Date: | 2020-02-17 15:16:40 |
| Message-ID: | f7f78f54-d984-da56-0df1-963cc2d2e736@2ndquadrant.com |
| Views: | Whole Thread |Raw Message |Download mbox |Resend email |
| Lists: | pgsql-hackers |
On 2020-02-13 16:16, Pavel Stehule wrote:
> čt 13. 2. 2020 v 14:40 odesílatel Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com
> <mailto:peter(dot)eisentraut(at)2ndquadrant(dot)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 Eisentrauthttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services