Message-ID: <60a4dc2711441931d29df7a43ba7447bbedb402f.camel@cybertec.at>
Subject: Re: Question on PostgreSQL Table Partitioning =?UTF-8?Q?=E2=80=93?=
 Performance of Queries That Do Not Use the Partition Key
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: atma ram <atmaramkp@gmail.com>, pgsql-general@lists.postgresql.org
Date: Wed, 26 Nov 2025 16:35:19 +0100
In-Reply-To: <CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
References: 
	<CAA1Yw9-rOunXTb8DEPCZKkEts1gj3oThKidCSVn6egVe9W4-UQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.56.2 (3.56.2-2.fc42) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/60a4dc2711441931d29df7a43ba7447bbedb402f.camel%40cybertec.at>
Precedence: bulk

On Wed, 2025-11-26 at 18:50 +0530, atma ram wrote:
> We have a table that is approximately 1.6 GB in size. Query performance h=
as started to degrade.
> Although we have multiple indexes, the large table size is still causing =
performance issues.

1.6 GB is too small for partitioning.

> We are planning to partition the table on the primary key. This is an OLT=
P system, and there
> are around 100 queries that access this table. About 80 of these queries =
use the primary key
> and will therefore benefit directly from the partition key once we implem=
ent partitioning.

If a table uses the primary key, I cannot see how it could cause performanc=
e issues.

I expect that these queries will become slightly *slower* if you partition =
the table,
because of the overhead of partition pruning.

> However, the remaining 20 queries do not use the primary key; they rely o=
n other indexed columns.
> Our question is: after partitioning the table, and after creating the nec=
essary indexes on
> each partition, what happens to the performance of those 20 queries that =
do not use the partition key?
> =E2=80=93 Will their performance degrade?

Very likely yes, though perhaps only slightly.

> =E2=80=93 Will it remain the same as before partitioning?
> =E2=80=93 Is there any chance it will improve?

That is unlikely, but possible, for example if the query can choose a faste=
r sequential
scan on some partitions, instead of a slower index scan on the whole table.

> Is there any benchmarking, documentation, or reference material that can =
help demonstrate
> how partitioning will affect the performance of the 20 queries that do no=
t use the partition key?
> This information is critical for us before proceeding with the partitioni=
ng strategy.

The only good way to tell is to implement it on a test database and try it.

But as I said initially, with a 1.6 GB table patritioning is pointless.

Examine the performance bottleneck with EXPLAIN (ANALYZE, BUFFERS) and try =
to improve
your queries.

Yours,
Laurenz Albe