pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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
>




view thread (8+ messages)  latest in thread

Message-ID: <012a01c3198e$48957270$9002a8c0@ialfranio>
Permalink:  ../012a01c3198e$48957270$9002a8c0@ialfranio/
Also on:    postgresql.org/message-id/012a01c3198e$48957270$9002a8c0@ialfranio

 · 

reply

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