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
