From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Cc: Stacy White <harsh@computer.org>
Subject: Re: Partitioned table performance
Date: Fri, 10 Dec 2004 21:52:40 -0800
Message-ID: <200412102152.40442.josh@agliodbs.com> (raw)
In-Reply-To: <001b01c4dcde$fc27be50$0200a8c0@grownups>
References: <000a01c4da74$84e072e0$0200a8c0@grownups>
<200412051506.41025.josh@agliodbs.com>
<001b01c4dcde$fc27be50$0200a8c0@grownups>
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'. The bar* columns are all
> set to non-null values. The 'one_big_foo' table stores all 2M rows in one
> table. '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-partitioning
doesn't pay for having only 2 partitions. Examine this:
-> Index Scan using idx_sub_foo2_partition on sub_foo2
super_foo (cost=0.00..2.01 rows=1 width=4) (actual time=0.221..0.221
rows=0 loops=1)
Index Cond: (partition = 1::numeric)
Total runtime: 15670.463 ms
As you see, even though the aggregate operation requires a seq scan, the
planner is still able to scan, and discard, sub_foo2, using its index in 0.2
seconds. Unfortunately, super_foo still needs to contend with:
-> Append (cost=0.00..28376.79 rows=1000064 width=4) (actual
time=6.699..12072.483 rows=1000000 loops=1)
Right there, in the Append, you lose 6 seconds. This means that
pseudo-partitioning via inheritance will become a speed gain once you can
"make up" that 6 seconds by being able to discard more partitions. If you
want, do a test with 6 partitions instead of 2 and let us know how it comes
out.
Also, keep in mind that there are other reasons to do pseudo-partitioning than
your example. Data write performance, expiring partitions, and vacuum are
big reasons that can motivate partitioning even in cases when selects are
slower.
--
Josh Berkus
Aglio Database Solutions
San Francisco
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: josh@agliodbs.com, harsh@computer.org
Subject: Re: Partitioned table performance
In-Reply-To: <200412102152.40442.josh@agliodbs.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