Skip site navigation (1) Skip section navigation (2)

Re: Partitioned table performance

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: "Stacy White" <harsh(at)computer(dot)org>
Subject: Re: Partitioned table performance
Date: 2004-12-11 05:52:40
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

> 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 

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 

Josh Berkus
Aglio Database Solutions
San Francisco

In response to


pgsql-performance by date

Next:From: Tomas =?iso-8859-1?q?Sk=E4re?=Date: 2004-12-11 14:17:13
Subject: Re: Query is not using index when it should
Previous:From: Josh BerkusDate: 2004-12-11 05:40:18
Subject: Re: LIMIT causes SEQSCAN in subselect

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group