Message-ID: <4D6B9BC9.1000404@enterprisedb.com>
Date: Mon, 28 Feb 2011 14:57:45 +0200
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI;
	rv:1.9.1.16) Gecko/20101226 Icedove/3.0.11
MIME-Version: 1.0
To: Marc Cousin <cousinmarc@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: inheritance: planning time vs children number vs column
 number
References: <201102281038.04945.cousinmarc@gmail.com>
In-Reply-To: <201102281038.04945.cousinmarc@gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 28.02.2011 11:38, Marc Cousin wrote:
> I've been facing a very large (more than 15 seconds) planning time in a
> partitioned configuration. The amount of partitions wasn't completely crazy,
> around 500, not in the thousands. The problem was that there were nearly 1000
> columns in the parent table (very special use case, there is a reason for this
> application for having these many columns). The check constraint was extremely
> simple (for each child, 1 column = 1 constant, always the same column).
>
> As I was surprised by this very large planning time, I have been trying to
> study the variation of planning time against several parameters:
> - number of columns
> - number of children tables
> - constraint exclusion's value (partition or off)
>
> What (I think) I measured is that the planning time seems to be O(n^2) for the
> number of columns, and O(n^2) for the number of children tables.
>
> Constraint exclusion had a limited impact on planning time (it added between
> 20% and 100% planning time when there were many columns).

Testing here with a table with 1000 columns and 100 partitions, about 
80% of the planning time is looking up the statistics on attribute 
width, to calculate average tuple width. I don't see O(n^2) behavior, 
though, it seems linear.

> I'd like to know if this is a known behavior ? And if I could mitigate it
> somehow ?

I'm out of ideas on how to make it faster, I'm afraid.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com