Re: Run-time pruning for ModifyTable

From: David Rowley <dgrowleyml(at)gmail(dot)com>
To: Amit Langote <amitlangote09(at)gmail(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Michael Paquier <michael(at)paquier(dot)xyz>, Thomas Munro <thomas(dot)munro(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, "Kato, Sho" <kato-sho(at)jp(dot)fujitsu(dot)com>
Subject: Re: Run-time pruning for ModifyTable
Date: 2020-03-24 23:51:38
Message-ID: CAApHDvqw4VZrLUShuKQno2evk8k8kYULBuF1Yxt=H5ib3kCpbQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2020-03-25 00:00:55 Re: Run-time pruning for ModifyTable
Previous Message Tom Lane 2020-03-24 23:49:04 Re: NOT IN subquery optimization