Hi Tom, Thanks for looking at it.
> On 24 Jul 2024, at 22:19, Tom Lane <t...@sss.pgh.pa.us> wrote: > > =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <mic...@kleczek.org> writes: >> I understand extensibility of GIST makes many operators opaque to the >> planner and it is the “consistent” function that can 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 array scan 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 somewhat ugly - 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
