Re: [PATCH] Equivalence Class Filters

On 12/16/2015 01:26 AM, Simon Riggs wrote:
>
> 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.

There's a concept of "correlation maps", described in a paper [1]
presented on VLDB 2009. It essentially talks about deriving conditions
between attributes of the same table, but I guess it might be modified
to track the correlations through foreign keys.

Interestingly enough, the data for the paper paper were collected on
PostgreSQL, but the correlation maps were implemented in an application
layer on top of the database.

[1] http://www.vldb.org/pvldb/2/vldb09-199.pdf

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services