Placing Group By before the join

Recently I was asked to optimize a query that involved joining a dimension table and a fact table, and using an aggregate function. Here is the shape of the query I was tasked with tuning.

​

SELECT TO_CHAR(E.DATE_OF_BIRTH,'YYYY'), SUM(S.QUANTITY)

FROM EMPLOYEES E, SALES S

WHERE E.EMPLOYEE_ID = S.EMPLOYEE_ID

AND S.EMPLOYEE_ID BETWEEN 1 AND 1000

GROUP BY TO_CHAR(DATE_OF_BIRTH,'YYYY');

​

If you want to do experiments, here is the code to generate some test data.

​

CREATE TABLE employees (
employee_id NUMERIC NOT NULL
, first_name VARCHAR(1000) NOT NULL
, last_name VARCHAR(1000) NOT NULL
, date_of_birth DATE
, phone_number VARCHAR(1000) NOT NULL
, junk CHAR(1000)
, CONSTRAINT employees_pk PRIMARY KEY (employee_id) );

CREATE FUNCTION random_string(minlen NUMERIC, maxlen NUMERIC)
RETURNS VARCHAR(1000)
AS $$
DECLARE rv VARCHAR(1000) := '';
  i INTEGER := 0;
  len INTEGER := 0;
BEGIN
  IF maxlen < 1OR minlen < 1OR maxlen < minlen
    THEN RETURN rv;
  END IF;
  len := floor(random()*(maxlen-minlen)) + minlen;
  FOR i IN 1..floor(len) LOOP
     rv := rv || chr(97+CAST(random() * 25 AS INTEGER));
  END LOOP;
  RETURN rv;
END;
$$ LANGUAGE plpgsql;

INSERT
  INTO employees (employee_id, first_name, last_name, date_of_birth, phone_number, junk)
SELECT GENERATE_SERIES ,
         initcap(lower(random_string(2, 8))) ,
         initcap(lower(random_string(2, 8))) ,
         CURRENT_DATE - CAST(floor(random() * 365 * 10 + 40 * 365) AS NUMERIC) * INTERVAL '1 DAY' ,
         CAST(floor(random() * 9000 + 1000) AS NUMERIC) ,
         'junk'
FROM GENERATE_SERIES(1, 1000);

CREATE TABLE sales (
sale_id NUMERIC NOT NULL,
employee_id NUMERIC NOT NULL,
subsidiary_id NUMERIC NOT NULL,
sale_date DATE NOT NULL,
eur_value NUMERIC(17,2) NOT NULL,
product_id BIGINT NOT NULL,
quantity INTEGER NOT NULL,
CHANNEL VARCHAR(4) NOT NULL,
CONSTRAINT sales_pk
PRIMARY KEY (sale_id, SALE_DATE)
) PARTITION BY RANGE (SALE_DATE);

CREATE TABLE SALES_2021 PARTITION OF SALES
    FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
CREATE TABLE SALES_2022 PARTITION OF SALES
   FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE SALES_MAX PARTITION OF SALES
   FOR VALUES FROM ('2023-01-01') TO (MAXVALUE);

SELECT SETSEED(0);

INSERT INTO sales (sale_id
, subsidiary_id, employee_id
, sale_date, eur_value
, product_id, quantity
, CHANNEL)
SELECT row_number() OVER (), data.*
  FROM (
       SELECT gen % 100, e.employee_id
       , (CURRENT_DATE - CAST(RANDOM()*3650 AS NUMERIC) * INTERVAL '1 DAY') sale_date
       , CAST(RANDOM()*100000 AS NUMERIC)/100 eur_value
       , CAST(RANDOM()*25 AS NUMERIC) + 1 product_id
       , CAST(RANDOM()*5 AS NUMERIC) + 1 quantity
      , CASE WHEN GEN % 2 = 0 THEN 'ONLI' ELSE 'OFFL' END
     FROM employees e
          , GENERATE_SERIES(1, 18000) gen
     WHERE MOD(employee_id, 7) = 4
     -- AND gen < employee_id / 2
       ORDER BY sale_date
       ) data
WHERE TO_CHAR(sale_date, 'D') <> '1';

DROP INDEX SALES_X01;