SQL query review and speed-up

1. Correctness

Revenue is counted more than once. JOIN order_items produces one row per item, so SUM(o.total) adds the same order total once for every item in it. An order with three items is tripled.

"Today" is the server's today. created_at::date = CURRENT_DATE uses the server time zone. Your shop runs on Paris time, so orders placed between midnight and 2 am land on the wrong day.

2. Why it is slow

• lower(c.email) LIKE '%@acme.com' starts with a wildcard. No index can help, so Postgres reads all 2 million customers.
• Casting created_at::date hides the column from its index. Compare against a time range instead.

3. Rewrite

SELECT c.id, c.name, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.email_domain = 'acme.com'
  AND o.created_at >= date_trunc('day', now() AT TIME ZONE 'Europe/Paris') AT TIME ZONE 'Europe/Paris'
  AND o.created_at <  date_trunc('day', now() AT TIME ZONE 'Europe/Paris') AT TIME ZONE 'Europe/Paris' + interval '1 day'
GROUP BY c.id, c.name;

Assumption: you add a generated column email_domain. The join to order_items is gone because nothing in the result needs it.

4. Indexes

CREATE INDEX CONCURRENTLY orders_created_customer_idx
  ON orders (created_at, customer_id) INCLUDE (total);

CREATE INDEX CONCURRENTLY customers_email_domain_idx
  ON customers (email_domain);

orders_status_idx is not used by this query. Check pg_stat_user_indexes before you drop it, in case something else needs it.

5. Verify

Run EXPLAIN (ANALYZE, BUFFERS) before and after. You want to see an Index Only Scan on orders instead of a Seq Scan. Expect it to go from about 4 seconds to well under 100 milliseconds.