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

On Fri, 22 Mar 2024, 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.
>
> Thanks for suggestion.
>
> I am afraid I don’t understand how it might actually work though:
[...]
> 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.

I would probably (try to) implement something like the following:

- Alter each partition by adding a constraint `CHECK
(hash(partitioncol) % part_modulus = part_remainder)`, to give the
planner the tools to do partition pruning. This solves the partition
pruning part, in userspace. I woudln't be opposed to a fix in
PostgreSQL if done well - hash partition pruning sounds like a niche,
but a valid niche nonetheless.
- Add support function that translates e.g. ||=(array, elem) on the
base table into a list of OR-ed `(hash(partitioncol) % part_modulus =
part_remainder AND col ||= [sublist])`-statements, one for each of the
partitions.
- Make sure there's a planner facility that pushes down OR branches
and removes non-matched qualifiers. Presumably, partition pruning can
already take care of that.

The planner should be able to deduct that each partition has their own
-unique- CHECK constraint, and that this check can't be satisfied by
any other partition and thus is ignored for those other partitions'
scans.

Alternatively, partition the table not using HASH, but using LIST
((hash(partitioncol) % modulus)) - this should enable partition
pruning without creating those manual CHECK constraints. However,
that'd be at the cost of access to hash partitioning-related features,
like different modulos at the same partitioning level.

All in all, this still seems like a very (very) specific optimization,
of which I'm not sure that it is generalizable. However, array
introspection and filtering for SAOP equality checks feel like a
relatively easy (?) push-down optimization in (e.g.) runtime partition
pruning (or planning); isn't that even better patch potential here?


Kind regards,

Matthias van de Meent
Neon (https://neon.tech)