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——————————–

1
2
3
4
5

--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——————————–

1
2
3
4
5

--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——————————–

1
2
3
4
5

--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

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