Bug in 8.0.0rc3 query planner: constant column in view changes execution plan

--- This script will demonstrate a bug-like misbehaviour found in the query planner
--- of postgresql 8.0.0.rc3 server.
--- Run it as an user with administrative rights with psql

--- query planner behaviour as demonstrated:
--------------------------------------
--- Adding a constant expression column to a view definition leads to different
--- (and very bad) execution plans.
--- The constant expression column can be reduced to "1 AS constval".
---
--- querey planner behaviour as expected:
---------------------------------------------
--- SELECTing expressions, which do not access any table data,
--- should not influence the execution plan.
---
--- Platform:
-------------
--- * postgresql 8.0.0.rc3 server
--- * compiled with #define FUNC_MAX_ARGS = INDEX_MAX_KEYS = 250
---   in   .\src\include\pg_config_manual.h
--- * System: Windows 2000, Service Pack 4
---   AMD Athlon XP 2600+, 1.5 GB RAM.
--- * file "postgresql.conf" was not modified.
---
--- What this script executes:
-------------------------------
--- * three tables t_a, t_b and t_c are defined and populated with data (total ca. 5.000.000  records,
---   this may take half an hour on Athlon 2500.
--- * the tables are linked with each other via CONSTRAINT..REFERENCES,
---   indexes on foreign keys are generated.
--- * three very similar views v_test_good, v_test_strange and v_test_bad are defined,
---   which join all three tables.
--- * an identical SELECT is executed on each view, it uses LEFT JOIN .
---   SELECTing the view which contains column "1 AS constval" runs forever.
---   SELECTing the view which contains column "table.col/table.col AS constval" runs fine.
---
--- feed back email:
-------------------
--- hoppe@geoinformationsdienst.de

----------------------- BEGIN OF SQL CODE --------------------------------------
-- DELETE objects from previous test run
drop view v_test_good cascade ;
drop view v_test_strange cascade ;
drop view v_test_bad cascade ;

drop table t_c cascade ;
drop table t_b cascade ;
drop table t_a cascade ;

-- create tables
-- t_a is master, t_b is detail of t_a , t_c is detail of t_a

-- dummy columns col1..col6 will be filled with dummy data,
-- this is needed to reproduce the error!
CREATE TABLE t_a (a_id integer, info varchar,  col1 varchar,  col2 varchar,  col3 varchar,  col4 varchar,  col5
varchar, col6 varchar,  CONSTRAINT pk_a PRIMARY KEY (a_id) 
) ;

CREATE TABLE t_b (b_id integer, a_id integer, info varchar,  col1 varchar,  col2 varchar,  col3 varchar,  col4 varchar,
col5 varchar,  col6 varchar,  CONSTRAINT pk_b PRIMARY KEY (b_id) 
) ;


CREATE TABLE t_c (c_id integer, a_id integer , info varchar,  col1 varchar,  col2 varchar,  col3 varchar,  col4
varchar, col5 varchar,  col6 varchar,  CONSTRAINT pk_c PRIMARY KEY (c_id) 
) ;



-- function to append detail data to a master table.
-- fills a master, if master_table IS NULL.
-- detail data is generated for  master records with pk BETWWEN min_master_pk_val AND  max_master_pk_val
CREATE OR REPLACE FUNCTION generate_detail_data(varchar, varchar,varchar,varchar,varchar,integer,integer,integer)
RETURNSvarchar AS 
$BODY$  BEGIN    DECLARE      master_table ALIAS FOR $1 ;      detail_table ALIAS FOR $2 ;      master_pk    ALIAS FOR
$3;      detail_pk    ALIAS FOR $4 ;      detail_fk    ALIAS FOR $5 ;      min_master_pk_val ALIAS FOR $6 ;
max_master_pk_valALIAS FOR $7 ;      n            ALIAS FOR $8 ; 
      maxrecord        RECORD ;      masterrecord    RECORD ;      v_detail_pk   integer ;      v_detail_fk   integer ;
    i             integer ;      s             VARCHAR ;    BEGIN      FOR maxrecord IN EXECUTE 'SELECT MAX(' ||
detail_pk||') AS pk FROM ' || detail_table LOOP         v_detail_pk := maxrecord.pk ; -- just 1 row!      END LOOP ; 
      IF v_detail_pk IS NULL THEN        v_detail_pk := 0 ; -- no records yet      END IF ;
      if (master_table IS NULL) OR (master_pk IS NULL) THEN         -- Detail has no master          FOR i IN 1 .. n
LOOP           v_detail_pk := v_detail_pk + 1 ;            -- some data ....            s := 'INSERTED detail #' || i
          || ' with id = '|| to_char(v_detail_pk)              || ' at ' || TO_CHAR(current_timestamp) ;
EXECUTE' INSERT INTO '               || detail_table               || '(' || detail_pk || ',
info,col1,col2,col3,col4,col5,col6)'               || ' values(' || v_detail_pk               || ', ''' || s || ''''
          || ', ''' || s || ''''               || ', ''' || s || ''''               || ', ''' || s || ''''
|| ', ''' || s || ''''               || ', ''' || s || ''''               || ', ''' || s || ''''               || ') '
;         END LOOP ;      ELSE        -- link detail records with master        FOR masterrecord IN EXECUTE 'SELECT '
||master_pk ||' AS pk FROM ' || master_table            || ' WHERE ' || master_pk || ' BETWEEN ' || min_master_pk_val
||' AND ' || max_master_pk_val        LOOP          v_detail_fk := masterrecord.pk ;          FOR i IN 1 .. n LOOP
     v_detail_pk := v_detail_pk + 1 ;            -- some data ....            s := 'INSERTED detail #' || i
|| ' for master ' || masterrecord.pk              || ' with id = ' || v_detail_pk              || ' at ' ||
TO_CHAR(current_timestamp);            EXECUTE ' INSERT INTO '               || detail_table               || '(' ||
detail_pk|| ',' || detail_fk || ', info,col1,col2,col3,col4,col5,col6) '               || ' values(' || v_detail_pk ||
','|| v_detail_fk               || ', ''' || s || ''''               || ', ''' || s || ''''               || ', ''' ||
s|| ''''               || ', ''' || s || ''''               || ', ''' || s || ''''               || ', ''' || s || ''''
             || ', ''' || s || ''''               || ') ' ; 
          END LOOP ;        END LOOP ;      END IF ;
      return v_detail_pk ;    END ;  END ;
$BODY$   LANGUAGE 'plpgsql' ;

-- Fill data into structure

-- a = master = objekte
select generate_detail_data(null,'t_a', null, 'a_id', null, null, null, 2400000) ;
-- select count(*) from t_a ;

-- b = detail = raumelemente
select generate_detail_data('t_a','t_b', 'a_id', 'b_id', 'a_id', 0,1700000, 1) ;
-- select count(*) from t_b ;

-- c = detail = flurstuecke
select generate_detail_data('t_a','t_c', 'a_id', 'c_id', 'a_id', 0,1500000, 1) ;
-- select count(*) from t_c ;

-- now there exist 1.5000.000 entries in t_c, who have also corresponding entries in t_b

--- now set fk-constraints (and indexes) ;
ALTER TABLE t_b ADD CONSTRAINT fk_b_1 FOREIGN KEY (a_id) REFERENCES t_a (a_id) ON UPDATE NO ACTION ON DELETE NO ACTION
DEFERRABLE 
INITIALLY IMMEDIATE ;
ALTER TABLE t_c ADD CONSTRAINT fk_c_1 FOREIGN KEY (a_id) REFERENCES t_a (a_id) ON UPDATE NO ACTION ON DELETE NO ACTION
DEFERRABLE 
INITIALLY IMMEDIATE;

CREATE INDEX idx_b_fk1 ON t_b(a_id) ;
CREATE INDEX idx_c_fk1 ON t_c(a_id) ;

VACUUM FULL ANALYZE t_a ;
VACUUM FULL ANALYZE t_b ;
VACUUM FULL ANALYZE t_c ;



-- this view contains a constant column,
-- this leads to bad execution plans
CREATE OR REPLACE VIEW v_test_bad AS SELECT   t_a.a_id,   t_b.b_id,   t_c.c_id,   1    AS  constcol
FROM t_a, t_b, t_c
WHERE t_a.a_id = t_b.a_id  AND t_a.a_id = t_c.a_id ;

-- this view contains no constant columns, ... execution plan is good
CREATE OR REPLACE VIEW v_test_good AS SELECT   t_a.a_id,   t_b.b_id,   t_c.c_id
FROM t_a, t_b, t_c
WHERE t_a.a_id = t_b.a_id  AND t_a.a_id = t_c.a_id ;


-- this view contains a expression columns, which yield also a cosntant "1",
--  ... execution plan is good!?!
CREATE OR REPLACE VIEW v_test_strange AS SELECT   t_a.a_id,   t_b.b_id,   t_c.c_id,   t_a.a_id / t_a.a_id    AS
constcol
FROM t_a, t_b, t_c
WHERE t_a.a_id = t_b.a_id  AND t_a.a_id = t_c.a_id ;


-- Now test the 3 views with code from a real application ...

-- This one runs well
SELECT R.a_ID,  R.b_ID  FROM t_b R LEFT JOIN v_test_good V on R.a_id = V.a_id WHERE r.b_id between 900000 and 900999
ANDv.a_id = v.a_id -- this cheat is necessary to make it runnable ... 
;

-- This one runs well, too
SELECT R.a_ID,  R.b_ID, v.constcol  FROM t_b R LEFT JOIN v_test_strange V on R.a_id = V.a_id WHERE r.b_id between
900000and 900999   AND v.a_id = v.a_id -- this cheat is necessary to make it runnable ... 
;

-- This one runs forever ...
SELECT R.a_ID,  R.b_ID, v.constcol  FROM t_b R LEFT JOIN v_test_bad V on R.a_id = V.a_id WHERE r.b_id between 900000
and900999   AND v.a_id = v.a_id 
;