DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=from:to:subject:date:user-agent:cc:references:in-reply-to
	:mime-version:content-type:content-transfer-encoding:message-id;
	b=HhSbhjELIpInxR4jr4aXUQmvO3sjbs6ndMjEi1tN+ViU80hqp30YjaYlI9fuj3j9bN
	zUFcKuw8Ry8IPs10bXHQHixOUyY5iKmTvTMC9jjh669AMjEwkEPqMGW62l2G5IZgp7Ar
	Xbnv4BZT0yuqA4FO07JPHpfu8j4WM5cdqa5yk=
From: Marc Cousin <cousinmarc@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: inheritance: planning time vs children number vs column number
Date: Tue, 1 Mar 2011 08:42:42 +0100
User-Agent: KMail/1.13.6 (Linux/2.6.37-ARCH; KDE/4.6.0; x86_64; ; )
Cc: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>,
	pgsql-performance@postgresql.org
References: <201102281038.04945.cousinmarc@gmail.com>
	<201102281947.46590.cousinmarc@gmail.com>
	<4188.1298960419@sss.pgh.pa.us>
In-Reply-To: <4188.1298960419@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Message-Id: <201103010842.42128.cousinmarc@gmail.com>

Le mardi 01 mars 2011 07:20:19, Tom Lane a =C3=A9crit :
> Marc Cousin <cousinmarc@gmail.com> writes:
> > The Monday 28 February 2011 16:35:37, Tom Lane wrote :
> >> Could we see a concrete example demonstrating that?  I agree with Heik=
ki
> >> that it's not obvious what you are testing that would have such
> >> behavior. I can think of places that would have O(N^2) behavior in the
> >> length of the targetlist, but it seems unlikely that they'd come to
> >> dominate runtime at a mere 1000 columns.
> >=20
> > I feel a little silly not having provided a test case from the start=D9=
=82=C2=80=EF=BF=BD
> >=20
> > A script doing a complete test is attached to this email.
>=20
> I did some oprofile analysis of this test case.  It's spending
> essentially all its time in SearchCatCache, on failed searches of
> pg_statistic.  The cache accumulates negative entries for each probed
> column, and then the searches take time proportional to the number of
> entries, so indeed there is an O(N^2) behavior --- but N is the number
> of columns times number of tables in your test case, not just the number
> of columns.
>=20
> The cache is a hash table, so ideally the search time would be more or
> less constant as the table grows, but to make that happen we'd need to
> reallocate with more buckets as the table grows, and catcache.c doesn't
> do that yet.  We've seen a few cases that make that look worth doing,
> but they tend to be pretty extreme, like this one.
>=20
> It's worth pointing out that the only reason this effect is dominating
> the runtime is that you don't have any statistics for these toy test
> tables.  If you did, the cycles spent using those entries would dwarf
> the lookup costs, I think.  So it's hard to get excited about doing
> anything based on this test case --- it's likely the bottleneck would be
> somewhere else entirely if you'd bothered to load up some data.
>=20
> 			regards, tom lane

Yes, for the same test case, with a bit of data in every partition and=20
statistics up to date, planning time goes from 20 seconds to 125ms for the =
600=20
children/1000 columns case. Which is of course more than acceptable.

Now I've got to check it's the same problem on the real environment. I thin=
k=20
it has quite a few empty partitions, so no statistics for them=E2=80=A6

Thanks a lot.

Marc