PERFORMANCE and SIZE - Mailing list pgsql-performance

 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