public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: William Kaper <[email protected]>
To: [email protected]
Cc: Antonio Papa <[email protected]>
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+)
Date: Tue, 27 Aug 2024 07:03:32 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD2w=nq3wbMHPZmPVCAQxxsh=Yd=5G4sWUjy=Pz0OQywh0g5Tw@mail.gmail.com>
References: <CAD2w=nq3wbMHPZmPVCAQxxsh=Yd=5G4sWUjy=Pz0OQywh0g5Tw@mail.gmail.com>

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

view thread (2+ messages)

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  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+)
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox