Re: Confusing EXPLAIN output in case of inherited tables

On Wed, Jan 11, 2012 at 6:43 AM, 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".

It's a feature, not a bug, that we schema-qualify names when VERBOSE
is specified. That was done on purpose for the benefit of external
tools that might need this information to disambiguate which object is
being referenced.

Table *aliases*, of course, should not be schema-qualified, but I
don't think that's what we're doing. You could make it more clear by
including an alias in the query, like this:

explain verbose select * into table ramp from road hwy where name ~ '.*Ramp';

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company