From: | Kovacs Zoltan <kovacsz(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: wrong query plan in 7.1beta3 |
Date: | 2001-01-27 15:44:35 |
Message-ID: | Pine.LNX.4.21.0101271640150.15325-100000@pc10.radnoti-szeged.sulinet.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
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(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)hu
http://www.math.u-szeged.hu/~kovzol
ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2001-01-27 15:58:16 | RE: plan for running postmaster directly as NT service |
Previous Message | Fred Yankowski | 2001-01-27 14:49:46 | plan for running postmaster directly as NT service |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-01-27 16:20:52 | Re: wrong query plan in 7.1beta3 |
Previous Message | John Reid | 2001-01-27 15:13:33 | Re: abstract data types? |