From 30 Seconds to 3: Optimizing a Query at 15 Million Rows

It was supposed to be a small ticket. A user asked for one more filter on a monthly sales report — nothing unusual, just another dimension to slice the data by.

I opened the endpoint to figure out where to plug it in. First, I ran the report to see the current output, and then just... waited.

Ten seconds. Twenty. Thirty. I sat there staring at a loading spinner on a dashboard, and somewhere around the twenty-fifth second I stopped thinking about the filter entirely.

I was thinking: if I were the one using this report every day, I'd be furious. That moment of frustration — as a user, not as an engineer — is what actually kicked off this whole investigation. The filter request could wait. Something underneath it couldn't.

01Understanding What I Was Actually Dealing With

The report itself wasn't complicated in business terms: monthly sales performance, top-selling products, which vendors were driving the most volume. The kind of view finance and product teams check regularly, not some rare edge-case query.

The complexity was in the data. Our system was logging around 500,000 transactions a day.

A "monthly view" of that isn't a small ask — it means the query has to work through roughly 15 million rows before it even starts aggregating anything. And this wasn't a nightly batch job with room to breathe; it was a live dashboard endpoint, expected to respond the moment someone opened the page.

So before I touched a single line for the new filter, I wanted to understand why it already felt this heavy.

02Reading the Query the Database Actually Ran

The endpoint was built with Sequelize, and the aggregation logic leaned entirely on the ORM — several levels of joins, all auto-generated from the model associations. Convenient to write, but I'd never actually seen what SQL it produced under the hood for this specific case.

So I did the unglamorous thing: pulled the raw query via console.log, and ran it through EXPLAIN ANALYZE in PostgreSQL. What came back was not subtle.

The plan was dominated by nested loop joins, stacked across large tables — exactly the pattern that scales beautifully at small data volumes and falls apart as row counts grow. That's precisely what was happening as our transaction volume kept climbing month over month.

On top of that, I noticed the same aggregate functions — SUM, DATE, and a few others — being recalculated multiple times across the query. Nothing in the query held onto an intermediate result, so every reference recomputed the aggregation from scratch. There was no CTE, no pre-computed layer, nothing shielding the database from redoing expensive work it had technically already done a few lines earlier.

None of this was really "wrong" code. It was the ORM doing precisely what it's built to do: turning model relationships into joins in the most direct way possible. The problem was that "the most direct way possible" and "cheap at 15 million rows" turned out to be very different things.

03Rebuilding It With Intent

I didn't throw Sequelize out of the codebase — for the majority of our application, it's still exactly the right tool. But for this one aggregation-heavy endpoint, I made a deliberate call to step outside the ORM and write the query by hand.

Two changes did most of the work.

First, raw SQL with LATERAL joins. This gave me direct control over join order instead of relying on whatever the ORM decided to generate, and that control mattered — it let PostgreSQL's planner work with a much leaner execution path.

Writing raw SQL also meant giving up Sequelize's built-in protection against SQL injection. Filter inputs had to be handled carefully through parameterized queries rather than any kind of string concatenation — speed was never allowed to come at the cost of safety.

Second, CTEs for the aggregations that kept getting recalculated. Instead of letting SUM and DATE groupings repeat themselves at every reference point in the query, I computed them once in a CTE and referenced that result downstream.

This single change removed a large share of redundant work. The database stopped re-deriving numbers it had already produced moments earlier in the same execution.

Together, these changes gave PostgreSQL a query it could actually reason about efficiently: fewer nested loops, no duplicated aggregation work, and an execution plan that stayed far closer to linear as the date range widened — instead of compounding the way it used to.

04What the Numbers Looked Like Afterward

I tested the same three ranges people actually use in this report:

Range	Before	After
1 day	~1s	~300ms
1 week	~10s	~500ms
1 month	30s+	2–3s

The 1-month view — the default most people open first — dropped from a genuinely unusable 30-plus seconds to somewhere around 2 to 3 seconds. That's where the "15x faster" number came from, and depending on the exact filter combination, some runs landed even higher than that.

What mattered to me just as much: none of this came from adding infrastructure. Same server, same database, same data volume sitting underneath. The only thing that changed was a query that finally matched the shape of the data it was operating on.

05What I Took Away From It

ORMs are genuinely useful, and I'd still reach for one by default — they save time, keep code readable, and remove a lot of repetitive boilerplate. But an ORM only understands relationships between models.

It has no sense of "this aggregation will get reused three times in this query" or "this table just crossed 15 million rows for the month." That awareness has to come from the engineer sitting in front of the execution plan, not from the abstraction sitting on top of it.

What actually pushed me to dig into this wasn't a monitoring alert or a performance dashboard — it was fifteen seconds of impatience while waiting for my own report to load. That discomfort was reason enough to stop, open EXPLAIN ANALYZE, and ask why.

If there's one habit worth carrying forward from this: the moment a report or dashboard starts feeling slow to you as the person testing it, don't build on top of that feeling. Go look at the plan first. More often than I'd like to admit, the answer is sitting right there.

---

This is the kind of problem I enjoy the most — one where the fix isn't about adding more infrastructure, but about understanding the data well enough to make it work smarter. If your product is dealing with reporting or dashboard performance issues like this one, feel free to get in touch — I'd be glad to take a look.