Re: ExplainModifyTarget doesn't work as expected

Hi Fujita-san,
I agree that it's a problem, and it looks more severe when there are
multiple children
postgres=# create table parent (a int check (a < 0) no inherit);
CREATE TABLE
postgres=# create table child1 (a int check (a >= 0));
CREATE TABLE
postgres=# create table child2 (a int check (a >= 0));
CREATE TABLE
postgres=# create table child3 (a int check (a >= 0));
CREATE TABLE
postgres=# alter table child1 inherit parent;
ALTER TABLE
postgres=# alter table child2 inherit parent;
ALTER TABLE
postgres=# alter table child3 inherit parent;
ALTER TABLE
postgres=# explain update parent set a = a * 2 where a >= 0;
QUERY PLAN
----------------------------------------------------------------
Update on child1 (cost=0.00..126.00 rows=2400 width=10)
-> Seq Scan on child1 (cost=0.00..42.00 rows=800 width=10)
Filter: (a >= 0)
-> Seq Scan on child2 (cost=0.00..42.00 rows=800 width=10)
Filter: (a >= 0)
-> Seq Scan on child3 (cost=0.00..42.00 rows=800 width=10)
Filter: (a >= 0)
(7 rows)

It's certainly confusing why would an update on child1 cause scan on child*.

But I also think that showing parent's name with Upate would be misleading
esp. when user expects it to get filtered because of constraint exclusion.

Instead, can we show all the relations that are being modified e.g Update
on child1, child2, child3. That will disambiguate everything.

On Mon, Dec 22, 2014 at 12:20 PM, Etsuro Fujita <fujita(dot)etsuro(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

> Hi,
>
> I think ExplainModifyTarget should show the parent of the inheritance
> tree in multi-target-table cases, as described there, but noticed that
> it doesn't always work like that. Here is an example.
>
> postgres=# create table parent (a int check (a < 0) no inherit);
> CREATE TABLE
> postgres=# create table child (a int check (a >= 0));
> CREATE TABLE
> postgres=# alter table child inherit parent;
> ALTER TABLE
> postgres=# explain update parent set a = a * 2 where a >= 0;
> QUERY PLAN
> ---------------------------------------------------------------
> Update on child (cost=0.00..42.00 rows=800 width=10)
> -> Seq Scan on child (cost=0.00..42.00 rows=800 width=10)
> Filter: (a >= 0)
> (3 rows)
>
> IIUC, I think this is because ExplainModifyTarget doesn't take into
> account that the parent *can* be excluded by constraint exclusion. So,
> I added a field to ModifyTable to record the parent, apart from
> resultRelations. (More precisely, the parent in its role as a simple
> member of the inheritance tree is recorded so that appending digits to
> refname in select_rtable_names_for_explain works as before.) Attached
> is a proposed patch for that.
>
> Thanks,
>
> Best regards,
> Etsuro Fujita
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company