Re: [PATCH] Equivalence Class Filters

On 7 December 2015 at 16:44, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On 6 December 2015 at 16:38, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> >> Lastly, in most cases knowing that t2.id <= 10 is just not worth all
>> >> that much; it's certainly far less useful than an equality condition.
>>
>> > I'd have thought that my link to a thread of a reported 1100 to 2200
>> times
>> > performance improvement might have made you think twice about claiming
>> the
>> > uselessness of this idea.
>>
>
> Personally, I think this optimization is a long way down the list of
> important items because I don't see it as a typical use case. There are
> already patches submitted for more important items, so this isn't the right
> place to be arguing such things. Not every beneficial optimization has a
> wide use case.
>

There is an interesting real world case where we might get some use of
these thoughts.

If we have Orders and OrderItems (FK->Orders)
and we also know (and can Assert) Order.order_date <= OrderItems.ship_date
then a restriction on Orders.order_date > X => OrderItem.ship_date > X when
the two tables are joined on OrderId
and also a restriction on OrderItems.ship_date >= X => Orders.order_date <
X when the two tables are joined on OrderId

Such an assertion could be checked during the FK check, so would not be
expensive to maintain.

One for the future, at least, since we don't have any way of expressing or
enforcing that just yet.

--
Simon Riggs http://www.2ndQuadrant.com/
<http://www.2ndquadrant.com/>
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services