CODEWITHSUNDEEP
postgresql
Peter
Peter

Reputation: 1206

Partitioned Index vs Index on each partition

In postgres 14 and above, what are the benefits of

  1. Having a partitioned index on a partitioned table versus
  2. Having the same index on each partition of a partitioned table, but no 'partitioned index' on the partitioned table

Benefits I can think of:

Are there more benefits of having that 'partitioned index' on the root table that 'glues together' all those indexes in the partitions? Especially when it comes to query planning and performance? Or is this 'partitioned index' on the root table merely a DBA quality of life feature?

Upvotes: 3

Views: 1947

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247625

An index on a partitioned table is mostly only an insurance that the index exists on every partition. The additional difference is that a constraint on a partitioned table can only be implemented with an index on the partitioned table.

Upvotes: 1

Related Questions