Re: Why schema of table is removed from explain?

On 1 September 2011 19:08, hubert depesz lubaczewski <depesz(at)depesz(dot)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