> On 22 Mar 2024, at 01:29, Michał Kłeczek <michal@kleczek.org> wrote:
>
>
>
>> On 21 Mar 2024, at 23:42, Matthias van de Meent <boekewurm+postgres@gmail.com> wrote:
>>
>>> On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek <michal@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
inthe first place.
And this need of custom operator and operator class triggered the proposal in turn.
—
Michal