Re: Confusing EXPLAIN output in case of inherited tables

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

>
>
> On Wed, Jan 11, 2012 at 5:13 PM, Ashutosh Bapat <
> ashutosh(dot)bapat(at)enterprisedb(dot)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(at)postgresql(dot)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
>
> Website: www.enterprisedb.com
> EnterpriseDB Blog : http://blogs.enterprisedb.com
> Follow us on Twitter : http://www.twitter.com/enterprisedb
>
>
>
>
>

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