wrong query plan in 7.1beta3
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
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/
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
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