Re: PERFORMANCE and SIZE - Mailing list pgsql-performance

Ha !

No - it didn't catch me -- but Yes my spam has been going through the roof lately.
Over here in Australia it's in the Media alot of late - Spam increases.
Seems like everyone is suffering.

Cheers
RS.




Bruce Momjian wrote:

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 theperformance 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.13rows=0 loops=1)    ->  Sort  (cost=4.84..4.84 rows=1 width=283) (actualtime=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.83rows=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) (actualtime=20.82..20.82 rows=0 loops=1)    ->  Sort  (cost=11100.99..11101.00 rows=4 width=638) (actualtime=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=0loops=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