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. 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
