Skip site navigation (1) Skip section navigation (2)

Re: Functional dependency in GROUP BY through JOINs

From: "Kevin Grittner" <kgrittn(at)mail(dot)com>
To: "David Rowley" <dgrowleyml(at)gmail(dot)com>,pgsql-hackers(at)postgresql(dot)org
Subject: Re: Functional dependency in GROUP BY through JOINs
Date: 2012-12-06 15:24:07
Message-ID: 20121206152407.142840@gmx.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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


Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2012-12-06 15:27:33
Subject: Re: strange isolation test buildfarm failure on guaibasaurus
Previous:From: Merlin MoncureDate: 2012-12-06 15:22:08
Subject: Re: WIP patch for hint bit i/o mitigation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group