Post date: Mar 4, 2015 2:34:51 PM
Using the partitions created in this post: http://www.peterhenell.se/msg/PostgreSQL---Automatic-Partition-creation-script
We now want to reap the benefits of partitioning!
First thing we want to do is to check what the maximum value of 'b' is in the last month.
select max(b) from customer where b > now() - interval '1 month';
But wait, why is it taking so long to execute? The query plan will tell us why.
Aggregate (cost=3326.43..3326.44 rows=1 width=8) -> Append (cost=0.00..3208.35 rows=47232 width=8) -> Seq Scan on customer (cost=0.00..0.00 rows=1 width=8) Filter: (b > (now() - '1 mon'::interval)) -> Seq Scan on customer_201001 (cost=0.00..43.95 rows=647 width=8) Filter: (b > (now() - '1 mon'::interval)) -> Seq Scan on customer_201002 (cost=0.00..43.95 rows=647 width=8) Filter: (b > (now() - '1 mon'::interval)) -> Seq Scan on customer_201003 (cost=0.00..43.95 rows=647 width=8) Filter: (b > (now() - '1 mon'::interval)) .. snip hundreds of rows of seq scans -> Seq Scan on customer_201512 (cost=0.00..43.95 rows=647 width=8) Filter: (b > (now() - '1 mon'::interval)) -> Seq Scan on customer_201601 (cost=0.00..43.95 rows=647 width=8) Filter: (b > (now() - '1 mon'::interval))
(the table is obviously empty so you need to imagine how slow it would be with a billion records. The core problem remain the same anyway)
Notice how our query is scanning all the partitions from 2010 and forward. Even though our current date is 2015.
Our query failed to achieve partition exclusion and forced Postgres to fully scan all of the partitions.
The reason for this is that in order to achieve partition exclusion - the filter on b in the where-clause must only contain constant values. NOW and CURRENT_DATE etc are volatile and will prevent any partition exclusion.
One solution is to run the query as dynamic SQL. A better and cleaner approach is to use prepared statements:
PREPARE query(timestamp) as select max(b) from customer where b > $1 - interval '1 months'; EXPLAIN ANALYZE EXECUTE query(current_date); -- You should deallocate your statement when you are done using it. DEALLOCATE query;
This query plan will show that it is only scanning partitions February and forward (because this blog post was written in march)