I have a 75GB table with more than 100M records. I've partitioned that table by month. Each partition is ~8M records big.
Most of the queries works on recent data and almost every query filters by the timestamp field used for partitioning. Also, monthly, old (more than 2 years) data is deleted. So it seemed to me that partitioning could help both query performance and old data deletion.
I followed this https://www.postgresql.org/docs/9.5/static/ddl-partitioning.html to partition the tables (the case there is very similar to mine)
Every child table has a CHECK constraint
CONSTRAINT p_inbox_201702_datarilev_check CHECK (datarilev >= '2017-02-01'::date AND datarilev < '2017-03-01'::date)
and an index on the timestamp field (
I've also dropped all the indexes from the parent table (it doesn't seem to make any difference).
Now, if I query the parent table using a date range :
select id from p_inbox where datarilev > '2017-02-02'::date and datarilev < '2017-02-03'::date limit 10;
it takes ~ 10 seconds.
If I query a single partition
select id from p_inbox_201702 where datarilev > '2017-02-02'::date and datarilev < '2017-02-03'::date limit 10
it takes 50 milliseconds.
If I query the non-partitioned table
select id from p_inbox_full where datarilev between '2017-02-02' and '2017-02-03'
it takes 200 milliseconds.
These are the results from an explain analyze of the three queries:
Query the parent: https://explain.depesz.com/s/EOoW
Query the partition: https://explain.depesz.com/s/MMnT
Query the full, non-partitioned table: https://explain.depesz.com/s/Bk4Z
It seems that the partitions are excluded correctly: disabling constraint_exclusion, the plan changes accordingly.
So, my question is: is it normal that checking the constraints to exclude out of range tables takes 10 seconds?
Version is PostgreSQL 9.5.7 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit