Re: making update/delete of inheritance trees scale better

Amit Langote <amitlangote09(at)gmail(dot)com> writes:
> On Fri, Mar 26, 2021 at 3:07 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> I think the reason is that the parser
>> initially builds all INSERT ... SELECT cases with the SELECT as an
>> explicit subquery, giving rise to a SubqueryScan node just below the
>> ModifyTable, which will project exactly the desired columns and no more.
>> We'll optimize away the SubqueryScan if it's a no-op projection, but not
>> if it is getting rid of junk columns. There is room for more optimization
>> here: dropping the SubqueryScan in favor of making ModifyTable do the same
>> projection would win by removing one plan node's worth of overhead.

> Oh, so there could possibly be a case where ModifyTable would have to
> do junk filtering for INSERTs, but IIUC only if the planner optimized
> away junk-filtering-SubqueryScan nodes too? I was thinking that
> perhaps INSERTs used to need junk filtering in the past but no longer
> and now it's just dead code.

I'm honestly not very sure about that. It's possible that there was
some state of the code in which we supported INSERT/SELECT but didn't
end up putting a SubqueryScan node in there, but if so it was a long
long time ago. It looks like the SELECT-is-a-subquery parser logic
dates to 05e3d0ee8 of 2000-10-05, which was a long time before
ModifyTable existed as such. I'm not interested enough to dig
further than that.

However, it's definitely true that we can now generate INSERT plans
where there's a SubqueryScan node that's not doing anything but
stripping junk columns, for instance

INSERT INTO t SELECT x FROM t2 ORDER BY y;

where the ORDER BY has to be done with an explicit sort. The
sort will be on a resjunk "y" column.

regards, tom lane