From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | William Kaper <bill(at)hubifi(dot)com>, pgsql-general(at)postgresql(dot)org |
Cc: | Antonio Papa <antonio(at)hubifi(dot)com> |
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: | 2024-08-27 05:03:32 |
Message-ID: | 2077e7db3cab149dfcb334dbd649d3fbe648ed98.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Clift | 2024-08-27 06:00:13 | Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |
Previous Message | Tom Lane | 2024-08-27 02:03:47 | Re: Significant Execution Time Difference Between PG13.14 and PG16.4 for Query on information_schema Tables. |