This article compares the speed and performance of queries between partitioned and non partitioned PostgreSQL tables. However, it is important to remember that the PostgreSQL tables partitioning has also another benefits, than the better performance on queries. More information about other benefits from the first part ‘Howto create PostgreSQL table partitioning (Part 1)‘.

This is comparision between partitioned and non partitioned PostgreSQL tables. The same tests were carried out with and without indices, because using the indices, it is no longer very meaningful example on table, which has one billion rows of data (if the table is not partitioned). This comparison is used an example of an existing table, which looks like this:


               Table "public.impressions_by_day_partitioned"
    Column     |  Type   |              Modifiers               
---------------+---------+--------------------------------------
 advertiser_id | integer | not null
 day           | date    | not null default ('now'::text)::date
 impressions   | integer | not null

Both partitioned and non partitioned tables are identical and tables contains one hundred million rows test data:


SELECT count(*) FROM impressions_by_day_partitioned;
  count   
----------
 100000000
(1 row)

6 Test cases

  • 1a. Select total sum of impressions on January 2009. (Day field indexed)
  • 1b. Select total sum of impressions on January 2009. (Day field not indexed)
  • 2a. Select the number of all advertisers in 2011. (Day field indexed)
  • 2b. Select the number of all advertisers in 2011. (Day field not indexed)
  • 3a. Select MAX impressions on 25 November 2010. (Day field indexed)
  • 3b. Select MAX impressions on 25 November 2010. (Day field not indexed)
  • 4a. Select Top ten advertisers on September 2009. (Day field indexed)
  • 4b. Select Top ten advertisers on September 2009. (Day field not indexed)
  • 5a. Update all impressions to 50 000 on 16 July 2010. (Day field indexed)
  • 5b. Update all impressions to 50 000 on 16 July 2010. (Day field not indexed)
  • 6a. Insert ten rows to table. (Day field indexed)
  • 6b. Insert ten rows to table. (Day field not indexed)

1a. Select total sum of impressions on January 2009. (Day field indexed)

[test]> SELECT SUM(impressions) 
FROM impressions_by_day_partitioned 
WHERE day >= '2009-01-01' AND day < '2009-02-01';
     sum      
--------------
 151715749479
(1 row)

Time: 4441.879 ms
[test]> SELECT SUM(impressions) 
FROM impressions_by_day_non_partitioned 
WHERE day >= '2009-01-01' AND day < '2009-02-01';
     sum      
--------------
 151715749479
(1 row)

Time: 12335.544 ms

1b. Select total sum of impressions on January 2009. (Day field not indexed)

[test]> SELECT SUM(impressions) 
FROM impressions_by_day_partioned 
WHERE day >= '2009-01-01' AND day < '2009-02-01';
     sum      
--------------
 151715749479
(1 row)

Time: 4760.410 ms
[test]> SELECT SUM(impressions) 
FROM impressions_by_day_non_partioned 
WHERE day >= '2009-01-01' AND day < '2009-02-01';
     sum      
--------------
 151715749479
(1 row)

Time: 30011.654 ms

Select-total-sum-of-impressions-on-January-2009

2a. Select the number of all advertisers in 2011. (Day field indexed)

[test]> SELECT count(distinct(advertiser_id)) 
FROM impressions_by_day_partitioned 
WHERE day >= '2011-01-01' AND day < '2012-01-01';
 count  
--------
 998952
(1 row)

Time: 35965.297 ms
[test]> SELECT count(distinct(advertiser_id)) 
FROM impressions_by_day_non_partitioned 
WHERE day >= '2011-01-01' AND day < '2012-01-01';
 count  
--------
 998952
(1 row)

Time: 38207.764 ms

2b. Select the number of all advertisers in 2011. (Day field not indexed)

[test]> SELECT count(distinct(advertiser_id)) 
FROM impressions_by_day_partitioned 
WHERE day >= '2011-01-01' AND day < '2012-01-01';
 count  
--------
 998952
(1 row)

Time: 35994.232 ms
[test]> SELECT count(distinct(advertiser_id)) 
FROM impressions_by_day_non_partitioned 
WHERE day >= '2011-01-01' AND day < '2012-01-01';
 count  
--------
 998952
(1 row)

Time: 54922.130 ms

Select-the-number-of-all-advertisers-in-2011

3a. Select MAX impressions on 25 November 2010. (Day field indexed)

[test]> SELECT MAX(impressions) FROM impressions_by_day_partitioned 
WHERE day = '2010-11-25';
  max   
--------
 100000
(1 row)

Time: 223.741 ms
[test]> SELECT MAX(impressions) FROM impressions_by_day_non_partioned 
WHERE day = '2010-11-25';
  max   
--------
 100000
(1 row)

Time: 795.810 ms

3b. Select MAX impressions on 25 November 2010. (Day field not indexed)

[test]> SELECT MAX(impressions) FROM impressions_by_day 
WHERE day = '2010-11-25';
  max   
--------
 100000
(1 row)

Time: 522.345 ms
[test]> SELECT MAX(impressions) FROM impressions_by_day_test 
WHERE day = '2010-11-25';
  max   
--------
 100000
(1 row)

Time: 27916.619 ms

Select-MAX impressions-on-25-November-2010

4a. Select Top ten advertisers on September 2009. (Day field indexed)

[test]> SELECT advertiser_id, sum(impressions) as impressions 
FROM impressions_by_day_partitioned 
WHERE day >= '2009-09-01' AND day < '2009-10-01' 
GROUP BY advertiser_id ORDER BY impressions DESC LIMIT 10;
 advertiser_id | impressions 
---------------+-------------
        155772 |      424030
        121456 |      409425
         99802 |      402586
        722802 |      391690
        609813 |      387730
        812803 |      387310
        314459 |      382856
        701436 |      380741
         31012 |      378990
        712014 |      378537
(10 rows)

Time: 1563.499 ms
[test]> SELECT advertiser_id, sum(impressions) as impressions 
FROM impressions_by_day_non_partitioned 
WHERE day >= '2009-09-01' AND day < '2009-10-01' 
GROUP BY advertiser_id ORDER BY impressions DESC LIMIT 10;
 advertiser_id | impressions 
---------------+-------------
        155772 |      424030
        121456 |      409425
         99802 |      402586
        722802 |      391690
        609813 |      387730
        812803 |      387310
        314459 |      382856
        701436 |      380741
         31012 |      378990
        712014 |      378537
(10 rows)

Time: 3824.523 ms

4b. Select Top ten advertisers on September 2009. (Day field not indexed)

[test]> SELECT advertiser_id, sum(impressions) as impressions 
FROM impressions_by_day_partitioned 
WHERE day >= '2009-09-01' AND day < '2009-10-01' 
GROUP BY advertiser_id ORDER BY impressions DESC LIMIT 10;
 advertiser_id | impressions 
---------------+-------------
        155772 |      424030
        121456 |      409425
         99802 |      402586
        722802 |      391690
        609813 |      387730
        812803 |      387310
        314459 |      382856
        701436 |      380741
         31012 |      378990
        712014 |      378537
(10 rows)

Time: 1592.145 ms
[test]> SELECT advertiser_id, sum(impressions) as impressions 
FROM impressions_by_day_non_partitioned 
WHERE day >= '2009-09-01' AND day < '2009-10-01' 
GROUP BY advertiser_id ORDER BY impressions DESC LIMIT 10;
 advertiser_id | impressions 
---------------+-------------
        155772 |      424030
        121456 |      409425
         99802 |      402586
        722802 |      391690
        609813 |      387730
        812803 |      387310
        314459 |      382856
        701436 |      380741
         31012 |      378990
        712014 |      378537
(10 rows)

Time: 17168.329 ms

Select-Top-ten-advertisers-on-September-2009

5a. Update all impressions to 50 000 on 16 July 2010. (Day field indexed)

[test]> UPDATE impressions_by_day_partitioned SET impressions = 50000 
WHERE day = '2010-07-16';
UPDATE 27004
Time: 86416.192 ms
[test]> UPDATE impressions_by_day_non_partitioned SET impressions = 50000 
WHERE day = '2010-07-16';
UPDATE 27004
Time: 68784.290 ms

5b. Update all impressions to 50 000 on 16 July 2010. (Day field not indexed)

UPDATE impressions_by_day_partitioned SET impressions = 50000 
WHERE day = '2010-07-16';
UPDATE 27004
Time: 93843.251 ms
[test]> UPDATE impressions_by_day_non_partitioned SET impressions = 50000 
WHERE day = '2010-07-16';
UPDATE 27004
Time: 317275.538 ms

Update-all-impressions-to-50000-on-16-July-2010

6a. Insert ten rows to table. (Day field indexed)

[test]> INSERT INTO impressions_by_day_partitioned VALUES 
(1001, '2009-01-08', 75455), 
(10431, '2010-11-04', 47457),
(1241, '2011-07-04', 3457),
(5353, '2009-06-01', 43357),
(3636, '2012-09-16', 8755),
(3939, '2009-11-19', 5454),
(7766, '2010-02-21', 8944),
(44676, '2012-04-26', 24110),
(5467, '2011-08-24', 1477),
(3243, '2009-11-20', 65478);
INSERT 0 0
Time: 892.246 ms
[test]> INSERT INTO impressions_by_day_non_partitioned VALUES 
(1001, '2009-01-08', 75455), 
(10431, '2010-11-04', 47457),
(1241, '2011-07-04', 3457),
(5353, '2009-06-01', 43357),
(3636, '2012-09-16', 8755),
(3939, '2009-11-19', 5454),
(7766, '2010-02-21', 8944),
(44676, '2012-04-26', 24110),
(5467, '2011-08-24', 1477),
(3243, '2009-11-20', 65478);
INSERT 0 10
Time: 205.299 ms

6b. Insert ten rows to table. (Day field not indexed)

[test]> INSERT INTO impressions_by_day_partitioned VALUES 
(1001, '2009-01-08', 75455), 
(10431, '2010-11-04', 47457),
(1241, '2011-07-04', 3457),
(5353, '2009-06-01', 43357),
(3636, '2012-09-16', 8755),
(3939, '2009-11-19', 5454),
(7766, '2010-02-21', 8944),
(44676, '2012-04-26', 24110),
(5467, '2011-08-24', 1477),
(3243, '2009-11-20', 65478);
INSERT 0 0
Time: 886.146 ms
[test]> INSERT INTO impressions_by_day_non_partitioned VALUES 
(1001, '2009-01-08', 75455), 
(10431, '2010-11-04', 47457),
(1241, '2011-07-04', 3457),
(5353, '2009-06-01', 43357),
(3636, '2012-09-16', 8755),
(3939, '2009-11-19', 5454),
(7766, '2010-02-21', 8944),
(44676, '2012-04-26', 24110),
(5467, '2011-08-24', 1477),
(3243, '2009-11-20', 65478);
INSERT 0 10
Time: 204.894 ms

Insert-ten-rows-to-table

Summary

A few test queries simply shows the fact that a Select queries is faster to partitioned table than the non-partitioned table. Is easily seen by the fact that Updates and Inserts slowed little bit on partitioned table.

These differences are much clearer on Select queries, if the amount of data is much higher. At a certain point indexing is not working anymore, because index size is too big. Then the only sensible option is partitioning.