Speeding up partitions

Every major release of PostgreSQL provides the end user with improved partitioning. The same holds true for PostgreSQL 12. This time, partition pruning has been speeded up dramatically. Why should we care? If you have got more than just a handful of partitions, fast removal of partitions during planning and execution is vital to ensure that the overhead of partitioning does not go through the roof. 

Let's do a small test and see what happens. Let's execute the following SQLs in PostgreSQL 11 as well as in PostgreSQL 12:

test=# CREATE TABLE part (id int) PARTITION BY RANGE (id);
CREATE TABLE

First of all, a normal range partitioned table is created. The second challenge is to create a really large number of partitions to do our test. The easiest way to achieve that is to generate the desired SQL commands using plain SQL, as shown in the next example. Make sure that this SQL statement is executed on PostgreSQL 11 as well as PostgreSQL 12:

test=# SELECT 'CREATE TABLE part_' || id || ' PARTITION OF part 
 FOR VALUES FROM (' || id || ') TO (' || id + 1 || ')' 
 FROM generate_series(1, 1000) AS id;
 ?column? 
------------------------------------------------------------------
 CREATE TABLE part_1 PARTITION OF part FOR VALUES FROM (1) TO (2)
 CREATE TABLE part_2 PARTITION OF part FOR VALUES FROM (2) TO (3)
 CREATE TABLE part_3 PARTITION OF part FOR VALUES FROM (3) TO (4)
...

The SQL statement will create 1,000 SQL statements to create partitions. The beauty now is that psql has the builting \gexec command. resultset that was just created will be seen as SQL input. In my judgment, this is the easiest way to create large numbers of tables:

test=# \gexec
CREATE TABLE
CREATE TABLE
CREATE TABLE

Now that 1,000 partitions should be in both databases, we can try to compare the results. To do that, I am using a simple explain analyze statement, which is totally sufficient to prove my point:

test=# explain analyze SELECT * FROM part WHERE id = 545;
 QUERY PLAN  
---------------------------------------------------------------
 Append (cost=0.00..41.94 rows=13 width=4) 
 (actual time=0.029..0.029 rows=0 loops=1)
 -> Seq Scan on part_545 (cost=0.00..41.88 rows=13 width=4) 
 (actual time=0.028..0.028 rows=0 loops=1)
 Filter: (id = 545)
 Planning Time: 17.747 ms
 Execution Time: 0.057 ms
(5 rows)

In PostgreSQL 11, the planner needs 17.747 ms to come up with the desired plan. Running the same in PostgreSQL 12 will show somewhat better results:

test=# explain analyze SELECT * FROM part WHERE id = 545;
 QUERY PLAN 
----------------------------------------------------------
 Seq Scan on part_545 (cost=0.00..41.88 rows=13 width=4) 
 (actual time=0.005..0.005 rows=0 loops=1)
 Filter: (id = 545)
 Planning Time: 0.146 ms
 Execution Time: 0.029 ms
(4 rows)

Wow! The time needed by the planner has increased by more than 100 times. The more partitions we have, the more important this feature is going to be in a real-world environment.