X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3B1FD4758F1 for ; Mon, 12 May 2003 07:45:14 -0400 (EDT) Received: from homer.lsd.di.uminho.pt (gsd.di.uminho.pt [193.136.20.132]) by postgresql.org (Postfix) with SMTP id A23A147580B for ; Mon, 12 May 2003 07:45:00 -0400 (EDT) Received: (qmail 17971 invoked from network); 12 May 2003 11:44:50 -0000 Received: from alfranio.lsd.di.uminho.pt (HELO ialfranio) (192.168.2.144) by mailer.lsd.di.uminho.pt with SMTP; 12 May 2003 11:44:50 -0000 Message-ID: <011d01c318be$997217f0$9002a8c0@ialfranio> From: "Alfranio Junior" To: Subject: PERFORMANCE and SIZE Date: Mon, 12 May 2003 12:35:24 -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=-1.9 required=5.0 tests=BAYES_20,DATE_IN_FUTURE_06_12 version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/125 X-Sequence-Number: 1904 Hello, I'm a new PostgresSql user and I do not know so much about the performance mechanisms currently implemented and available. So, as a dummy user I think that something strange is happening with me. When I run the following command: 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; I receive the following results: (Customer table with 60.000 rows) - QUERY PLAN --------------------------------------------------------------------------- ----------------------------------------------------------- Limit (cost=4.84..4.84 rows=1 width=283) (actual time=213.13..213.13 rows=0 loops=1) -> Sort (cost=4.84..4.84 rows=1 width=283) (actual time=213.13..213.13 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..4.83 rows=1 width=283) (actual time=211.93..211.93 rows=0 loops=1) Index Cond: ((c_w_id = 1) AND (c_d_id = 1)) Filter: (c_last = 'ROUGHTATION'::bpchar) Total runtime: 213.29 msec (7 rows) (Customer table with 360.000 rows) - QUERY PLAN --------------------------------------------------------------------------- ------------------------------------------------------------- Limit (cost=11100.99..11101.00 rows=1 width=638) (actual time=20.82..20.82 rows=0 loops=1) -> Sort (cost=11100.99..11101.00 rows=4 width=638) (actual time=20.81..20.81 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..11100.95 rows=4 width=638) (actual time=20.40..20.40 rows=0 loops=1) Index Cond: ((c_w_id = 1) AND (c_d_id = 1)) Filter: (c_last = 'ROUGHTATION'::bpchar) Total runtime: 21.11 msec (7 rows) Increasing the number of rows the total runtime decreases. The customer table has the following structure: CREATE TABLE customer ( c_id int NOT NULL , c_d_id int4 NOT NULL , c_w_id int4 NOT NULL , c_first char (16) NULL , c_middle char (2) NULL , c_last char (16) NULL , c_street_1 char (20) NULL , c_street_2 char (20) NULL , c_city char (20) NULL , c_state char (2) NULL , c_zip char (9) NULL , c_phone char (16) NULL , c_since timestamp NULL , c_credit char (2) NULL , c_credit_lim numeric(12, 2) NULL , c_discount numeric(4, 4) NULL , c_balance numeric(12, 2) NULL , c_ytd_payment numeric(12, 2) NULL , c_payment_cnt int4 NULL , c_delivery_cnt int4 NULL , c_data text NULL ); ALTER TABLE customer ADD CONSTRAINT PK_customer PRIMARY KEY ( c_w_id, c_d_id, c_id ); Does anybody know what is happening ? Thanks !!!! Alfranio Junior