Re: DRAFT: Pass sk_attno to consistent function - Mailing list pgsql-hackers

> 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.

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
Bitmapscan 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
metadatafrom 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