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.
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.
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.
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.
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
Neat article, thanks for posting! 🙂
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 :).
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
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.
Good article, i am interested in insert/update speed for partitioned tables.
Try my approach at http://notsononsense.blogspot.com/2009/12/postgresql-different-approach-to-table.html
Great 😀