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@xxxxxxxxxxx>
Sent: Thursday, February 29, 2024 9:32:48 AM
To: David Kelly <dkelly123190@xxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
Subject: Re: Table Partitioning and Indexes Performance Questions
Sent: Thursday, February 29, 2024 9:32:48 AM
To: David Kelly <dkelly123190@xxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxxxxxxxx <pgsql-performance@xxxxxxxxxxxxxxxxxxxx>
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
