Message-ID: <2077e7db3cab149dfcb334dbd649d3fbe648ed98.camel@cybertec.at>
Subject: Re: Does a partition key need to be part of a composite index for
 the planner to take advantage of it? (PG 16.3+)
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: William Kaper <bill@hubifi.com>, pgsql-general@postgresql.org
Cc: Antonio Papa <antonio@hubifi.com>
Date: Tue, 27 Aug 2024 07:03:32 +0200
In-Reply-To: <CAD2w=nq3wbMHPZmPVCAQxxsh=Yd=5G4sWUjy=Pz0OQywh0g5Tw@mail.gmail.com>
References: 
	<CAD2w=nq3wbMHPZmPVCAQxxsh=Yd=5G4sWUjy=Pz0OQywh0g5Tw@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.52.4 (3.52.4-1.fc40) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/2077e7db3cab149dfcb334dbd649d3fbe648ed98.camel%40cybertec.at>
Precedence: bulk

On Tue, 2024-08-20 at 12:35 -0400, William Kaper wrote:
> We have a set of operational tables that are all partitioned by organizat=
ion ID
> (customer ID) in the=C2=A0100M row range. We also have 3-4 composite inde=
xes on these
> tables that currently do not include the organization ID. Any queries tha=
t
> reference these tables always provide=C2=A0the organization ID as a discr=
iminator.=C2=A0
>=20
> 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 creat=
e the
> composite index to include organization ID in the first position, then th=
e planner
> both selects the correct partitions, AND index scans those partitions.=C2=
=A0
>=20
> Is that expected behavior and it is appropriate to include any partition =
keys
> as leading columns in any indexes on a partitioned table?

I think it is hard to reason about this without seeing a concrete example a=
nd
the EXPLAIN (ANALYZE, BUFFERS) output for it.

Yours,
Laurenz Albe