On Wed, 05 May 2004 21:09:25 -0400, Tom Lane wrote:
>> Just trying some tests out, and wanted to know about some optimizations.
>> If I do a CHECK constraint on a table, is this used to optimize a SELECT
>
> It is not.
I one were to try to add some constraint-based optimizations ("semantic
query optimizations"), what parts of the code would be most relevant to
study?
In particular, I'm interested in the case of join eliminations, based on
foreign key constraints. E.g. having a SUPPLIER(s_id,...) and a
SUPPLIER_PART(s_id,p_id) table where SUPPLIER_PART.s_id references
SUPPLIER.s_id. Then, a "SELECT p_id FROM SUPPLIER_PART NATURAL JOIN
SUPPLIER" could skip the join and just look in SUPPLIER_PART.
Another thing:
Oracle and PostgreSQL uses IOs to respond to
SELECT * FROM person WHERE age < 30 AND age > 30.
DB2 and MySQL sees that the result is the empty set, without wasting IOs.
- So here's another place for potential optimizations, although the area
is rather hairy, as soon as one moves beyond the most simple cases.
By the way, in "An Introduction to Database Systems", Date writes about
semantic optimizations:
"... such optimization could provide significant
performance improvements - much greater improvements, very likely,
than are obtained by any of today's more traditional optimization
techniques".
--
Greetings from Troels Arvin, Copenhagen, Denmark
