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

Hi Tom,

Thanks for looking at it.

> On 24 Jul 2024, at 22:19, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
>> I understand extensibility of GIST makes many operators opaque to the planner and it is the “consistent” function
thatcan perform optimisations (or we can come up with some additional mechanism during planning phase). 
>> Providing more information to “consistent” function would make it possible to implement optimisations not only for
arrayscan keys but for ranges and others. 
>
>> What we can do is to provide the index attribute number (reduntantly) as option - it is going to work but is
somewhatugly - especially that this information is already available when calling consistent function. 
>
> While the proposed change is simple enough and wouldn't break
> anything, I share Matthias' distaste for it, because the motivation
> you've given for it is deeply unsatisfying and indeed troubling.
> A GIST consistent function is surely the wrong place to be optimizing
> away unnecessary partitions: that consideration is not unique to
> GIST indexes (or even to indexscans), much less to particular opclasses.
> Moreover, having a consistent function inspect catalog state seems
> like a kluge of the first order, not least because there'd be no good
> place to cache the lookup results, so you'd be doing them over and
> over again.

Indeed - caching results of such lookups is clumsy.

And I agree the whole thing is hackish.
But in reality I think this is due to fundamental mismatch between
extensibility interface of GIST and the lack of it in partition pruning code.

>
> I like Matthias' suggestion of seeing whether you could use a
> planner support function to split up the query by partitions
> during planning.

You mean implement custom partition pruning algorithm using
query rewriting?

As I’ve written in another message in this thread:
If it was possible to write the query in such a way then the whole
discussion would be moot and I wouldn’t propose this patch :)

>
> There are bits of what you mentioned that I would gladly take
> patches for --- for example, I think the reason GIST lacks SAOP
> support is mostly lack of round tuits, not that it'd be a bad
> thing.  But it's not clear to me that that alone would help you
> much.  The whole design as you've described it seems like multiple
> layers of kluges, so that getting rid of any one of them doesn't
> really make it not klugy.

Kluges are workarounds for lack of two things in GIST:
- missing SAOP support (which I try to simulate using custom operator)
- missing ORDER BY support (for which I posted a draft idea in another patch)

> (I also have serious doubts about how well
> it would perform for you, even with this additional kluge in place.
> I don't think that a multidimensional GIST over zillions of rows will
> perform very well: the upper tree layers are just not going to be very
> selective.)

It work surprisingly well in practice as our performance tests show.
A single multicolumn GIST index is capable of handling most of our queries.

Regards,


—
Michal