On Tue, 19 Mar 2024 at 17:00, Michał Kłeczek <michal@kleczek.org> wrote:
>
> Hi All,
>
> Since it looks like there is not much interest in the patch I will try to provide some background to explain why I
thinkit is needed.
>
[...]
> What we really need is for Gist to support “= ANY (…)”.
> As Gist index is extensible in terms of queries it supports it is quite easy to implement an operator class/family
withoperator:
>
> ||= (text, text[])
>
> that has semantics the same as “= ANY (…)”
I've had a similar idea while working on BRIN, and was planning on
overloading `@>` for @>(anyarray, anyelement) or using a new
`@>>(anyarray, anyelement)` operator. No implementation yet, though.
> 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.
> 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
fromthe 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.
Note that operator classes were built specifically so that they're
independent from their column position. It doens't really make sense
to expose this. Maybe my suggestion up above helps?
Kind regards,
Matthias van de Meent