Re: Allowing join removals for more join types

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.