Re: Confusing EXPLAIN output in case of inherited tables - Mailing list pgsql-hackers

From Ashutosh Bapat
Subject Re: Confusing EXPLAIN output in case of inherited tables
Date
Msg-id CAFjFpReknaRjdBN3NMYYsGNeaVCtVfL-2WXfoCH5gPcBaNvPgg@mail.gmail.com
Whole thread Raw
In response to Re: Confusing EXPLAIN output in case of inherited tables  (Chetan Suttraway <chetan.suttraway@enterprisedb.com>)
List pgsql-hackers


On Wed, Jan 11, 2012 at 5:25 PM, Chetan Suttraway <chetan.suttraway@enterprisedb.com> wrote:


On Wed, Jan 11, 2012 at 5:13 PM, Ashutosh Bapat <ashutosh.bapat@enterprisedb.com> wrote:
Hi,
After running regression, I ran EXPLAIN on one of the queries in regression (test create_misc) and got following output
regression=# explain verbose select * into table ramp from road where name ~ '.*Ramp';
                                     QUERY PLAN                                    
------------------------------------------------------------------------------------
 Result  (cost=0.00..154.00 rows=841 width=67)
   Output: public.road.name, public.road.thepath
   ->  Append  (cost=0.00..154.00 rows=841 width=67)
         ->  Seq Scan on public.road  (cost=0.00..135.05 rows=418 width=67)
               Output: public.road.name, public.road.thepath
               Filter: (public.road.name ~ '.*Ramp'::text)
         ->  Seq Scan on public.ihighway road  (cost=0.00..14.99 rows=367 width=67)
                                                        ^^^^^
               Output: public.road.name, public.road.thepath
                           ^^^^^^^^^^,           ^^^^^^
               Filter: (public.road.name ~ '.*Ramp'::text)
                         ^^^^^^^^^^^
         ->  Seq Scan on public.shighway road  (cost=0.00..3.96 rows=56 width=67)
               Output: public.road.name, public.road.thepath
               Filter: (public.road.name ~ '.*Ramp'::text)
(12 rows)

regression=# \d+ road
                        Table "public.road"
 Column  | Type | Modifiers | Storage  | Stats target | Description
---------+------+-----------+----------+--------------+-------------
 name    | text |           | extended |              |
 thepath | path |           | extended |              |
Indexes:
    "rix" btree (name)
Child tables: ihighway,
              shighway
Has OIDs: no

Table "road" has children "ihighway" and "shighway" as seen in the \d+ output above. The EXPLAIN output of Seq Scan node on children has "public.road" as prefix for variables. "public.road" could imply the parent table "road" and thus can cause confusion, as to what's been referreed, the columns of parent table or child table. In the EXPLAIN output children tables have "road" as alias (as against "public.road"). The alias comes from RangeTblEntry->eref->aliasname. It might be better to have "road" as prefix in the variable names over "public.road".

The reason why this happens is the code in get_variable()
3865     /* Exceptions occur only if the RTE is alias-less */
3866     if (rte->alias == NULL)
3867     {
3868         if (rte->rtekind == RTE_RELATION)
3869         {
3870             /*
3871              * It's possible that use of the bare refname would find another
3872              * more-closely-nested RTE, or be ambiguous, in which case we need
3873              * to specify the schemaname to avoid these errors.
3874              */
3875             if (find_rte_by_refname(rte->eref->aliasname, context) != rte)
3876                 schemaname = get_namespace_name(get_rel_namespace(rte->relid));
3877         }

If there is no alias, we find out the schema name and later add it to the prefix. In the inherited table case, we are actually creating a "kind of" alias for the children table and thus we should not find out the schema name and add it to the prefix. This case has been taken care of in get_from_clause_item(),
6505         else if (rte->rtekind == RTE_RELATION &&
6506             strcmp(rte->eref->aliasname, get_relation_name(rte->relid)) != 0)
6507         {
6508             /*
6509              * Apparently the rel has been renamed since the rule was made.
6510              * Emit a fake alias clause so that variable references will still
6511              * work.  This is not a 100% solution but should work in most
6512              * reasonable situations.
6513              */
6514             appendStringInfo(buf, " %s",
6515                              quote_identifier(rte->eref->aliasname));
6516             gavealias = true;
6517         }

I see similar code in ExplainTargetRel()
1778         if (objectname == NULL ||
1779             strcmp(rte->eref->aliasname, objectname) != 0)
1780             appendStringInfo(es->str, " %s",
1781                              quote_identifier(rte->eref->aliasname));

Based on this, here is patch to not add schemaname in the prefix for a variable.

I have run make check. All except inherit.sql passed. The expected output change is included in the patch.

--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers


A table can inherit from one or more parent table. So in that case, qualifying schema/table name
helps in finding out where the column is coming from.

Do you have any example of this case? From the code it does not look like that. Even if a table is inherited from more than one parent, the aliasname is set to the name of the parent on which the scan is executed, it will have only one alias. The case you are talking about can happen in case more than one of these parents is included in the query. But, in such case there will be multiple RTEs for same child table each coming from corresponding parent, and thus will have corresponding aliasname.
 

Regards,
Chetan

--
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

EnterpriseDB Blog : http://blogs.enterprisedb.com







--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company

pgsql-hackers by date:

Previous
From: Fujii Masao
Date:
Subject: Re: [COMMITTERS] pgsql: Send new protocol keepalive messages to standby servers.
Next
From: Simon Riggs
Date:
Subject: Re: measuring spinning