wrong query plan in 7.1beta3 - Mailing list pgsql-hackers

There seems to be an optimizer problem in 7.1beta3. The query you can see
below worked fast in 7.0.2 but in 7.1beta3 is rather slow. The problem is
that an 'index scan' has been changed to a 'seq scan'. Details:

----------------------------------------------------------------------------
CREATE FUNCTION plpgsql_call_handler ( ) RETURNS opaque AS '/.../lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER plpgsql_call_handler LANCOMPILER 'PL/pgSQL';

CREATE TABLE sd_tabla ( azonosito varchar(100) PRIMARY KEY
);

CREATE TABLE sd_grant ( tabla varchar(100) REFERENCES sd_tabla(azonosito), gname varchar(100), sel bool NOT NULL
DEFAULT'f', upd bool NOT NULL DEFAULT 'f', ins bool NOT NULL DEFAULT 'f', del bool NOT NULL DEFAULT 'f', rul bool NOT
NULLDEFAULT 'f', PRIMARY KEY (tabla, gname)
 
);

create function or_(bool,bool) returns bool as 'begin return $1 or $2;
end;' language 'plpgsql';

create aggregate aggr_or(basetype=bool, sfunc1=or_, stype1=bool,initcond1='f');

create view sd_user_grant as select tabla, usename, 
aggr_or(sel) as sel,aggr_or(ins) as ins,aggr_or(upd) as upd,
aggr_or(del) as del,aggr_or(rul) as rul from sd_grant,pg_user 
where sd_grant.gname>pg_user.usename::varchar group by tabla,usename;

explain select sel,ins,upd,del,rul from sd_user_grant where 
usename::varchar='1016' and tabla='cikk';
----------------------------------------------------------------------------

Here you can see the outputs of the EXPLAIN statement:

7.0.2:

Aggregate  (cost=5.95..5.96 rows=0 width=61) ->  Group  (cost=5.95..5.95 rows=1 width=61)       ->  Sort
(cost=5.95..5.95rows=1 width=61)             ->  Nested Loop  (cost=0.00..5.94 rows=1 width=61)                   ->
SeqScan on pg_shadow  (cost=0.00..1.75 rows=1 width=32)                   ->  Index Scan using sd_grant_pkey on
sd_grant (cost=0.00..4.08 rows=6 width=29)
 

7.1beta3:

Subquery Scan sd_user_grant  (cost=38.68..38.85 rows=1 width=61) ->  Aggregate  (cost=38.68..38.85 rows=1 width=61)
 ->  Group  (cost=38.68..38.73 rows=10 width=61)             ->  Sort  (cost=38.68..38.68 rows=10 width=61)
     ->  Nested Loop  (cost=0.00..38.51 rows=10 width=61)                         ->  Seq Scan on pg_shadow
(cost=0.00..1.01rows=1 width=32)                         ->  Seq Scan on sd_grant  (cost=0.00..20.00 rows=1000
width=29)

Unfortunately this query is a frequently used one in our application.
So we can't upgrade to 7.1 until this works properly. Please help if
there is a way to modify the query or consider it as a bug.

TIA, Zoltan

--                         Kov\'acs, Zolt\'an                        kovacsz@pc10.radnoti-szeged.sulinet.hu
          http://www.math.u-szeged.hu/~kovzol                        ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz