> 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.
Thanks for suggestion.
I am afraid I don’t understand how it might actually work though:
1) I think planning time is too early for us - we are heavily using
planner_mode = force_generic_plan:
- we have many partitions and planning times started to dominate execution time
- generic plans are not worse than specialised
2) I am not sure how I could transform
"col ||= [array]" to multiple criteria to make sure it works well with
partition pruning and planner.
It looks like what you are suggesting is to generate something like:
(part_condition AND col ||= [subarray1]) OR (part_condition AND col ||=
[subarray2])
and hope the planner would generate proper Merge Append node (which I doubt
would happen and planner would generate Bitmap scan due to lack of OR support
in Gist).
What’s more - there is no part_condition for hash partitions.
>
>> What we need is a way for the “consistent” function to be able to
>> pre-process input query array and remove elements
>> that are not relevant for this scan. To do that it is necessary to have
>> enough information to read necessary metadata from the catalog.
>
>> The proposed patch addresses this need and seems (to me) largely
>> uncontroversial as it does not break any existing extensions.
>
> I don't think "my index operator class will go into the table
> definition and check if parts of the scankey are consistent with the
> table constraints" is a good reason to expose the index column to
> operator classes.
Quite possibly but I still don’t see any other way to do that TBH.
—
Michal
