Re: inheritance: planning time vs children number vs column number

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: Marc Cousin <cousinmarc(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: inheritance: planning time vs children number vs column number
Date: 2011-02-28 12:57:45
Message-ID: 4D6B9BC9.1000404@enterprisedb.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Marc Cousin 2011-02-28 13:09:55 Re: inheritance: planning time vs children number vs column number
Previous Message Heikki Linnakangas 2011-02-28 11:30:48 Re: Performance Test for PostgreSQL9