Re: Implement predicate propagation for non-equivalence clauses

On 05/09/18 09:34, Richard Guo wrote:
> Hi,
> 
> As we know, current planner will generate additional restriction clauses 
> from
> equivalence clauses. This will generally lower the total cost because 
> some of
> tuples may be filtered out before joins.
> 
> In this patch, we are trying to do the similar deduction, from 
> non-equivalence
> clauses, that is, A=B AND f(A) implies A=B AND f(A) and f(B), under some
> restrictions on f.

I haven't read the patch in detail, but that really only applies under 
special circumstances. Tom caught me making that assumption just 
recently 
(https://www.postgresql.org/message-id/8003.1527092720%40sss.pgh.pa.us). 
I think the restriction here is that f(x) must be an operator that's in 
the same operator family as the = operator. In a quick read-through, 
it's not clear to me what conditions are in the patch now. Please have a 
comment somewhere to list them explicitly.

> This patch will introduce extra cost for relation scan, due to the
> cost of evaluating the new implied quals. Meanwhile, since the extra
> filter may reduce the number of tuples returned by the scan, it may
> lower the cost of following joins. So, whether we will get a better
> plan depends on the selectivity of the implied quals.
Perhaps we should evaluate the selectivity of the clause, and only add 
them if they seem helpful, based on the cost vs. selectivity?

At least in this case from the regression tests:

>  explain (costs off)
>    select * from ec0 a, ec1 b
>    where a.ff = b.ff and a.ff = 43::bigint::int8alias1;
> -                 QUERY PLAN                  
> ----------------------------------------------
> +                              QUERY PLAN                              
> +----------------------------------------------------------------------
>   Nested Loop
>     ->  Index Scan using ec0_pkey on ec0 a
>           Index Cond: (ff = '43'::int8alias1)
>     ->  Index Scan using ec1_pkey on ec1 b
>           Index Cond: (ff = a.ff)
> -         Filter: (f1 < '5'::int8alias1)
> +         Filter: ((f1 < '5'::int8alias1) AND (ff = '43'::int8alias1))
>  (6 rows)

the new qual is redundant with the Index Condition. If we could avoid 
generating such redundant quals, that would be good.

- Heikki