| From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
|---|---|
| To: | David Kelly <dkelly123190(at)gmail(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Table Partitioning and Indexes Performance Questions |
| Date: | 2024-02-29 17:32:48 |
| Message-ID: | 3fc6bb529ca26a706f39b4469943f3bfb3748c8e.camel@cybertec.at |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
On Thu, 2024-02-29 at 11:42 -0500, David Kelly wrote:
> I was told that partitioned table indexed must always start with the partition key columns.
That's not true.
Only unique indexes (as used by primary key and unique constraints) must
contain the partitioning key (but they don't have to start with it).
> Any other performance considerations when it comes to partitioned table indexing?
> Specifically, partitioning by range where the range is a single value.
Not particularly - selecting from a partitioned table is like selecting
from a UNION ALL of all partitions, except that sometimes PostgreSQL
can forgo scanning some of the partitions.
If you use very many partitions, the overhead for query planning and
execution can become noticable.
Yours,
Laurenz Albe
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Anupam b | 2024-02-29 18:11:33 | Re: Table Partitioning and Indexes Performance Questions |
| Previous Message | David Kelly | 2024-02-29 16:42:11 | Table Partitioning and Indexes Performance Questions |