>> 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/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services