Thread: Why schema of table is removed from explain?
example:
$ create schema x;
CREATE SCHEMA
$ create table x.y as select * from pg_class;
SELECT 294
$ explain select * from x.y limit 1;
QUERY PLAN
------------------------------------------------------------
Limit (cost=0.00..0.04 rows=1 width=189)
-> Seq Scan on y (cost=0.00..13.70 rows=370 width=189)
(2 rows)
Why it doesn't show "Seq Scan on x.y" ? it makes certain plans virtually
useless, when you can't know which schema was used?!
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On 1 September 2011 19:08, hubert depesz lubaczewski <depesz@depesz.com> wrote:
INSERT INTO a.y (things) SELECT x FROM generate_series(1,100,3) z(x);example:
$ create schema x;
CREATE SCHEMA
$ create table x.y as select * from pg_class;
SELECT 294
$ explain select * from x.y limit 1;
QUERY PLAN
------------------------------------------------------------
Limit (cost=0.00..0.04 rows=1 width=189)
-> Seq Scan on y (cost=0.00..13.70 rows=370 width=189)
(2 rows)
Why it doesn't show "Seq Scan on x.y" ? it makes certain plans virtually
useless, when you can't know which schema was used?!
You mean like this?
CREATE SCHEMA a;
CREATE SCHEMA b;
CREATE TABLE a.y (id serial, things int);
CREATE TABLE b.y (id serial, things int);
INSERT INTO b.y (things) SELECT x FROM generate_series(1,100,5) z(x);
EXPLAIN SELECT * FROM a.y INNER JOIN b.y ON a.y.things = b.y.things;
QUERY PLAN
--------------------------------------------------------------
Hash Join (cost=1.45..3.12 rows=20 width=16)
Hash Cond: (a.y.things = b.y.things)
-> Seq Scan on y (cost=0.00..1.34 rows=34 width=8)
-> Hash (cost=1.20..1.20 rows=20 width=8)
-> Seq Scan on y (cost=0.00..1.20 rows=20 width=8)
(5 rows)
I agree, it's not helpful. But EXPLAIN (VERBOSE) prefixes the schema:
EXPLAIN SELECT * FROM a.y INNER JOIN b.y ON a.y.things = b.y.things;
QUERY PLAN
----------------------------------------------------------------
Hash Join (cost=1.45..3.12 rows=20 width=16)
Hash Cond: (a.y.things = b.y.things)
-> Seq Scan on a.y (cost=0.00..1.34 rows=34 width=8)
Output: a.y.id, a.y.things
-> Hash (cost=1.20..1.20 rows=20 width=8)
Output: b.y.id, b.y.things
-> Seq Scan on b.y (cost=0.00..1.20 rows=20 width=8)
Output: b.y.id, b.y.things
(9 rows)
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Thu, Sep 01, 2011 at 04:24:59PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > $ explain select * from x.y limit 1;
> > QUERY PLAN
> > ------------------------------------------------------------
> > Limit (cost=0.00..0.04 rows=1 width=189)
> > -> Seq Scan on y (cost=0.00..13.70 rows=370 width=189)
> > (2 rows)
>
> > Why it doesn't show "Seq Scan on x.y" ?
>
> The non-plain-text output formats provide that sort of detail, if you
> need it.
Which is great, but why can't we have it in plain text too?
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
On Thu, Sep 01, 2011 at 04:39:06PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <depesz@depesz.com> writes:
> > On Thu, Sep 01, 2011 at 04:24:59PM -0400, Tom Lane wrote:
> >> The non-plain-text output formats provide that sort of detail, if you
> >> need it.
>
> > Which is great, but why can't we have it in plain text too?
>
> It's frequently unnecessary, and horizontal space is precious in the
> plain-text format.
Well, I understand that adding "public." usually wouldn't be any good,
but what about representing the table name as oid::regclass does? i.e.
adds schema only if table is in schema that is not in search_path?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
hubert depesz lubaczewski <depesz@depesz.com> writes:
> $ explain select * from x.y limit 1;
> QUERY PLAN
> ------------------------------------------------------------
> Limit (cost=0.00..0.04 rows=1 width=189)
> -> Seq Scan on y (cost=0.00..13.70 rows=370 width=189)
> (2 rows)
> Why it doesn't show "Seq Scan on x.y" ?
The non-plain-text output formats provide that sort of detail, if you
need it.
regards, tom lane
hubert depesz lubaczewski <depesz@depesz.com> writes:
> On Thu, Sep 01, 2011 at 04:24:59PM -0400, Tom Lane wrote:
>> The non-plain-text output formats provide that sort of detail, if you
>> need it.
> Which is great, but why can't we have it in plain text too?
It's frequently unnecessary, and horizontal space is precious in the
plain-text format.
regards, tom lane