> On 22 Mar 2024, at 01:29, Michał Kłeczek <mic...@kleczek.org> wrote: > > > >> On 21 Mar 2024, at 23:42, Matthias van de Meent >> <boekewurm+postg...@gmail.com> wrote: >> >>> On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek <mic...@kleczek.org> wrote: >>> >>> With this operator we can write our queries like: >>> >>> account_number ||= [list of account numbers] AND >>> account_number = ANY ([list of account numbers]) — redundant for partition >>> pruning as it does not understand ||= >>> >>> and have optimal plans: >>> >>> Limit >>> — Merge Append >>> —— Index scan of relevant partitions >>> >>> The problem is that now each partition scan is for the same list of >>> accounts. >>> The “consistent” function cannot assume anything about contents of the >>> table so it has to check all elements of the list >>> and that in turn causes reading unnecessary index pages for accounts not in >>> this partition. >> >> You seem to already be using your own operator class, so you may want >> to look into CREATE FUNCTION's support_function parameter; which would >> handle SupportRequestIndexCondition and/or SupportRequestSimplify. >> I suspect a support function that emits multiple clauses that each >> apply to only a single partition at a time should get you quite far if >> combined with per-partition constraints that filter all but one of >> those. Also, at plan-time you can get away with much more than at >> runtime.
Thinking about it some more - the suggestion goes backwards - ie. there must have been some deep misunderstanding: If I was able to write my query such that the planner generates optimal plan, I would not implement the custom operator in the first place. And this need of custom operator and operator class triggered the proposal in turn. — Michal
