[SQL] Re: [HACKERS] wrong query plan in 7.1beta3

2001-01-27 Thread Tom Lane 

Kovacs Zoltan <[EMAIL PROTECTED]> 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

Re: [HACKERS] wrong query plan in 7.1beta3

2001-01-27 Thread Kovacs Zoltan 

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
 [EMAIL PROTECTED]
 http://www.math.u-szeged.hu/~kovzol
 ftp://pc10.radnoti-szeged.sulinet.hu/home/kovacsz

Re: [HACKERS] wrong query plan in 7.1beta3

2001-01-27 Thread Peter Eisentraut 

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  [EMAIL PROTECTED]   http://yi.org/peter-e/