groupby.sql

text/plain

Download

BEGIN WORK;

CREATE TABLE products (
  productid INT NOT NULL,
  product_code VARCHAR(64) NOT NULL,
  PRIMARY KEY(productid)
);

CREATE UNIQUE INDEX product_product_code_uidx ON products (product_code);

-- create small list of products
INSERT INTO products sELECT g.id,'ABC' || CAST(g.id AS TEXT) FROM generate_series(1,10) g(id);

CREATE TABLE bigsalestable (
  productid INT NOT NULL,
  quantity INT NOT NULL
);

INSERT INTO bigsalestable (productid,quantity)
SELECT productid,CAST(random() * 1000 AS INT)
FROM products
CROSS JOIN generate_series(1,100000);

COMMIT;

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

EXPLAIN
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;