Re: Confusing EXPLAIN output in case of inherited tables

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.

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