Received: from localhost (unknown [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id 894D99FC60F
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 31 Oct 2007 13:14:37 -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 52879-02-6
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Wed, 31 Oct 2007 13:14: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 4869B9FC4F4
	for <pgsql-performance@postgresql.org>;
	Wed, 31 Oct 2007 12:27:49 -0300 (ADT)
Received: (qmail 5113 invoked by uid 0); 31 Oct 2007 15:27:47 -0000
Received: from 72.15.64.64 by linus.numail.org (envelope-from
	<pabloa@laotraesquina.com.ar>, 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.157203 secs); 31 Oct 2007 15:27:47 -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 15:27:47 -0000
Message-ID: <47289EEE.50008@laotraesquina.com.ar>
Date: Wed, 31 Oct 2007 11:27:42 -0400
From: Pablo Alcaraz <pabloa@laotraesquina.com.ar>
User-Agent: Thunderbird 2.0.0.6 (Windows/20070728)
MIME-Version: 1.0
To: Steven Flatt <steven.flatt@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: tables with 300+ partitions
References: <47277341.7070704@laotraesquina.com.ar>
	<357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com>
In-Reply-To: <357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com>
Content-Type: multipart/alternative;
	boundary="------------020402080703090101070906"
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200710/375
X-Sequence-Number: 27397

This is a multi-part message in MIME format.
--------------020402080703090101070906
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Steven Flatt wrote:
> On 10/30/07, *Pablo Alcaraz* <pabloa@laotraesquina.com.ar 
> <mailto: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?

To modify the appilication logic for this is not an options because they 
need to open the program, modify it and retest. All because an 
implementation problem. I prefer to try to solve it at the database 
level because the database need this table partitioned.

Thanks for your help

Regards.

Pablo
>  


--------------020402080703090101070906
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Steven Flatt wrote:
<blockquote
 cite="mid:357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com"
 type="cite">
  <div><span class="gmail_quote">On 10/30/07, <b
 class="gmail_sendername">Pablo Alcaraz</b> &lt;<a
 moz-do-not-send="true" href="mailto:pabloa@laotraesquina.com.ar">pabloa@laotraesquina.com.ar</a>&gt;
wrote:</span>
  <blockquote class="gmail_quote"
 style="border-left: 1px solid rgb(204, 204, 204); margin: 0px 0px 0px 0.8ex; padding-left: 1ex;">I
did some testing. I created a 300 partitioned empty table. Then, I<br>
inserted some rows on it and the perfomance was SLOW too.
  </blockquote>
  <div>&nbsp;</div>
  <div>Is the problem with inserting to the partitioned table or
selecting from it?&nbsp; It sounds like inserting is the problem in which
case I ask: how are you redirecting inserts to the appropriate
partition?&nbsp; 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.&nbsp; 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.
  </div>
  <div>&nbsp;</div>
  <div>Steve</div>
  </div>
</blockquote>
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.<br>
<br>
The selects that need to evaluate all partitions were slow too, but I
think I can wait for them. :D<br>
<br>
I wonder if the update are slow too. I do not know that.<br>
<br>
Do I need to do a trigger for insert only or I need a trigger to update
and delete too?<br>
<br>
To modify the appilication logic for this is not an options because
they need to open the program, modify it and retest. All because an
implementation problem. I prefer to try to solve it at the database
level because the database need this table partitioned.<br>
<br>
Thanks for your help<br>
<br>
Regards.<br>
<br>
Pablo<br>
<blockquote
 cite="mid:357fa7590710301400x3a05796dle6bae219a1a25fc@mail.gmail.com"
 type="cite">
  <div>
  <div>&nbsp;</div>
  </div>
</blockquote>
<br>
</body>
</html>

--------------020402080703090101070906--