> I think it's true that for unique indexes, fewer keys lead to better > performance. But the same is not necessarily true for non-unique > indexes: more keys could narrow the search space.
Agreed. I have amended the patch to keep the existing behavior for non-unique indexes. > I think it would be > better to address the cases where there are no unique indexes on the > subscriber. Even if we don't have dedicated handling for non-unique > indexes, we can at least leave some comments. I have amended the patch to include a comment explaining the behavior for non-unique indexes. Without invoking the planner, I figure it would be difficult to reason about performance of a scan on a non-unique index. I think a beneficial future optimization would allow users to selectively invoke the planner for their logical apply processes. (Possibly a new configuration in the subscription). Attached is the updated patch. Thank you, Ethan Mertz SDE, Amazon Web Services On Thu, May 28, 2026 at 6:34 PM Masahiko Sawada <[email protected]> wrote: > Hi, > > On Fri, May 22, 2026 at 10:18 AM Ethan Mertz <[email protected]> > wrote: > > > > Hello hackers, > > > > I'd like to reopen the discussion on index selection for logical > replication apply for replica identity full. Since PostgreSQL 14, replica > identity full is able to make use of existing indexes [1][2] (authors in > CC) when replicating UPDATE or DELETE operations. > > > > Today, when identifying which index to use for the update or delete, the > first suitable index is chosen by OID order, which generally corresponds to > creation order. If the chosen index has low cardinality, the lookup may > perform no better than a sequential scan. While avoiding replica identity > full is generally recommended, some users need to maintain REPLICA IDENTITY > FULL to support downstream logical consumers that require full row images. > These users would also like performant PostgreSQL to PostgreSQL replication. > > > > I propose improving the index selection heuristic to prefer unique > indexes, favoring those with fewer columns. Previous discussion in the > linked threads avoided invoking the planner for full index selection; the > heuristic I propose serves as a middle ground. A unique index guarantees > that each tuple match requires at most one index scan, and among unique > indexes, fewer columns means a narrower, more efficient lookup. I have > attached a patch implementing this check. > > +1 > > I think it's true that for unique indexes, fewer keys lead to better > performance. But the same is not necessarily true for non-unique > indexes: more keys could narrow the search space. I think it would be > better to address the cases where there are no unique indexes on the > subscriber. Even if we don't have dedicated handling for non-unique > indexes, we can at least leave some comments. > > Regards, > > -- > Masahiko Sawada > Amazon Web Services: https://aws.amazon.com >
v2-0001-Improve-index-selection-for-REPLICA-IDENTITY-FULL.patch
Description: Binary data
