Postgresql partitioning queries benefits

Question

I am trying to apply partitioning process to some large tables on my Postgresql database, I have read the documentation and many articles about that and could not found an answer to this question.

If I use the range partitioning on for example a Date field, is it necessary to do queries with this field in order to get the benefits of the partition process or can I have this even if I do queries using other fields not included in partitioning fields condition ?

Thanks

Answer 1

To take advantage of the table partitioning a select query must include at least the partitioning key this way the optimizer will perform a partitioning pruning and scan the partitions that corresponds to you predicate.

EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2008-01-01';
                                    QUERY PLAN
-------------------------------------------------------------------​----------------
 Aggregate  (cost=37.75..37.76 rows=1 width=8)
   ->  Seq Scan on measurement_y2008m01  (cost=0.00..33.12 rows=617 width=0)
         Filter: (logdate >= '2008-01-01'::date)

Answer 2

Performance is not the only (and perhaps not even the primary) advantage of partitioning. For example, an index scan on a partitioned table will typically be slightly slower on a partitioned table that on an unpartitioned one, even if you can benefit from partition pruning, because the planning time increases.

Many of the advantages of partitioning do not require partition pruning:

• mass deletion by dropping a partition

• improved autovacuum performance

• speed gains from partitionwise aggregation or join

Only last week, when I had to deal with data corruption in a 3TB table, my job would have been easier had the table been partitioned.