Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 421BB9FC607 for ; Wed, 31 Oct 2007 18:53:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 18421-03-5 for ; Wed, 31 Oct 2007 18:53:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from linus.numail.org (mail.numail.org [216.130.152.7]) by postgresql.org (Postfix) with ESMTP id 45AA19FC74F for ; Wed, 31 Oct 2007 17:15:42 -0300 (ADT) Received: (qmail 32148 invoked by uid 0); 31 Oct 2007 20:15:39 -0000 Received: from 72.15.64.64 by linus.numail.org (envelope-from , uid 101) with qmail-scanner-1.23 (fsecure: 4.50/2111/fprot(2004-10-21)/avp(2004-10-21). Clear:RC:1(72.15.64.64):. Processed in 0.210454 secs); 31 Oct 2007 20:15:39 -0000 Received: from unknown (HELO ?24.25.1.100?) (72.15.64.64) by mail.numail.org with AES256-SHA encrypted SMTP; 31 Oct 2007 20:15:39 -0000 Message-ID: <4728E266.6030709@laotraesquina.com.ar> Date: Wed, 31 Oct 2007 16:15:34 -0400 From: Pablo Alcaraz User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: Scott Marlowe CC: Steven Flatt , pgsql-performance@postgresql.org Subject: Re: tables with 300+ partitions References: <47277341.7070704@laotraesquina.com.ar> <357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com> <47289EEE.50008@laotraesquina.com.ar> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050004000504070400050000" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/386 X-Sequence-Number: 27408 This is a multi-part message in MIME format. --------------050004000504070400050000 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Scott Marlowe wrote: > On 10/31/07, Pablo Alcaraz wrote: > >> Steven Flatt wrote: >> >> On 10/30/07, Pablo Alcaraz wrote: >> >>> I did some testing. I created a 300 partitioned empty table. Then, I >>> inserted some rows on it and the perfomance was SLOW too. >>> >> Is the problem with inserting to the partitioned table or selecting from it? >> It sounds like inserting is the problem in which case I ask: how are you >> redirecting inserts to the appropriate partition? If you're using rules, >> then insert performance will quickly degrade with number of partitions as >> *every* rule needs to be evaluated for *every* row inserted to the base >> table. Using a trigger which you can modify according to some schedule is >> much faster, or better yet, use some application-level logic to insert >> directly to the desired partition. >> >> Steve I was a program inserting into the base table. The program ran in 200+ >> threads and every thread insert data on it. Every thread inserts a row every >> 3 seconds aprox.(or they used to do it), but when I put more partitions the >> insert speed went to 1 insert every 2 minutes. >> >> The selects that need to evaluate all partitions were slow too, but I think >> I can wait for them. :D >> >> I wonder if the update are slow too. I do not know that. >> >> Do I need to do a trigger for insert only or I need a trigger to update and >> delete too? >> > > You need a trigger for any update / delete / insert you don't want to > be really slow. Basically, if a rule is doing it now, you need a > trigger to do it to speed it up. > > My experience has been that at 200 to 1000 partitions, the speed of > the smaller tables still makes selects faster than with one big table > for certain kinds of access. At some point, the cost of planning a > lookup against thousands of tables will be more than the savings of > looking in a really small table. > > The nice thing about triggers is that you can use maths to figure out > the name of the table you'll be writing to instead of a static table > like most rules use. So, all you have to do is make sure the new > tables get added under the parent and poof, you're ready to go, no > need for a new trigger. > > Currently I have a insert rule only and the updates are right solved. I think the UPDATEs use the constraint because the program use the base table everywhere. This is the base table structure: -- Table: t -- DROP TABLE t; CREATE TABLE t ( idt bigint NOT NULL, idtpadre bigint NOT NULL, e integer NOT NULL, dmodi timestamp without time zone NOT NULL DEFAULT now(), p integer NOT NULL DEFAULT 0, m text NOT NULL ) WITHOUT OIDS; ALTER TABLE t OWNER TO e; -- Rule: "t_update_00003 ON t" -- DROP RULE t_update_00003 ON t; CREATE OR REPLACE RULE t_update_00003 AS ON INSERT TO t WHERE new.idt >= 1::bigint AND new.idt <= 30000000::bigint DO INSTEAD INSERT INTO t_00003 (idt, idtpadre, e, dmodi, p, m) VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m); -- Rule: "t_update_00006 ON t" -- DROP RULE t_update_00006 ON t; CREATE OR REPLACE RULE t_update_00006 AS ON INSERT TO t WHERE new.idt >= 30000001::bigint AND new.idt <= 60000000::bigint DO INSTEAD INSERT INTO t_00006 (idt, idtpadre, e, dmodi, p, m) VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m); -- Rule: "t_update_00009 ON t" -- DROP RULE t_update_00009 ON t; CREATE OR REPLACE RULE t_update_00009 AS ON INSERT TO t WHERE new.idt >= 60000001::bigint AND new.idt <= 90000000::bigint DO INSTEAD INSERT INTO t_00009 (idt, idtpadre, e, dmodi, p, m) VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m); -- Rule: "t_update_00012 ON t" -- DROP RULE t_update_00012 ON t; CREATE OR REPLACE RULE t_update_00012 AS ON INSERT TO t WHERE new.idt >= 90000001::bigint AND new.idt <= 120000000::bigint DO INSTEAD INSERT INTO t_00012 (idt, idtpadre, e, dmodi, p, m) VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m); etc ... 300 hundred partitions The partitions are created like: CREATE TABLE t_00003 ( CONSTRAINT t_00003_pkey PRIMARY KEY (idt), CONSTRAINT t_00003_idt_check CHECK (idt >= 1::bigint AND idt <= 30000000::bigint) ) INHERITS (t) WITHOUT OIDS; ALTER TABLE t_00003 OWNER TO e; CREATE INDEX t_00003_e ON t_00003 USING btree (e); etc --------------050004000504070400050000 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Scott Marlowe wrote: 
On 10/31/07, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
  
 Steven Flatt wrote:

On 10/30/07, Pablo Alcaraz <pabloa@laotraesquina.com.ar> wrote:
    
I did some testing. I created a 300 partitioned empty table. Then, I
inserted some rows on it and the perfomance was SLOW too.
      
Is the problem with inserting to the partitioned table or selecting from it?
 It sounds like inserting is the problem in which case I ask: how are you
redirecting inserts to the appropriate partition?  If you're using rules,
then insert performance will quickly degrade with number of partitions as
*every* rule needs to be evaluated for *every* row inserted to the base
table.  Using a trigger which you can modify according to some schedule is
much faster, or better yet, use some application-level logic to insert
directly to the desired partition.

Steve I was a program inserting into the base table. The program ran in 200+
threads and every thread insert data on it. Every thread inserts a row every
3 seconds aprox.(or they used to do it), but when I put more partitions the
insert speed went to 1 insert every 2 minutes.

 The selects that need to evaluate all partitions were slow too, but I think
I can wait for them. :D

 I wonder if the update are slow too. I do not know that.

 Do I need to do a trigger for insert only or I need a trigger to update and
delete too?
    

You need a trigger for any update / delete / insert you don't want to
be really slow.  Basically, if a rule is doing it now, you need a
trigger to do it to speed it up.

My experience has been that at 200 to 1000 partitions, the speed of
the smaller tables still makes selects faster than with one big table
for certain kinds of access.  At some point, the cost of planning a
lookup against thousands of tables will be more than the savings of
looking in a really small table.

The nice thing about triggers is that you can use maths to figure out
the name of the table you'll be writing to instead of a static table
like most rules use.  So, all you have to do is make sure the new
tables get added under the parent and poof, you're ready to go, no
need for a new trigger.

Currently I have a insert rule only and the updates are right solved. I think the UPDATEs use the constraint because the program use the base table everywhere.

This is the base table structure:

-- Table: t

-- DROP TABLE t;

CREATE TABLE t
(
  idt bigint NOT NULL,
  idtpadre bigint NOT NULL,
  e integer NOT NULL,
  dmodi timestamp without time zone NOT NULL DEFAULT now(),
  p integer NOT NULL DEFAULT 0,
  m text NOT NULL
)
WITHOUT OIDS;
ALTER TABLE t OWNER TO e;



-- Rule: "t_update_00003 ON t"

-- DROP RULE t_update_00003 ON t;

CREATE OR REPLACE RULE t_update_00003 AS
    ON INSERT TO t
   WHERE new.idt >= 1::bigint AND new.idt <= 30000000::bigint DO INSTEAD  INSERT INTO t_00003 (idt, idtpadre, e, dmodi, p, m)
  VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m);

-- Rule: "t_update_00006 ON t"

-- DROP RULE t_update_00006 ON t;

CREATE OR REPLACE RULE t_update_00006 AS
    ON INSERT TO t
   WHERE new.idt >= 30000001::bigint AND new.idt <= 60000000::bigint DO INSTEAD  INSERT INTO t_00006 (idt, idtpadre, e, dmodi, p, m)
  VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m);

-- Rule: "t_update_00009 ON t"

-- DROP RULE t_update_00009 ON t;

CREATE OR REPLACE RULE t_update_00009 AS
    ON INSERT TO t
   WHERE new.idt >= 60000001::bigint AND new.idt <= 90000000::bigint DO INSTEAD  INSERT INTO t_00009 (idt, idtpadre, e, dmodi, p, m)
  VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m);

-- Rule: "t_update_00012 ON t"

-- DROP RULE t_update_00012 ON t;

CREATE OR REPLACE RULE t_update_00012 AS
    ON INSERT TO t
   WHERE new.idt >= 90000001::bigint AND new.idt <= 120000000::bigint DO INSTEAD  INSERT INTO t_00012 (idt, idtpadre, e, dmodi, p, m)
  VALUES (new.idt, new.idtpadre, new.e, new.dmodi, new.p, new.m);

etc ... 300 hundred partitions


The partitions are created like:

CREATE TABLE t_00003
(
  CONSTRAINT t_00003_pkey PRIMARY KEY (idt),
  CONSTRAINT t_00003_idt_check CHECK (idt >= 1::bigint AND idt <= 30000000::bigint)
) INHERITS (t)
WITHOUT OIDS;
ALTER TABLE t_00003 OWNER TO e;

CREATE INDEX t_00003_e
  ON t_00003
  USING btree
  (e);

etc



--------------050004000504070400050000--