On 1 September 2011 19:08, hubert depesz lubaczewski <depesz@xxxxxxxxxx> 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
