On Fri, 2005-07-15 at 00:24 +0300, Hannu Krosing wrote: > Btw, not just UNION ALL, but also simple UNION, INTERSECT and EXCEPT > could probably be taught to use CE at some stage.
It turns out that to solve this problem you very nearly have to solve the "any table" problem. Thats an extra argument in favour of making this work for any table. > That's why I asked for GUC, not a default behaviour ;) > but what about _static_ exlusion based on constraints ? > > I mean if there is a left side table with say a single partition having > CHECK(id_order BETWEEN 1 AND 1000) > (either originally or left after eliminating other by other constraints) > > and 3 right side partition with > CHECK(key_order BETWEEN 1 AND 1000) > CHECK(key_order BETWEEN 1001 AND 2000) > CHECK(key_order BETWEEN 2001 AND 3000) > > then the 3rd one could be eliminated statically from a join on > id_order=key_order Well, SQL allows you to express almost any query, but that doesn't mean it is all 3 of: frequently occcuring, meaningful and interesting. Have you ever seen such a construct? I think we might be able to use equivalence to show that a restriction on one table could be translated into a restriction on the Fact table. e.g. SELECT FROM Fact, OtherTable WHERE Fact.Key = OtherTable.Key AND OtherTable.Key > 28000 But the harder and yet more common problem is where there is no direct restriction on the equivalent join column. Thats the one I would eventually seek to solve e.g. SELECT FROM Fact, Dimension WHERE Fact.Key = Dimension.Key AND Dimension.DescriptiveField = 'Blah' where there is a relationship between DescriptiveField and Key enshrined within the Dimension table. > and thanks for the good work so far! Thank you, Best Regards, Simon Riggs ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend