MIME-Version: 1.0
From: William Kaper <bill@hubifi.com>
Date: Tue, 20 Aug 2024 12:35:20 -0400
Message-ID: <CAD2w=nq3wbMHPZmPVCAQxxsh=Yd=5G4sWUjy=Pz0OQywh0g5Tw@mail.gmail.com>
Subject: Does a partition key need to be part of a composite index for the
 planner to take advantage of it? (PG 16.3+)
To: pgsql-general@postgresql.org
Cc: Antonio Papa <antonio@hubifi.com>
Content-Type: multipart/alternative; boundary="000000000000c5c6cc062020034a"
Archived-At: <https://www.postgresql.org/message-id/CAD2w%3Dnq3wbMHPZmPVCAQxxsh%3DYd%3D5G4sWUjy%3DPz0OQywh0g5Tw%40mail.gmail.com>
Precedence: bulk

--000000000000c5c6cc062020034a
Content-Type: text/plain; charset="UTF-8"

We have a set of operational tables that are all partitioned by
organization ID (customer ID) in the 100M row range. We also have 3-4
composite indexes on these tables that currently do not include the
organization ID. Any queries that reference these tables always provide the
organization ID as a discriminator.

We recently started noticing that the query planner sequence scanning the
correct partitions, but is not using the indexes. So we decided to run a
test by creating a new set of composite indexes that mirror the existing
ones but include organization_id as the first column in the composite
index. When we create the composite index to include organization ID in the
first position, then the planner both selects the correct partitions, AND
index scans those partitions.

Is that expected behavior and it is appropriate to include any partition
keys as leading columns in any indexes on a partitioned table?

One additional piece of information that may or may not be relevant: a
couple weeks ago we upgraded from PG 16.1 to 16.3. In the release notes for
16.2, I did see some fixes pertaining to indexes on partitioned tables and
collations. I couldn't find information on the actual fixes (my
inexperience digging into PG support).

I'm happy to provide some simple examples to illustrate what we are seeing
if the behavior I'm describing is not expected.

Thanks,
Bill Kaper

--000000000000c5c6cc062020034a
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">We have a set of operational tables that are all partition=
ed by organization ID (customer ID) in the=C2=A0100M row range. We also hav=
e 3-4 composite indexes on these tables that currently do not include the o=
rganization ID. Any queries that reference these tables always provide=C2=
=A0the organization ID as a discriminator.=C2=A0<br><br>We recently started=
 noticing that the query planner sequence scanning the correct partitions, =
but is not using the indexes. So we decided to run a test by creating a new=
 set of composite indexes that mirror the existing ones but include organiz=
ation_id as the first column in the composite index. When we create the com=
posite index to include organization ID in the first position, then the pla=
nner both selects the correct partitions, AND index scans those partitions.=
=C2=A0<br><br>Is that expected behavior and it is appropriate to include an=
y partition keys as leading columns in any indexes on a partitioned table?<=
br><br>One additional piece of information that may or may not be relevant:=
 a couple weeks ago we upgraded from PG 16.1 to 16.3. In the release notes =
for 16.2, I did see some fixes pertaining to indexes on partitioned tables =
and collations. I couldn&#39;t find information on the actual fixes (my ine=
xperience digging into PG support).=C2=A0<br><br>I&#39;m happy to provide s=
ome simple examples to illustrate what we are seeing if the behavior I&#39;=
m describing is not expected.<br><br>Thanks,<div>Bill Kaper<div><div dir=3D=
"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"><div dir=
=3D"ltr"><div></div></div></div></div></div></div>

--000000000000c5c6cc062020034a--