Postgresql partitioning queries benefits



  • 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



  • 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)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2