Обсуждение: Table Partitioning and Indexes Performance Questions
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.
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
Would eliminating triggers and stored procedure would be step #1 to start seeing gains from partitions?
We have many triigers and stored procedure and i am trying to to kake sure if need to deprecate before moving to partitioning.
Many thx
Andy
From: Laurenz Albe <laurenz.albe@cybertec.at>
Sent: Thursday, February 29, 2024 9:32:48 AM
To: David Kelly <dkelly123190@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Table Partitioning and Indexes Performance Questions
Sent: Thursday, February 29, 2024 9:32:48 AM
To: David Kelly <dkelly123190@gmail.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Table Partitioning and Indexes Performance Questions
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
> 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