Main Tutorials

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 Author

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

Comments

Subscribe
Notify of
8 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
j vb
7 years ago

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
8 years ago

Neat article, thanks for posting! 🙂

losho
11 years ago

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
12 years ago

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
14 years ago

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
14 years ago

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

Vincenzo Romano
14 years ago
Reply to  Greya
migovigo
14 years ago

Great 😀