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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Robert Haas | 2012-01-27 18:01:44 | Re: get_fn_expr_argtype() vs. internal calls |
Previous Message | Robert Haas | 2012-01-27 17:16:51 | Re: patch for parallel pg_dump |