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 NULL DEFAULT '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.95 rows=1 width=61)
              ->  Nested Loop  (cost=0.00..5.94 rows=1 width=61)
                    ->  Seq Scan 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.01 rows=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


Sat, 27 Jan 2001 15:05:45 +0100 (CET)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
wrong query plan in 7.1beta3
Kovacs Zoltan writes:

> 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:

> 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.01 rows=1 width=32)
>                           ->  Seq Scan on sd_grant  (cost=0.00..20.00 rows=1000 width=29)

You haven't VACUUM ANALYZE'd the sd_grant table.  Therefore the row
estimate is way off (1000 vs 6) and thus a sequential scan is (correctly)
thought to be faster.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/


Sat, 27 Jan 2001 15:45:08 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: wrong query plan in 7.1beta3
On Sat, 27 Jan 2001, Peter Eisentraut wrote:

> Kovacs Zoltan writes:
> 
> > 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:
> 
> > 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.01 rows=1 width=32)
> >                           ->  Seq Scan on sd_grant  (cost=0.00..20.00 rows=1000 width=29)
> 
> You haven't VACUUM ANALYZE'd the sd_grant table.  Therefore the row
> estimate is way off (1000 vs 6) and thus a sequential scan is (correctly)
> thought to be faster.
> 
> 

Thanks, I tried it. 

tir=# explain select sel,ins,upd,del,rul from sd_user_grant where
tabla='cikk' and usename::varchar='1016';
NOTICE:  QUERY PLAN:

Subquery Scan sd_user_grant  (cost=8.00..8.03 rows=1 width=61)
  ->  Aggregate  (cost=8.00..8.03 rows=1 width=61)
        ->  Group  (cost=8.00..8.01 rows=2 width=61)
              ->  Sort  (cost=8.00..8.00 rows=2 width=61)
                    ->  Nested Loop  (cost=0.00..7.99 rows=2 width=61)
                          ->  Seq Scan on pg_shadow  (cost=0.00..1.01
rows=1 width=32)
                          ->  Seq Scan on sd_grant  (cost=0.00..3.81
rows=181 width=29)

It seems to be a little bit faster, but it's still very-very slow.
The 'seq scan' on sd_grant still remained.

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


Sat, 27 Jan 2001 16:44:35 +0100 (CET)
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu>
Re: wrong query plan in 7.1beta3
Kovacs Zoltan <kovacsz@pc10.radnoti-szeged.sulinet.hu> writes:
> 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:

This is fixed in current sources: I get

Subquery Scan sd_user_grant  (cost=5.16..5.22 rows=1 width=61)
  ->  Aggregate  (cost=5.16..5.22 rows=1 width=61)
        ->  Group  (cost=5.16..5.18 rows=3 width=61)
              ->  Sort  (cost=5.16..5.16 rows=3 width=61)
                    ->  Nested Loop  (cost=0.00..5.14 rows=3 width=61)
                          ->  Seq Scan on pg_shadow  (cost=0.00..1.01 rows=1 width=32)
                          ->  Index Scan using sd_grant_pkey on sd_grant  (cost=0.00..4.07 rows=3 width=29)

			regards, tom lane

Sat, 27 Jan 2001 11:20:52 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: wrong query plan in 7.1beta3 