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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to