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