2

I am from SQL Server background so very limited knowledge in Postgres.

We have a partition table with more than 100 partitions based on clientid. queries to that table is being very slow so planning to create an index on that partition.

I did refer Postgres manual, everywhere it tells to create index on partition then i will end up creating more than 100 indexes in table.

Do we have any global partition index which will cover all partition in Postgres?

VBAGuy
  • 172
  • 1
  • 9

1 Answers1

3

No, Postgres does not have global indexes, i.e. a single index that contains data from all partitions.

However, I strongly recommend to upgrade to Postgres 12 which has substantial performance improvements for partitioning.

Postgres 11 introduced the ability to create an index on the partitioned table and will then automatically create and maintain the indexes on the partitions. Which is maybe what you are looking for (you still have one index per partition)

  • @a_horse_with_no_name..Thanks for your responce...But we are in AWS aurora...it supports max postgres 11 till now. so you mean have to create like the below example CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); CREATE INDEX measurement_y2007m11_logdate ON measurement_y2007m11 (logdate); CREATE INDEX measurement_y2007m12_logdate ON measurement_y2007m12 (logdate); CREATE INDEX measurement_y2008m01_logdate ON measurement_y2008m01 (logdate); this is weird. – VBAGuy Jul 15 '20 at 07:25
  • 1
    Actually the possibility to create an index on a partitioned table was [introduced Postgres 11](https://www.postgresql.org/docs/11/release-11.html), so you should be able to run `create index on measurement (logdate);` once you upgrade to 11 –  Jul 15 '20 at 07:28
  • 1
    @ a_horse_with_no_name...will we be able to create index like you said create index on measurement (logdate); or we have to create index on each partition with partition key – VBAGuy Jul 15 '20 at 08:17