From: | Michał Kłeczek <michal(at)kleczek(dot)org> |
---|---|
To: | Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: DRAFT: Pass sk_attno to consistent function |
Date: | 2024-03-22 09:11:51 |
Message-ID: | 94BCD6C1-E402-4DD5-8FC8-5C819907DD0E@kleczek.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> On 22 Mar 2024, at 01:29, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
>
>
>> On 21 Mar 2024, at 23:42, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:
>>
>>> On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek <michal(at)kleczek(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-03-22 09:18:33 | Re: Reducing output size of nodeToString |
Previous Message | Bertrand Drouvot | 2024-03-22 08:57:33 | Re: Introduce XID age and inactive timeout based replication slot invalidation |