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

Stacy,

> Each set of test tables holds 1,000,000 tuples with a partition value of
> '1', and 1,000,000 with a partition value of '2'. =A0The bar* columns are=
 all
> set to non-null values. =A0The 'one_big_foo' table stores all 2M rows in =
one
> table. =A0'super_foo' and 'union_foo' split the data into two tables, and=
 use
> inheritance and union views (respectively) to tie them together, as
> described in my previous message.
>
> Query timings and 'EXPLAIN ANALYZE' results for full table scans and for
> partition scans follow:

Hmmm .... interesting.   I think you've demonstrated that pseudo-partitioni=
ng=20
doesn't pay for having only 2 partitions.   Examine this:

=A0 =A0 =A0 =A0 =A0-> =A0Index Scan using idx_sub_foo2_partition on sub_foo2
super_foo =A0(cost=3D0.00..2.01 rows=3D1 width=3D4) (actual time=3D0.221..0=
=2E221
rows=3D0 loops=3D1)
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Index Cond: (partition =3D 1::numeric)
=A0Total runtime: 15670.463 ms

As you see, even though the aggregate operation requires a seq scan, the=20
planner is still able to scan, and discard, sub_foo2, using its index in 0.=
2=20
seconds.  Unfortunately, super_foo still needs to contend with:

=A0 =A0-> =A0Append =A0(cost=3D0.00..28376.79 rows=3D1000064 width=3D4) (ac=
tual
time=3D6.699..12072.483 rows=3D1000000 loops=3D1)

Right there, in the Append, you lose 6 seconds.   This means that=20
pseudo-partitioning via inheritance will become a speed gain once you can=20
"make up" that 6 seconds by being able to discard more partitions.   If you=
=20
want, do a test with 6 partitions instead of 2 and let us know how it comes=
=20
out.

Also, keep in mind that there are other reasons to do pseudo-partitioning t=
han=20
your example.  Data write performance, expiring partitions, and vacuum are=
=20
big reasons that can motivate partitioning even in cases when selects are=20
slower.

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