From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
To: pgsql-performance@postgresql.org
Subject: Re: Partitioned table performance
Date: Sun, 5 Dec 2004 15:06:40 -0800
User-Agent: KMail/1.6.2
Cc: "Stacy White" <harsh@computer.org>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
In-Reply-To: <000a01c4da74$84e072e0$0200a8c0@grownups>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200412051506.41025.josh@agliodbs.com>

Stacy,

Thanks for the stats!

> In some cases we've seen some increased performance in tests by splitting
> the table into several smaller tables. =A0Both 'UNION ALL' views, and the
> superclass/subclass scheme work well at pruning down the set of rows a
> query uses, but they seem to introduce a large performance hit to the time
> to process each row (~50% for superclass/subclass, and ~150% for union
> views).

This seems reasonable, actually, given your test.   Really, what you should=
 be=20
comparing it against is not against selecting from an individual partition,=
=20
but selecting from the whole business as one large table. =20

I also suspect that wider rows results in less overhead proportionally; not=
e=20
that your test contains *only* the indexed rows.   I should soon have a tes=
t=20
to prove this, hopefully.

However, I would be interested in seeing EXPLAIN ANALYZE from your tests=20
rather than just EXPLAIN.

=2D-=20
Josh Berkus
Aglio Database Solutions
San Francisco