0

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

2 Answers 2

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)
1
  • I see Thanks Rody
    – Zikofs
    Apr 13, 2022 at 1:57
0

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.

1
  • Thanks Laurens for the answer
    – Zikofs
    Apr 13, 2022 at 21:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.