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 (datarilev).

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

  • 1
    How many partitions do you have? Postgres is known to be not very efficient if you have several thousands of partitions. Can you try the same queries on 9.6? I think there were some improvements to the optimizer when dealing with many partitions – a_horse_with_no_name Aug 29 '17 at 14:32
  • 1
    On the parent, have you tried where datarilev between '2017-02-02 00:00:00' and '2017-02-03 00:00:00', or creating an index for each partition on datarilev::DATE? – bma Aug 29 '17 at 14:39
  • 1
    A LIMIT without an ORDER BY is rarely a sensible thing to do. Is that what you actually want, or is that the result of some attempted optimization? – jjanes Aug 29 '17 at 17:18
  • 1
    Can you post an explain (analyze, buffers) of the parent query, preferably with track_io_timing turned on? Something seems very wrong with your index p_inbox_201702_darilev. – jjanes Aug 29 '17 at 17:57
  • 1
    For a 75 GB table I would have start with partial indexes with clustered table. – Sahap Asci Aug 29 '17 at 18:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.