Table Partitioning and Indexes Performance Questions

From: David Kelly <dkelly123190(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Table Partitioning and Indexes Performance Questions
Date: 2024-02-29 16:42:11
Message-ID: CAOB7Wd2+T0D9qthyVkz61wv370xyoR9toPv7y3=LJRsjD7yx3Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I was told that partitioned table indexed must always start with the
partition key columns.

Is this always the case or does it depend on use case? When would you want
to create indexes in this way?

The documentation just mentions that it is strictly unnecessary but can be
helpful. My understanding is partitions behave like normal tables. Each
gets their own index. So, I'd expect the reasoning behind creating the
index on the partition should be the same as if it were just a normal table
(assuming it has the same subset of data as the individual partition). Is
this a correct understanding?

Any other performance considerations when it comes to partitioned table
indexing? Specifically, partitioning by range where the range is a single
value.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Laurenz Albe 2024-02-29 17:32:48 Re: Table Partitioning and Indexes Performance Questions
Previous Message Pavel Stehule 2024-02-29 14:38:28 Re: generic plan generate poor performance