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

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.

Regards,
Chetan

--

EnterpriseDB Corporation
The Enterprise PostgreSQL Company

Website: www.enterprisedb.com

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

Follow us on Twitter : http://www.twitter.com/enterprisedb