X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7E53247655C for ; Wed, 14 May 2003 23:40:16 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 00291-10 for ; Wed, 14 May 2003 23:40:14 -0400 (EDT) Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (Postfix) with ESMTP id A1069476555 for ; Wed, 14 May 2003 23:40:13 -0400 (EDT) Received: from oasis.net.au (guardian [210.8.139.5]) by water.oasis.net.au (8.9.3/8.9.3/Debian 8.9.3-21) with ESMTP id NAA19692 for ; Thu, 15 May 2003 13:49:45 +1000 X-Authentication-Warning: water.oasis.net.au: Host guardian [210.8.139.5] claimed to be oasis.net.au Message-ID: <3EC30D7D.4070107@oasis.net.au> Date: Thu, 15 May 2003 13:46:05 +1000 From: Rudi Starcevic User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-performance@postgresql.org Subject: Re: PERFORMANCE and SIZE References: <200305150330.h4F3UHV03141@candle.pha.pa.us> In-Reply-To: <200305150330.h4F3UHV03141@candle.pha.pa.us> Content-Type: multipart/alternative; boundary="------------030102000703030609060504" X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/177 X-Sequence-Number: 1956 This is a multi-part message in MIME format. --------------030102000703030609060504 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 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 >> >> >> > > > --------------030102000703030609060504 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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 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

    

  

--------------030102000703030609060504--