Re: Run-time pruning for ModifyTable

On Tue, 10 Mar 2020 at 00:13, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> Over in inheritance_planner(), I noticed that the RT index of the
> SELECT query and the UPDATE/DELETE query can differ. There was some
> code that performed translations. I changed that code slightly so that
> it's a bit more optimal. It was building two lists, one for the old
> RT index and one for the new. It added elements to this list
> regardless of if the RT indexes were the same or not. I've now changed
> that to only add to the list if they differ, which I feel should never
> be slower and most likely always faster. I'm also now building a
> translation map between the old and new RT indexes, however, I only
> found one test in the regression tests which require any sort of
> translation of these RT indexes. This was with an inheritance table,
> so I need to do a bit more work to find a case where this happens with
> a partitioned table to ensure all this works.

I had a closer look at this today and the code I have in
inheritance_planner() is certainly not right.

It's pretty easy to made the SELECT and UPDATE/DELETE's RT indexes
differ with something like:

drop table part_t cascade;
create table part_t (a int, b int, c int) partition by list (a);
create table part_t12 partition of part_t for values in(1,2) partition
by list (a);
create table part_t12_1 partition of part_t12 for values in(1);
create table part_t12_2 partition of part_t12 for values in(2);
create table part_t3 partition of part_t for values in(3);
create view vw_part_t as select * from part_t;

explain analyze update vw_part_t set a = t2.a +0 from part_t t2 where
t2.a = vw_part_t.a and vw_part_t.a = (select 1);

In this case, the sub-partitioned table changes RT index. I can't
just take the RelOptInfo's from the partition_root's simple_rel_array
and put them in the correct element in the root's simple_rel_array as
they RT indexes stored within also need to be translated.

I'll be having another look at this to see what the best fix is going to be.

David