Skip site navigation (1) Skip section navigation (2)

Re: Confusing EXPLAIN output in case of inherited tables

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Ashutosh Bapat <ashutosh(dot)bapat(at)enterprisedb(dot)com>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Confusing EXPLAIN output in case of inherited tables
Date: 2012-01-27 17:56:19
Message-ID: CA+Tgmobqtszrhpsj0-ES7R4B+R0UtAzJCi2D+N-5fFjErn6icw@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2012-01-27 18:01:44
Subject: Re: get_fn_expr_argtype() vs. internal calls
Previous:From: Robert HaasDate: 2012-01-27 17:16:51
Subject: Re: patch for parallel pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group