X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B78E34763A9 for ; Tue, 13 May 2003 08:31:44 -0400 (EDT) Received: from homer.lsd.di.uminho.pt (gsd.di.uminho.pt [193.136.20.132]) by postgresql.org (Postfix) with SMTP id C9F724763A1 for ; Tue, 13 May 2003 08:31:36 -0400 (EDT) Received: (qmail 8419 invoked from network); 13 May 2003 12:31:30 -0000 Received: from alfranio.lsd.di.uminho.pt (HELO ialfranio) (192.168.2.144) by mailer.lsd.di.uminho.pt with SMTP; 13 May 2003 12:31:30 -0000 Message-ID: <012a01c3198e$48957270$9002a8c0@ialfranio> From: "Alfranio Junior" To: References: <011d01c318be$997217f0$9002a8c0@ialfranio> <200305120849.39381.josh@agliodbs.com> Subject: Re: PERFORMANCE and SIZE Date: Tue, 13 May 2003 13:28:58 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-Spam-Status: No, hits=-11.6 required=5.0 tests=BAYES_20,DATE_IN_FUTURE_06_12,QUOTED_EMAIL_TEXT,REFERENCES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/146 X-Sequence-Number: 1925 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. > > > 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 >