From: Alfranio Junior <alfranio@lsd.di.uminho.pt>
To: pgsql-performance@postgresql.org
Subject: Re: PERFORMANCE and SIZE
Date: Tue, 13 May 2003 13:28:58 -0700
Message-ID: <012a01c3198e$48957270$9002a8c0@ialfranio> (raw)
References: <011d01c318be$997217f0$9002a8c0@ialfranio>
<200305120849.39381.josh@agliodbs.com>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: alfranio@lsd.di.uminho.pt
Subject: Re: PERFORMANCE and SIZE
In-Reply-To: <012a01c3198e$48957270$9002a8c0@ialfranio>
References: <011d01c318be$997217f0$9002a8c0@ialfranio> <200305120849.39381.josh@agliodbs.com> <012a01c3198e$48957270$9002a8c0@ialfranio>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox