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/
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
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/
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
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/
On 1 September 2011 19:08, hubert depesz lubaczewski <depesz@depesz.com>wrote:
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 a.y (things) SELECT x FROM generate_series(1,100,3) z(x);
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)
Output: a.y.id, a.y.things, b.y.id, b.y.things
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