Re: DRAFT: Pass sk_attno to consistent function

> On 26 Jul 2024, at 10:10, Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
>
>
>> On 26 Jul 2024, at 01:28, Matthias van de Meent <boekewurm+postgres(at)gmail(dot)com> wrote:
>>
>> 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?
>
> The issue is not partition pruning for SAOP - it works fine. The issue is lack of SAOP support in GIST.
>
> Because I cannot use SAOP I have two options:
>
> 1) LATERAL JOIN (ie. iterate through input array elements, SELECT rows for each, merge results)
> 2) Implement a custom operator that emulates SAOP and provide consistent function for it. Additionally provide SAOP clause (redundantly) to enable partition pruning.
>
> In case of 1):
> - the query becomes convoluted with multiple redundant ORDER BY and LIMIT clauses
> - unnecessary sort is performed (because we have to merge results of subqueries)
> - some partitions are scanned multiple times (per each element in input array that happens to land in the same partition)
>
> In case of 2):
> - the whole input array is passed to consistent function for each partition so we unnecessarily search for non-existent rows

Which is especially painful in case of sharding implementation based on partitioning and postgres_fdw as it requires multiple
SELECTS from remote partition.

>
> To fix the issue in 2) I need to somehow filter input array per partition - hence this patch.
>

Regards,

—
Michal