Re: PERFORMANCE and SIZE

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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073