CODEWITHSUNDEEP
postgresql
Adam Benson
Adam Benson

Reputation: 8532

Partitioned table - is adding an index on the partition column unnecessary?

We have a table partitioned on a date column.

Some of my colleagues believe that this means that column is automatically indexed. Having looked for evidence of this I don't believe that is so. Who is right?

The manual https://www.postgresql.org/docs/current/ddl-partitioning.html (section 5.11.2.1. Example) says:

Create an index on the key column(s), as well as any other indexes you might want, on the partitioned table. (The key index is not strictly necessary, but in most scenarios it is helpful.) This automatically creates a matching index on each partition, and any partitions you create or attach later will also have such an index. An index or unique constraint declared on a partitioned table is “virtual” in the same way that the partitioned table is: the actual data is in child indexes on the individual partition tables.

This suggests to me we should create the index.

Each partition has ~350K rows. Since we often query by date range on that column would each partition get its own index? Or one massive one across all partitions?

Would adding an index on this column improve or degrade performance?

Upvotes: 5

Views: 5192

Answers (1)

jjanes
jjanes

Reputation: 44192

There is not automatically an index on the partition column.

If you did list partitioning and every list only contains one date (i.e. every date has its own partition) then I don't think also having an index on that column would be helpful. There is not extra information in the column beyond what the partitioning already knows about.

If you did range partitioning on quarter or year, but often query by a specific date, then the index would likely be useful as it provides a lot of extra specificity.

Upvotes: 8

Related Questions