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

From Michał Kłeczek
Subject Re: DRAFT: Pass sk_attno to consistent function
Date
Msg-id DE2605C0-07C4-4477-9DB6-3BC4FE0387D1@kleczek.org
Whole thread Raw
In response to Re: DRAFT: Pass sk_attno to consistent function  (Matthias van de Meent <boekewurm+postgres@gmail.com>)
Responses Re: DRAFT: Pass sk_attno to consistent function
List pgsql-hackers


On 26 Jul 2024, at 01:28, Matthias van de Meent <boekewurm+postgres@gmail.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

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

Regards,

Michal

pgsql-hackers by date:

Previous
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: [Proposal] Add foreign-server health checks infrastructure
Next
From: Bertrand Drouvot
Date:
Subject: Re: Restart pg_usleep when interrupted