Functional dependency in GROUP BY through JOINs

Some idle thoughts has provoked me to sending this email. I'm wondering what
I'm seeing here, is a simple missed optimisation or something that would be
very difficult to implement, or even perhaps something I've completely
misunderstood.

To set the scene let's go with the old products and sales example. In this
example each product has been given a unique ID maybe due to perhaps
product_codes being a bit long and to keep the sales table as narrow as
possible.

If we wanted to see the sales per product we could write something like
this:

SELECT p.product_code,SUM(s.quantity)

FROM products p

INNER JOIN bigsalestable s ON p.productid = s.productid

GROUP BY p.product_code;

Though this plan might not be quite as optimal as it could be as it performs
the grouping after the join.

QUERY PLAN

----------------------------------------------------------------------------
---------

HashAggregate (cost=33195.13..33199.63 rows=450 width=150)

-> Hash Join (cost=20.13..28195.13 rows=1000000 width=150)

Hash Cond: (s.productid = p.productid)

-> Seq Scan on bigsalestable s (cost=0.00..14425.00 rows=1000000
width=8)

-> Hash (cost=14.50..14.50 rows=450 width=150)

-> Seq Scan on products p (cost=0.00..14.50 rows=450
width=150)

(6 rows)

Of course the query could have been written in the first place as:

SELECT p.product_code,s.quantity

FROM products AS p

INNER JOIN (SELECT productid,SUM(quantity) AS quantity FROM bigsalestable
GROUP BY productid) AS s ON p.productid = s.productid;

And that would have given us a more efficient plan.

Of course, for these actual plans to be equivalent there would naturally
have to be a unique index on product_code in the products table.

I think I'm right in thinking that if a unique index exists to match the
group by clause, and the join condition is equality (probably using the same
operator class as the unique btree index?), then the grouping could be
pushed up to before the join.

In the attached script the hand optimised query runs about twice as fast
with 1 million record sales table.

The existence of join removal makes me think we don't want to always allow
it up to who or what is writing the query to allow the best choice in plan.

I'm not really skilled enough in the arts of postgresql's planner to look
into how hard it would be to implement this, but I thought I'd throw it out
there to collect some comments on the idea.

Regards

David Rowley