Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 41CE4133616B for ; Mon, 28 Feb 2011 09:10:06 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 46369-02 for ; Mon, 28 Feb 2011 13:09:57 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mimolette.dalibo.net (unknown [212.85.154.222]) by mail.postgresql.org (Postfix) with ESMTP id 8CF101337C05 for ; Mon, 28 Feb 2011 09:09:56 -0400 (AST) Received: from marco-dalibo.localnet (gev44-1-78-228-108-65.fbx.proxad.net [78.228.108.65]) by mimolette.dalibo.net (Postfix) with ESMTPSA id AAA4DD83EB; Mon, 28 Feb 2011 14:09:56 +0100 (CET) From: Marc Cousin To: Heikki Linnakangas Subject: Re: inheritance: planning time vs children number vs column number Date: Mon, 28 Feb 2011 14:09:55 +0100 User-Agent: KMail/1.13.6 (Linux/2.6.37-ARCH; KDE/4.6.0; x86_64; ; ) Cc: pgsql-performance@postgresql.org References: <201102281038.04945.cousinmarc@gmail.com> <4D6B9BC9.1000404@enterprisedb.com> In-Reply-To: <4D6B9BC9.1000404@enterprisedb.com> X-KMail-Markup: true MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="Boundary-01=_j65aNHEWZYGHReY" Content-Transfer-Encoding: 7bit Message-Id: <201102281409.55827.cousinmarc@gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.105 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RDNS_NONE=0.793 X-Spam-Level: X-Archive-Number: 201102/526 X-Sequence-Number: 42664 --Boundary-01=_j65aNHEWZYGHReY Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable 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 =3D 1 constan= t, > > always the same column). > >=20 > > 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) > >=20 > > 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. > >=20 > > Constraint exclusion had a limited impact on planning time (it added > > between 20% and 100% planning time when there were many columns). >=20 > 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=E2=80=A6=20 If you measure the planning time, modifying either the columns or the=20 partitions number, the square root of the planning time is almost perfectly= =20 proportional with the parameter you're playing with. --Boundary-01=_j65aNHEWZYGHReY Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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

> > p= artitioned configuration. The amount of partitions wasn't completely

> > c= razy, around 500, not in the thousands. The problem was that there were

> > n= early 1000 columns in the parent table (very special use case, there is

> > a= reason for this application for having these many columns). The check

> > c= onstraint was extremely simple (for each child, 1 column =3D 1 constant,

> > a= lways the same column).

> > <= /p>

> > A= s I was surprised by this very large planning time, I have been trying

> > t= o study the variation of planning time against several parameters: -

> > n= umber of columns

> > -= number of children tables

> > -= constraint exclusion's value (partition or off)

> > <= /p>

> > W= hat (I think) I measured is that the planning time seems to be O(n^2)

> > f= or the number of columns, and O(n^2) for the number of children tables.

> > <= /p>

> > C= onstraint exclusion had a limited impact on planning time (it added

> > b= etween 20% and 100% planning time when there were many columns).

> Testin= g 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=E2=80=A6

If you meas= ure the planning time, modifying either the columns or the partitions numbe= r, the square root of the planning time is almost perfectly proportional wi= th the parameter you're playing with.

--Boundary-01=_j65aNHEWZYGHReY--