Performance Testing on Partition Table In PostgreSQL – Part 3

I will make a performance test between partition table and non partition table.

Here are two tables both contains 200 millions of data.

1) hashvalue_pt (Partiton tables In Month)
2) hashvalue (Non Partiton tables)

P.S Please visit Partition Table In PostgreSQL (Create Partition) – Part 1 If you do not know how to do table partition in PostgreSQL, and visit Partition Table In PostgreSQL (Simulate Millions Data) – Part 2 to simulate millions of data to test it.

Performance Testing On Speficied Date

--partition table
SELECT * FROM hashvalue_PT WHERE hashtime = DATE '2008-08-01'

--non partition table
SELECT * FROM hashvalue WHERE hashtime = DATE '2008-08-01'

Here is the performance test result from 1 million to 200 millions data. Please click picture to enlarge performance result.

performance-test-on-partition-table-postgresql-1

When both contains 200 millions of data, search on specified date, partition table is more faster then non-partition table about 144.45%

Search on specified date “2008-08-01”
Records Retrieved = 741825
Partition Table = 359.61 seconds
Non Partition Table = 879.062 seconds

Performance Testing On Range Date Same Month

--partition table
SELECT * FROM hashvalue_PT WHERE  hashtime >= DATE '2008-05-01' AND hashtime <= '2008-05-15'; 

--non partition table
SELECT * FROM hashvalue WHERE hashtime >= DATE '2008-05-01' AND hashtime <= '2008-05-15'; 

Here is the performance test result from 1 million to 200 millions data. Please click picture to enlarge performance result.

performance-test-on-partition-table-postgresql-2

When both contains 200 millions of data, search on range date in same month, partition table is more faster then non-partition table about 209.84%

Search on range date in same month from ‘2008-05-01’ to ‘2008-05-15’;
Records Retrieved = 11112338
Partition Table = 603.328 seconds
Non Partition Table = 1869.375 seconds

Performance Testing On Range Date Cross Month

--partition table
SELECT * FROM hashvalue_PT WHERE  hashtime >= DATE '2008-06-20' AND hashtime <= '2008-07-10'; 

--non partition table
SELECT * FROM hashvalue WHERE hashtime >= DATE '2008-06-20' AND hashtime <= '2008-07-10';

Here is the performance test result from 1 million to 200 millions data. Please click picture to enlarge performance result.

performance-test-on-partition-table-postgresql-3

When both contains 200 millions of data, search on range date and cross month, partition table is more faster then non-partition table about 39.97%

Search on range date and cross month from ‘2008-06-20’ to ‘2008-07-10’;
Records Retrieved = 12220658
Partition Table = 1310.766 seconds
Non Partition Table = 1834.743 seconds

Conclusion

After a few performance test between partition and non partition table, result is obviously, partition table is totally win in this performance test.

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
7 Comment threads
1 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
7 Comment authors
j vbKrishna MallikloshoRob Rasner Magic CastleVincenzo Romano Recent comment authors
newest oldest most voted
j vb
Guest
j vb

It does a full table scan if you pass values via Join :(

e.g. select from hashvalue_PT a, time b WHERE a.hashtime = b.time and b.timeid = 20

Krishna Mallik
Guest
Krishna Mallik

Neat article, thanks for posting! :)

losho
Guest
losho

Nice article. I would like to add that partitioning sometimes might be slower. What would happen in case when you have another range between dates? For example:
SELECT * FROM hashvalue_PT WHERE hashtime >= DATE ‘2008-01-20’ AND hashtime <= '2008-07-10';

It will use 7 indexes (partition table) instead of one (non-partitioned table). So be careful when and how to use partitioning :).

Rob Rasner Magic Castle
Guest
Rob Rasner Magic Castle

Man! The site is the best Performance Testing on Partition Table In PostgreSQL – Part 3 Exactly how did one cause it to be seem this great … Rgds ! Rob Rasner Magic Castle

Vincenzo Romano
Guest
Vincenzo Romano

Testing the SELECTs is just 50% of the story!
Why not testing also the INSERTs?
Inserting 200+M lines in a partitioned table is also a relevant activity, though.
Maybe my article at http://notsononsense.blogspot.com/2009/12/postgresql-different-approach-to-table.html can shed some light.
Your study is very good, anyway. I would also mention the “constraint exclusion” thing, which actually explains the behaviour.

Greya
Guest
Greya

Good article, i am interested in insert/update speed for partitioned tables.

Vincenzo Romano
Guest
Vincenzo Romano
migovigo
Guest
migovigo

Great :D