Re: making update/delete of inheritance trees scale better

On Thu, Mar 25, 2021 at 4:22 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I wrote:
> > Yeah, it's on my to-do list for this CF, but I expect it's going to
> > take some concentrated study and other things keep intruding :-(.
>
> I've started to look at this seriously,

Thanks a lot.

> and I wanted to make a note
> about something that I think we should try to do, but there seems
> little hope of shoehorning it in for v14.
>
> That "something" is that ideally, the ModifyTable node should pass
> only the updated column values to the table AM or FDW, and let that
> lower-level code worry about reconstructing a full tuple by
> re-fetching the unmodified columns. When I first proposed this
> concept, I'd imagined that maybe we could avoid the additional tuple
> read that this implementation requires by combining it with the
> tuple access that a heap UPDATE must do anyway to lock and outdate
> the target tuple. Another example of a possible win is Andres'
> comment upthread

(Ah, I think you mean Heikki's.)

> that a columnar-storage AM would really rather
> deal only with the modified columns. Also, the patch as it stands
> asks FDWs to supply all columns in a whole-row junk var, which is
> something that might become unnecessary.

That would indeed be nice. I had considered taking on the project to
revise FDW local (non-direct) update APIs to deal with being passed
only the values of changed columns, but hit some problems when
implementing that in postgres_fdw that I don't quite remember the
details of. As you say below, we can pick that up later.

> However, there are big stumbling blocks in the way. ModifyTable
> is responsible for applying CHECK constraints, which may require
> looking at the values of not-modified columns. An even bigger
> problem is that per-row triggers currently expect to be given
> the whole proposed row (and to be able to change columns not
> already marked for update). We could imagine redefining the
> trigger APIs to reduce the overhead here, but that's certainly
> not going to happen in time for v14.

Yeah, at least the trigger concerns look like they will take work we
better not do in the 2 weeks left in the v14 cycle.

> So for now I think we have to stick with Amit's design of
> reconstructing the full updated tuple at the outset in
> ModifyTable, and then proceeding pretty much as updates
> have done in the past. But there's more we can do later.

Agreed.

I'm addressing Robert's comments and will post an updated patch by tomorrow.

--
Amit Langote
EDB: http://www.enterprisedb.com