Re: Implement predicate propagation for non-equivalence clauses

From: Richard Guo <riguo(at)pivotal(dot)io>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Implement predicate propagation for non-equivalence clauses
Date: 2018-09-26 07:22:11
Message-ID: CAN_9JTyqsSAGhZY_AYaqfH54NnJFu5cjiYoZPntGDxOgQ73Vfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

Hi,

Thanks everyone for reviewing. We updated the patch and added more strict
checks for the non-equivalence clauses before deducing new quals, including:

1) The non-equivalence clause for deduction can only be type of OpExpr or
ScalarArrayOpExpr, with two arguments.

2) The operator of the non-equivalence clause must be in the eclass's
operator
family, if it intends to deduce new quals based on the eclass.

3) The expression in the non-equivalence clause must be equal to the
EquivalenceMember, if we want to replace that expression to get a new qual.

Thanks
Richard

On Thu, Sep 6, 2018 at 12:01 PM Richard Guo <riguo(at)pivotal(dot)io> wrote:

> Hi,
>
> On Wed, Sep 5, 2018 at 2:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Richard Guo <riguo(at)pivotal(dot)io> writes:
>> > 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.
>>
>> Uh, *what* restrictions on f()? In general the above equivalence
>> does not hold, at least not for any data type more complicated than
>> integers; and we do not have any semantic model for deciding
>> which functions it would be correct for.
>>
>
> Exactly! The operator in f() should be at least in the same opfamily as
> the equivalence class containing A,B.
> Besides, as far as I can consider, the clause in f() should not contain volatile
> functions or subplans. Not sure
> if these restrictions are enough to make it safe.
>
>
>> One simple example to show what I'm talking about is that float8 zero
>> and minus zero are equal according to float8eq (assuming IEEE float
>> arithmetic); but they aren't equivalent for any function f() that is
>> sensitive to the sign or the text representation of the value.
>> The numeric data type likewise has values that are "equal" without
>> being identical for all purposes, eg 0.0 vs 0.000. Or consider
>> citext.
>>
>
> Thanks for the example. Heikki materialized this example as:
>
> create table a (f float8);
> create table b (f float8);
>
> insert into a values ('0'), ('-0');
> insert into b values ('0'), ('-0');
>
> select * from a, b where a.f = b.f and a.f::text <> '-0';
>
> And run that query, this patch would give wrong result. Will address this
> in v2.
>
>
>> The existing planner deduction rules for equivalence classes are
>> carefully designed to ensure that we only generate derived clauses
>> using operators from the same operator class or family, so that
>> it's on the opclass author to ensure that the operators have consistent
>> semantics. I don't think we can extrapolate from that to any random
>> function that accepts the datatype.
>
>
>
>
>
>
>> regards, tom lane
>>
>
>

Attachment Content-Type Size
v2-0001-Implement-predicate-propagation-for-non-equivalence-clauses.patch application/octet-stream 37.8 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tahir Ramzan 2018-09-26 07:31:20 Re: Participate in GCI as a Mentor
Previous Message Michael Paquier 2018-09-26 06:44:08 Re: file cloning in pg_upgrade and CREATE DATABASE