From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | EXPLAIN with view: bogus varno: 5 |
Date: | 2005-08-27 02:45:42 |
Message-ID: | 20050827024542.GA33982@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Running EXPLAIN on a view that has an aggregate and uses an index
results in the error "bogus varno: 5". At least I think the aggregate
and index are necessary -- removing either from the following example
allows EXPLAIN to succeed:
test=> CREATE TABLE foo (x integer);
CREATE TABLE
test=> CREATE INDEX foo_x_idx ON foo (x);
CREATE INDEX
test=> CREATE VIEW fooview1 AS SELECT count(*) FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview2 AS SELECT * FROM foo WHERE x < 10;
CREATE VIEW
test=> CREATE VIEW fooview3 AS SELECT count(*) FROM foo;
CREATE VIEW
test=> CREATE VIEW fooview4 AS SELECT * FROM foo;
CREATE VIEW
test=> \set VERBOSITY verbose
test=> EXPLAIN SELECT * FROM fooview1;
ERROR: XX000: bogus varno: 5
LOCATION: get_rte_for_var, ruleutils.c:2478
test=> EXPLAIN SELECT * FROM fooview2;
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on foo (cost=3.50..22.41 rows=713 width=4)
Recheck Cond: (x < 10)
-> Bitmap Index Scan on foo_x_idx (cost=0.00..3.50 rows=713 width=0)
Index Cond: (x < 10)
(4 rows)
test=> EXPLAIN SELECT * FROM fooview3;
QUERY PLAN
-------------------------------------------------------------
Aggregate (cost=36.75..36.75 rows=1 width=0)
-> Seq Scan on foo (cost=0.00..31.40 rows=2140 width=0)
(2 rows)
test=> EXPLAIN SELECT * FROM fooview4;
QUERY PLAN
-------------------------------------------------------
Seq Scan on foo (cost=0.00..31.40 rows=2140 width=4)
(1 row)
test=> DROP INDEX foo_x_idx;
DROP INDEX
test=> EXPLAIN SELECT * FROM fooview1;
QUERY PLAN
------------------------------------------------------------
Aggregate (cost=38.53..38.53 rows=1 width=0)
-> Seq Scan on foo (cost=0.00..36.75 rows=713 width=0)
Filter: (x < 10)
(3 rows)
--
Michael Fuhr
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2005-08-27 04:52:45 | Re: Call for 7.5 feature completion |
Previous Message | Josh Berkus | 2005-08-26 22:43:37 | Re: [HACKERS] Spikewatch testing |