Re: PERFORMANCE and SIZE
От | Bruce Momjian |
---|---|
Тема | Re: PERFORMANCE and SIZE |
Дата | |
Msg-id | 200305150330.h4F3UHV03141@candle.pha.pa.us обсуждение исходный текст |
Ответ на | PERFORMANCE and SIZE ("Alfranio Junior" <alfranio@lsd.di.uminho.pt>) |
Ответы |
Re: PERFORMANCE and SIZE
|
Список | 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
В списке pgsql-performance по дате отправления: