Hi David: On Mon, Mar 8, 2021 at 9:34 AM David Rowley <dgrowle...@gmail.com> wrote:
> On Thu, 4 Mar 2021 at 22:07, Amit Langote <amitlangot...@gmail.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.1449335...@sss.pgh.pa.us#906319f5e212fc3a6a682f16da079f04 > -- Best Regards Andy Fan (https://www.aliyun.com/)
