From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
To: Marc Cousin <cousinmarc@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: inheritance: planning time vs children number vs column number
Date: Mon, 28 Feb 2011 14:57:45 +0200
Message-ID: <4D6B9BC9.1000404@enterprisedb.com> (raw)
In-Reply-To: <201102281038.04945.cousinmarc@gmail.com>
References: <201102281038.04945.cousinmarc@gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki.linnakangas@enterprisedb.com, cousinmarc@gmail.com
Subject: Re: inheritance: planning time vs children number vs column number
In-Reply-To: <4D6B9BC9.1000404@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox