From: Marc Cousin <cousinmarc@gmail.com>
To: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: inheritance: planning time vs children number vs column number
Date: Mon, 28 Feb 2011 14:09:55 +0100
Message-ID: <201102281409.55827.cousinmarc@gmail.com> (raw)
In-Reply-To: <4D6B9BC9.1000404@enterprisedb.com>
References: <201102281038.04945.cousinmarc@gmail.com>
<4D6B9BC9.1000404@enterprisedb.com>
The Monday 28 February 2011 13:57:45, Heikki Linnakangas wrote :
> 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.
It is only based on experimentation, for my part, of course…
If you measure the planning time, modifying either the columns or the
partitions number, the square root of the planning time is almost perfectly
proportional with the parameter you're playing with.
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: cousinmarc@gmail.com, heikki.linnakangas@enterprisedb.com
Subject: Re: inheritance: planning time vs children number vs column number
In-Reply-To: <201102281409.55827.cousinmarc@gmail.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