Re: Does a partition key need to be part of a composite index for the planner to take advantage of it? (PG 16.3+)

On Tue, 2024-08-20 at 12:35 -0400, William Kaper wrote:
> 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?

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

Yours,
Laurenz Albe