Skip site navigation (1) Skip section navigation (2)

Re: PERFORMANCE and SIZE

From: "Alfranio Junior" <alfranio(at)lsd(dot)di(dot)uminho(dot)pt>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: PERFORMANCE and SIZE
Date: 2003-05-13 20:28:58
Message-ID: 012a01c3198e$48957270$9002a8c0@ialfranio (view raw or flat)
Thread:
Lists: pgsql-performance
Josh,

I ran the vacuumdb as follows:
vacuumdb -f -v -e -a

and after that,

vacuumdb -z -v -e -a.

And now, the optimizer started to use a table scan and in consequence gives
me:

explain analyze select * from customer

where c_last = 'ROUGHTATION' and

c_w_id = 1 and

c_d_id = 1

order by c_w_id, c_d_id, c_last, c_first limit 1;

QUERY PLAN

----------------------------------------------------------------------------
-----------------------------------------

Limit (cost=6302.03..6302.03 rows=1 width=639) (actual time=208.33..208.33
rows=0 loops=1)

-> Sort (cost=6302.03..6302.04 rows=3 width=639) (actual time=208.32..208.32
rows=0 loops=1)

Sort Key: c_w_id, c_d_id, c_last, c_first

-> Seq Scan on customer (cost=0.00..6302.00 rows=3 width=639) (actual
time=207.99..207.99 rows=0 loops=1)

Filter: ((c_last = 'ROUGHTATION'::bpchar) AND (c_w_id = 1) AND (c_d_id = 1))

Total runtime: 208.54 msec

(6 rows)




When I force the index use a receive a better result:

set enable_seqscan to off;

explain analyze select * from customer

where c_last = 'ROUGHTATION' and

c_w_id = 1 and

c_d_id = 1

order by c_w_id, c_d_id, c_last, c_first limit 1;


QUERY PLAN

----------------------------------------------------------------------------
-----------------------------------------------------------

Limit (cost=9860.03..9860.03 rows=1 width=639) (actual time=13.98..13.98
rows=0 loops=1)

-> Sort (cost=9860.03..9860.04 rows=3 width=639) (actual time=13.98..13.98
rows=0 loops=1)

Sort Key: c_w_id, c_d_id, c_last, c_first

-> Index Scan using pk_customer on customer (cost=0.00..9860.00 rows=3
width=639) (actual time=13.86..13.86 rows=0 loops=1)

Index Cond: ((c_w_id = 1) AND (c_d_id = 1))

Filter: (c_last = 'ROUGHTATION'::bpchar)

Total runtime: 14.11 msec

(7 rows)

Is this the only way to force the index ?
What are the reasons to the optimizer to decide for a worse plan ?

> Alfranio,
>
> > I'm a new PostgresSql user and I do not know so much about the
> >  performance mechanisms currently implemented and available.
> <snip>
> >  Does anybody know what is happening ?
>
> 90% likely:  You haven't run VACUUM FULL ANALYZE in a while.
>
> --
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>


In response to

Responses

pgsql-performance by date

Next:From: scott.marloweDate: 2003-05-13 20:51:37
Subject: Re: PERFORMANCE and SIZE
Previous:From: Tom LaneDate: 2003-05-13 20:16:10
Subject: Re: How are null's stored?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group