Re: Extend more usecase for planning time partition pruning and init partition pruning.

Hi David:

On Mon, Mar 8, 2021 at 9:34 AM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Thu, 4 Mar 2021 at 22:07, Amit Langote <amitlangote09(at)gmail(dot)com> wrote:
> > * Or maybe have you considered generalizing what
> > build_implied_pruning_quals() does so that other places like
> > indxpath.c can use the facility?
>
> I agree with doing it another way. There's plenty of other queries
> which we could produce a better plan for if EquivalenceClass knew
> about things like IN conditions and >=, >, < and <= btree ops.
>
> It seems wrong to code anything in this regard that's specific to
> partition pruning.
>
> Please see [1] for an idea. IIRC, the implementation was not well
> received and there were concerns about having to evaluate additional
> needless quals. That part I think can be coded around. The trick will
> be to know when and when not to use additional quals.
>
> The show stopper for me was having a more efficient way to find if a
> given Expr exists in an EquivalenceClass. This is why I didn't take
> the idea further, at the time. My implementation in that patch
> required lots of looping to find if a given Expr had an existing
> EquivalenceMember, to which there was a danger of that becoming slow
> for complex queries.
>
> I'm unsure right now if it would be possible to build standard
> EquivalenceMembers and EquivalenceFilters in the same pass. I think
> it might require 2 passes since you only can use IN and range type
> quals for Exprs that actually have a EquivalenceMember. So you need to
> wait until you're certain there's some equality OpExpr before adding
> EquivalenceFilters. (Pass 1 can perhaps remember if anything looks
> interesting and then skip pass 2 if there's not...??)
>
> EquivalenceClass might be slightly faster now since we have
> RelOptInfo.eclass_indexes. However, I've not checked to see if the
> indexes will be ready in time for when you'd be building the
> additional filters. I'm guessing that they wouldn't be since you'd
> still be building the EquivalenceClasses at that time. Certainly,
> process_equivalence() could do much faster lookups of Exprs if there
> was some global index for all EquivalenceMembers. However,
> equalfuncs.c only gives us true or false if two nodes are equal().
> We'd need to either have a -1, 0, +1 value or be able to hash nodes
> and put things into a hash table. Else we're stuck trawling through
> lists comparing each item 1-by-1. That's pretty slow. Especially with
> complex queries.
>
> Both Andres and I have previously suggested ways to improve Node
> searching. My idea is likely easier to implement, as it just changed
> equalfuncs.c to add a function that returns -1, 0, +1 so we could use
> a binary search tree to index Nodes. Andres' idea [2] is likely the
> better of the two. Please have a look at that. It'll allow us to
> easily build a function to hash nodes and put them in a hash table.
>
> To get [1], the implementation will need to be pretty smart. There's
> concern about the idea. See [3]. You'll need to ensure you're not
> adding too much planner overhead and also not slowing down execution
> for cases by adding additional qual evals that are redundant.
>
> It's going to take some effort to make everyone happy here.
>

I truly understand what you are saying here, and believe that needs some
more hard work to do. I am not sure I am prepared to do that at current
stage. So I will give up this idea now and continue to work with this
when time is permitted. I have marked the commitfest entry as "Returned
with
Feedback". Thanks for the detailed information!

> David
>
> [1]
> https://www.postgresql.org/message-id/flat/CAKJS1f9fPdLKM6%3DSUZAGwucH3otbsPk6k0YT8-A1HgjFapL-zQ%40mail.gmail.com#024ad18e19bb9b6c022fb572edc8c992
> [2]
> https://www.postgresql.org/message-id/flat/20190828234136.fk2ndqtld3onfrrp%40alap3.anarazel.de
> [3]
> https://www.postgresql.org/message-id/flat/30810(dot)1449335261(at)sss(dot)pgh(dot)pa(dot)us#906319f5e212fc3a6a682f16da079f04
>

--
Best Regards
Andy Fan (https://www.aliyun.com/)