David Rowley wrote: > 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. > 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. Off-hand, it seems equivalent to me; I don't know how much work it would be. Out of curiosity, does the first query's plan change if you run this instead?: SELECT s.product_code,SUM(s.quantity) FROM products p INNER JOIN bigsalestable s ON p.productid = s.productid GROUP BY s.product_code; -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
