On 18 May 2014 16:38 David Rowley Wrote
Sound like a good idea to me..
I have one doubt regarding the implementation, consider the below query
Create table t1 (a int, b int);
Create table t2 (a int, b int);
Create unique index on t2(b);
select x.a from t1 x left join (select distinct t2.a a1, t2.b b1 from t2) as y
on x.a=y.b1; (because of distinct clause subquery will not be pulled up)
In this case, Distinct clause is used on t2.a, but t2.b is used for left Join
(t2.b have unique index so this left join can be removed).
So I think now when you are considering this join removal for subqueries then
this can consider other case also like unique index inside subquery,
because in attached patch unique index is considered only if its RTE_RELATION
+ if (innerrel->rtekind == RTE_RELATION &&
+ relation_has_unique_index_for(root, innerrel,
clause_list, NIL, NIL))
return true;
Correct me if I am missing something..
CREATE TABLE products (productid integer NOT NULL, code character varying(32)
NOT NULL);
CREATE TABLE sales (saleid integer NOT NULL, productid integer NOT NULL, qty
integer NOT NULL);
CREATE VIEW product_sales AS
SELECT p.productid,
p.code,
s.qty
FROM (products p
LEFT JOIN ( SELECT sales.productid,
sum(sales.qty) AS qty
FROM sales
GROUP BY sales.productid) s ON ((p.productid = s.productid)));
If a user does:
SELECT productid,code FROM product_sales;
Then, if I'm correct, the join on sales can be removed.
Attached is a patch which implements this. It's still a bit rough around the
edges and some names could likely do with being improved, but it at least seems
to work with all of the test cases that I've thrown at it so far.
Comments are welcome, but the main purpose of the email is so I can register
the patch for the June commitfest.
