Re: PERFORMANCE and SIZE - Mailing list pgsql-performance

I have gotten so much spam, this subject line struck me as spam until I
looked closer.  Did it catch anyone else?

---------------------------------------------------------------------------

Alfranio Junior wrote:
>  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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073