Speeding up a CRM query over 2.69 million leads
At HCL GUVI, I optimized DORA’s re-enquiry filter for about 2.69 million leads. Only around 2% had a re-enquiry date, so I added a partial index for that subset. The measured page query fell from 2,680 ms to 5.6 ms—about 480 times faster. Subtracting the indexed count from a precomputed total also reduced empty-date counts from 1,400–1,900 ms to about 79 ms. These October 2026 project figures are database-query timings, not end-to-end page-load measurements.
Two query paths for two different populations
- With a date: partial index → matching page
- Without a date: precomputed total − dated count
The problem: scanning a large table for a small subset
DORA’s Leads Tracker needed a re-enquiry-date filter and a matching table column. Only about 2% of the roughly 2.69 million lead records had a date, yet retrieving a page could walk through millions of records to locate that sparse subset.
I owned the re-enquiry filter and the query optimization. The useful result was a faster database operation behind an everyday workflow, rather than a new database system or a claim that all CRM interactions became hundreds of times faster.
Index the records the filter actually needs
I added a partial index containing the leads that have a re-enquiry date. Instead of treating every row as equally relevant, the database can use an index focused on the small group the filter selects. This matched the actual distribution of the data rather than assuming a date was common.
The important design choice was to separate two different questions: finding the small set with dates, and counting the large set without them. An approach that helps the first does not automatically make a count over almost the entire table cheap.
Avoid counting the same large complement repeatedly
The “is empty” condition matched about 98% of the records. For that count, I used an existing precomputed total and subtracted the indexed count of leads with a date. This avoided repeatedly scanning the large empty-date population.
That choice depends on the precomputed total’s freshness and scope. It should not be generalized to arbitrary filters or a different counting contract without checking those assumptions. The technique was applied to this specific CRM filter, using the totals already maintained by the product.
Measured outcome and what it does not establish
The recorded re-enquiry page query changed from 2,680 ms to 5.6 ms, approximately a 480-fold improvement in that query. The empty-date count went from a measured 1,400–1,900 ms to about 79 ms. These figures come from the October 2026 project work recorded in PR #114.
They are database-query timings, not measurements of the entire page, network round-trip or browser rendering. The project write-up does not document a repeated statistical benchmark, a full hardware specification or a controlled cache study, so this page does not invent those details or present the result as a universal benchmark.
The broader lesson is to inspect the shape of the data before adding more infrastructure. Here, a sparse-field index and a different counting strategy addressed the measured bottleneck within the existing PostgreSQL application.