Postgres Pro
Downloads
Home   >   mailing lists

Re: ExplainModifyTarget doesn't work as expected - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: ExplainModifyTarget doesn't work as expected
Date
Msg-id CAFjFpRdtocgbf38P9CAkGOMkUUXyrHD7NWH0Khx9rEZCG7-wuQ@mail.gmail.com
Whole thread Raw
In response to ExplainModifyTarget doesn't work as expected  (Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>)
Responses Re: ExplainModifyTarget doesn't work as expected
List pgsql-hackers
Tree view
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.etsuro@lab.ntt.co.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers




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

pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: SSL information view
Next
From: Michael Paquier
Date:
Subject: Small memory leak in execute.c of ECPG driver