SQL Query Optimizer

Optimizing the monthly revenue-per-customer dashboard query (PostgreSQL 15)

Before I commit: what I still need from you

Your symptom description is detailed, but a few facts change the numbers and one design choice. I give firm recommendations below and flag where these facts matter.

Reading the plan (what I infer from your symptoms)

1. Seq Scan on orders (12.4M rows, ~9.8s). This is caused by DATE(o.created_at).
   • A B-tree on created_at can only serve predicates of the form created_at <op> constant.
   • A function applied to the column hides it from the index.
   • It also wrecks the row estimate. Postgres has no statistics for the expression date(created_at), so it falls back to a default range selectivity of about 0.5%, roughly 62k rows. If August actually has several hundred thousand orders, every downstream join and memory decision is sized wrong.
2. The "~38M-row" hash join. The Seq Scan on order_items (41M rows) is most likely probing a hash of the filtered orders.
   • The join output itself should only be about August's line items.
   • The plan will tell us which. Either way, all 41M order_items rows get read.
3. The 612MB "external merge" sort is probably not the ORDER BY ... LIMIT 50 sort.
   • ORDER BY x LIMIT 50 uses a top-N heapsort, which keeps only 50 rows in memory and never spills.
   • COUNT(DISTINCT ...) can't be computed with a HashAggregate in PG 15. The planner must sort the whole joined input by c.id, c.company_name, c.region and feed a GroupAggregate.
   • That input is every line item of every eligible customer. It also includes one null-extended row for each active customer with no August orders, because of the LEFT JOIN.
   • That big pre-aggregation sort is what spills with work_mem = 4MB.
4. The "lock waits" on inserts. SELECT takes AccessShareLock, which conflicts only with DDL-level locks, not with INSERT's RowExclusiveLock.
   • What your inserts are almost certainly waiting on is resources: CPU starvation at 95%, LWLock:BufferMapping / BufferContent, IO:DataFileRead, and temp-file I/O competing with WAL writes.
   • Fixing the query fixes this. It isn't a locking problem, so don't go tuning lock settings.
5. Concurrency multiplies all of the above. About 40 analysts run an identical 15s query every 5 minutes, and runs overlap. That is roughly 600 CPU-seconds of repeated identical work per 5-minute window on a 4-vCPU box.

Diagnosis

The single biggest cost driver is the non-sargable DATE(o.created_at) BETWEEN ... predicate. It forces a full scan of orders, gives the planner a default row estimate, and lets it pick a plan that also reads all 41M order_items rows.

Two structural issues multiply that cost:

• COUNT(DISTINCT o.id) over a line-item-level fan-out forces a giant sort-based aggregation, which is the disk spill.
• The LEFT JOIN from customers drags every eligible customer through the aggregate, even though HAVING SUM(...) > 0 discards anyone without revenue.

On top of that, 40 clients recompute the identical result independently. That is why even a well-optimized live query is the wrong architecture for a p95 of 300ms.

Optimized query

This is logically equivalent to the original: same rows, same values, same types. As in the original, which row wins a tie at position 50 is unspecified.

SELECT
    c.id             AS customer_id,
    c.company_name,
    c.region,
    t.order_count,
    t.total_revenue,
    t.last_order_at
FROM (
    -- LEVEL 2: roll per-order totals up to one row per customer
    SELECT
        po.customer_id,
        COUNT(*)              AS order_count,    -- CHANGED: input is exactly one row per order,
                                                 --   so COUNT(*) = COUNT(DISTINCT o.id) with no DISTINCT sort
        SUM(po.order_revenue) AS total_revenue,  -- CHANGED: sum of per-order sums; NUMERIC is exact,
                                                 --   so regrouping the addition cannot change the result
        MAX(po.created_at)    AS last_order_at
    FROM (
        -- LEVEL 1: one row per August order, with its line-item total
        SELECT
            o.id,
            o.customer_id,                       -- allowed without GROUP BY: functionally dependent on PK o.id
            o.created_at,
            SUM(oi.quantity * oi.unit_price) AS order_revenue  -- NULL for item-less orders, exactly as before
        FROM orders o
        LEFT JOIN order_items oi                 -- kept LEFT: item-less orders still count in order_count
               ON oi.order_id = o.id             --   and last_order_at, as in the original
        WHERE o.created_at >= '2026-08-01'       -- CHANGED: sargable half-open range; uses idx_orders_created_at
          AND o.created_at <  '2026-09-01'       --   replaces DATE(o.created_at) BETWEEN '2026-08-01' AND '2026-08-31'
          AND EXISTS (                           -- CHANGED: customer filter applied early as a semi-join,
                SELECT 1                         --   so ineligible customers' items are never aggregated
                FROM customers c2
                WHERE c2.id = o.customer_id
                  AND c2.status = 'active'
                  AND c2.region IN ('US-EAST', 'US-WEST', 'EU-CENTRAL'))
        GROUP BY o.id
    ) po
    GROUP BY po.customer_id
    HAVING SUM(po.order_revenue) > 0             -- unchanged; this is why the original LEFT JOIN from
                                                 --   customers was effectively an INNER join
    ORDER BY total_revenue DESC
    LIMIT 50                                     -- CHANGED: pick the top 50 BEFORE fetching display columns
) t
JOIN customers c ON c.id = t.customer_id         -- CHANGED: only 50 primary-key lookups for company_name/region
ORDER BY t.total_revenue DESC;                   -- re-apply order; a join does not guarantee it is preserved

Why each rewrite is safe

1. DATE(ts) BETWEEN d1 AND d2 becomes ts >= d1 AND ts < d2 + 1.

• DATE(timestamptz) converts to a local date using the session TimeZone.
• The untyped literal '2026-08-01' compared to a timestamptz column is cast to midnight in the same session TimeZone.
• So date(ts) >= '2026-08-01' ⇔ ts >= '2026-08-01 00:00 local', and date(ts) <= '2026-08-31' ⇔ ts < '2026-09-01 00:00 local'.
• They are identical for any session time zone. Using < next day instead of <= '2026-08-31 23:59:59' also avoids losing microsecond-precision rows in the last second.
• Also, you can't fix this with an expression index on DATE(created_at). For timestamptz it's STABLE, not IMMUTABLE, because it depends on TimeZone. Postgres rejects it with "functions in index expression must be marked IMMUTABLE". The range rewrite is the correct fix, not a workaround.

2. Dropping the LEFT JOIN from customers.

• A customer with no matching orders or items has SUM(...) = NULL.
• NULL > 0 is not true, so HAVING already removed them.
• Driving the query from August orders removes those rows before aggregation instead of after.

3. Two-level aggregation instead of COUNT(DISTINCT).

• Level 1 collapses the line-item fan-out to one row per order, so a plain COUNT(*) is exact at level 2.
• quantity and unit_price are NOT NULL, and NUMERIC addition is associative, so revenue is bit-identical.
• Both levels can now use HashAggregate.

4. LIMIT before the display join.

• company_name and region don't affect grouping, filtering, or ordering.
• So we fetch them for 50 rows instead of carrying wide TEXT through the aggregates.

Optional: append , t.customer_id to both ORDER BY clauses to make ties deterministic. This is a behavior improvement, not an equivalence change, so I left it out.

Index recommendations

My guiding rule, given your write constraint, is to replace rather than add. Your hot-write tables should end up with the same number of indexes they have now.

-- 1) No new index needed on orders.
--    idx_orders_created_at (created_at) becomes usable as soon as the predicate is sargable.
--    orders.id comes from BIGSERIAL and created_at defaults to now(), so the heap is
--    physically ordered by time (correlation ~1). An August range scan reads a
--    contiguous block of pages: few, sequential-ish I/Os.

-- 2) customers: composite replaces the single-column index (customers is low-write).
CREATE INDEX CONCURRENTLY idx_customers_status_region
    ON customers (status, region) INCLUDE (id);
DROP INDEX CONCURRENTLY idx_customers_status;   -- redundant: (status, region) serves status-only lookups too

• Why this shape: both columns are equality/IN filters. status goes first so the index still serves every existing status = ... query, which makes the old index redundant.
• Why INCLUDE (id): it lets the semi-join build its hash from an index-only scan.
• When it's worth it: only if the eligible set is a minority of the 850k rows. If ~90% of customers are active and in these regions, skip it; a seq scan of 850k narrow rows is ~50–100ms and fine.

-- 3) OPTIONAL, only if EXPLAIN shows order_items heap fetches dominating after the rewrite:
--    a covering index replaces (not supplements) the existing order_id index.
CREATE INDEX CONCURRENTLY idx_order_items_order_id_cov
    ON order_items (order_id) INCLUDE (quantity, unit_price);
DROP INDEX CONCURRENTLY idx_order_items_order_id;

• What it enables: index-only scans for the per-order probes. order_id leads, so it still serves the FK checks and cascades the old index served.
• Write cost: still exactly one index insertion per row, just a wider tuple. That's roughly 1.5–2× the index size, and a negligible per-insert cost difference.

Explicitly not recommended:

• Any new index on orders or order_items.
• An index on order_items (order_id, quantity, unit_price) as key columns. INCLUDE does the same job without bloating the upper B-tree levels.
• An expression index on DATE(created_at), which is impossible as explained above.

Materially important: stop computing this 40 times (schema/config)

Let me be direct: even the optimized query must aggregate every August line item for eligible customers. If August is ~300k–1M orders and ~1–3M items, that is on the order of 0.3–1.5s per run, and 40 concurrent copies will contend for 4 vCPUs. Query tuning alone is unlikely to hold p95 under 300ms under that concurrency.

The result is identical for every analyst, so compute it once per refresh interval.

-- Proposed new object (name is a suggestion; nothing existing is renamed)
CREATE MATERIALIZED VIEW mv_customer_monthly_revenue AS
SELECT c.id AS customer_id, c.company_name, c.region,
       t.order_count, t.total_revenue, t.last_order_at
FROM (
    SELECT po.customer_id, COUNT(*) AS order_count,
           SUM(po.order_revenue) AS total_revenue, MAX(po.created_at) AS last_order_at
    FROM (
        SELECT o.id, o.customer_id, o.created_at,
               SUM(oi.quantity * oi.unit_price) AS order_revenue
        FROM orders o
        LEFT JOIN order_items oi ON oi.order_id = o.id
        WHERE o.created_at >= '2026-08-01' AND o.created_at < '2026-09-01'
          AND EXISTS (SELECT 1 FROM customers c2
                      WHERE c2.id = o.customer_id AND c2.status = 'active'
                        AND c2.region IN ('US-EAST','US-WEST','EU-CENTRAL'))
        GROUP BY o.id
    ) po
    GROUP BY po.customer_id
    HAVING SUM(po.order_revenue) > 0          -- no LIMIT: store all qualifying customers
) t
JOIN customers c ON c.id = t.customer_id;

CREATE UNIQUE INDEX ON mv_customer_monthly_revenue (customer_id);   -- required for REFRESH ... CONCURRENTLY
CREATE INDEX ON mv_customer_monthly_revenue (total_revenue DESC);   -- makes the dashboard read a 50-row index scan

-- Refresh with generous memory for this one job only
CREATE OR REPLACE PROCEDURE refresh_mv_customer_monthly_revenue()
LANGUAGE plpgsql
SET work_mem = '128MB'
AS $$
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY mv_customer_monthly_revenue;  -- readers are never blocked
END;
$$;

-- pg_cron is supported on RDS PG15 (add to shared_preload_libraries in the parameter group; needs a reboot)
SELECT cron.schedule('refresh-customer-revenue', '*/5 * * * *',
                     'CALL refresh_mv_customer_monthly_revenue()');

-- Dashboard query becomes:
SELECT * FROM mv_customer_monthly_revenue ORDER BY total_revenue DESC LIMIT 50;

August is a closed month (today is 2026-09-23). Unless you have late or backdated orders or edits, its result never changes. Refresh it once, or nightly to catch corrections, instead of every 5 minutes.

If the dashboard is really "current month":

• Swap the literals for date_trunc('month', now()) and date_trunc('month', now()) + interval '1 month'.
• Those evaluate at refresh time, in the refreshing session's TimeZone. Pin it, e.g. SET TimeZone = 'UTC' in the procedure, to match what analysts expect.

Other config and schema notes

• work_mem: don't raise it globally. 40 sessions × several hash/sort nodes × parallel workers could reach many GB. Scope it:
  • per job, as in the procedure above, or
  • per role: ALTER ROLE <your_dashboard_role> SET work_mem = '64MB'; (substitute your actual role).
• Visibility map (only if you adopt the covering index): index-only scans need pages marked all-visible. Keep that current on the insert-heavy table with ALTER TABLE order_items SET (autovacuum_vacuum_insert_scale_factor = 0.01);. PG13+ triggers vacuum on inserts, but the default 0.2 × 41M rows means it rarely fires.
• Statistics: ANALYZE from 2 days ago is fine for August. For current-month queries, Postgres probes the actual index endpoints for out-of-histogram values, which mitigates stale stats on created_at.
• Partitioning orders by month: don't do this now.
  • It would enable partition pruning.
  • But on a partitioned table, the primary key must include created_at. That breaks order_items.order_id REFERENCES orders(id) unless you also carry created_at into order_items.
  • It's a large migration for a problem the matview already solves.

Why it's faster

Rough estimates

These assume August ≈ 300k–1M orders and 1–3M items, all warm in the 8GB shared_buffers. Confirm with your counts.

Freeing that CPU is also what makes the "lock waits" (really CPU/IO/LWLock waits) on your inserts disappear.

Trade-offs & risks

• Staleness: the matview is up to 5 minutes old, plus the refresh duration. Since the dashboard already auto-refreshes every 5 minutes, this is usually acceptable, but confirm with stakeholders.
• Refresh cost: REFRESH ... CONCURRENTLY runs the full query plus a diff against the old contents, so it's somewhat more expensive than a plain refresh.
  • It takes an EXCLUSIVE lock on the matview only. That blocks other refreshes, not readers, and never touches orders or order_items writes.
  • Monitor that refresh duration stays well under 5 minutes.
• Time zone: the original query already returns different results for sessions with different TimeZone settings. If different analysts' drivers set different zones, you have an existing correctness bug. Decide on one business zone and make it explicit.
• CREATE INDEX CONCURRENTLY:
  • It can't run inside a transaction block and takes longer than a normal build.
  • If it fails, it leaves an INVALID index that still incurs write overhead. Check pg_index.indisvalid and drop any leftovers.
  • Between the CREATE and the DROP, both indexes exist, so writes pay double index maintenance for that window. Do it off-peak.
• Covering index: it's ~1.5–2× the size of the index it replaces. Its benefit depends on the visibility map; a stale map means heap fetches anyway.
• Edge cases to verify:
  • customers whose orders all have zero or negative revenue (excluded in both versions);
  • orders with no items (counted in order_count in both);
  • ties at rank 50 (order unspecified in both).

Validation step

Run this on a staging clone or an RDS snapshot restore if you can.

1. Prove identical results. Use one snapshot and one session, so both queries see the same data and time zone.

BEGIN ISOLATION LEVEL REPEATABLE READ;

CREATE TEMP TABLE r_old ON COMMIT DROP AS
SELECT c.id AS customer_id, c.company_name, c.region,
       COUNT(DISTINCT o.id) AS order_count,
       SUM(oi.quantity * oi.unit_price) AS total_revenue,
       MAX(o.created_at) AS last_order_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
     AND DATE(o.created_at) BETWEEN '2026-08-01' AND '2026-08-31'
LEFT JOIN order_items oi ON oi.order_id = o.id
WHERE c.status = 'active' AND c.region IN ('US-EAST','US-WEST','EU-CENTRAL')
GROUP BY c.id, c.company_name, c.region
HAVING SUM(oi.quantity * oi.unit_price) > 0;          -- no ORDER BY/LIMIT: compare the full set

CREATE TEMP TABLE r_new ON COMMIT DROP AS
SELECT * FROM mv_customer_monthly_revenue;            -- or the optimized query with LIMIT removed

SELECT (SELECT count(*) FROM r_old) AS old_rows,
       (SELECT count(*) FROM r_new) AS new_rows,
       (SELECT count(*) FROM (TABLE r_old EXCEPT ALL TABLE r_new) d) AS missing_in_new,   -- expect 0
       (SELECT count(*) FROM (TABLE r_new EXCEPT ALL TABLE r_old) d) AS extra_in_new;     -- expect 0
COMMIT;

If you compare against the matview, refresh it right before so it reflects the same data. For August that doesn't matter.

2. Confirm the plan change. Run the optimized query from above:

SET work_mem = '64MB';
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
<optimized query from above>;

Look for all of the following:

• an Index Scan or Bitmap Index Scan on idx_orders_created_at;
• no Seq Scan on order_items;
• HashAggregate with no "Disk Usage" or "Batches > 1";
• Sort Method: top-N heapsort;
• no external merge anywhere.

3. Confirm p95 under realistic concurrency.

echo "SELECT * FROM mv_customer_monthly_revenue ORDER BY total_revenue DESC LIMIT 50;" > dashboard.sql
pgbench -n -c 40 -j 4 -T 120 -P 10 -l -f dashboard.sql "$DATABASE_URL"
# p95 latency in ms from the per-transaction log (3rd column is latency in microseconds):
cat pgbench_log.* | awk '{print $3}' | sort -n | awk '{a[NR]=$1} END {print a[int(NR*0.95)]/1000 " ms"}'

Also run this pgbench test with the optimized live query in dashboard.sql instead of the matview read. That shows you directly whether the rewrite alone meets 300ms at 40 concurrent sessions.

If you send the EXPLAIN (ANALYZE, BUFFERS, SETTINGS) output and the August counts, I can tighten these estimates and tell you whether the optional covering index is worth it.