Making a 3-second list endpoint answer in 25 ms

The busiest screen on the platform I work on is a paged list of leads. Every role uses it. Admins see everything, and a single location's staff see a handful of records. On a production-sized copy of the database, one page of 25 rows took three to five seconds to load.

Nobody wants to wait that long for a list. It's also the first screen people open, so it set the tone for the whole product.

This post covers the two rounds of work that took the admin view from 3.3 s to 25 ms. Neither round added an index or changed a database setting. Both came from reading the query plan (PostgreSQL's description of how it will run a query) carefully. Each time, I found PostgreSQL doing a lot of work I had asked for without meaning to.

The system and the query

The product is a multi-tenant lead-routing platform. Partners send in leads: people who asked to be contacted. Each lead goes to the nearest location. If no location is close enough, it goes to a fallback team matched on language and country. Status then syncs back to the partner and to the location's own CRM.

The list endpoint, ListLeadsFiltered, returns one page of leads. Each row carries quite a lot of extra data:

• a JSON array of the locations the lead is assigned to
• the lead's latest course and progress
• a count of suspected duplicates
• a PostGIS (PostgreSQL's geography extension) flag saying whether the lead's position disagrees with its assigned location

On top of that sits row-level access control, meaning each user only sees certain rows:

• A region user sees leads assigned anywhere in their region.
• A district user sees leads in their districts.
• A location user sees leads held by their location, and by any other record for the same physical building. We merge duplicate location records with a merged_into_id column.
• Admins see everything.

So the query has to answer two questions. Which leads is this caller allowed to see? And what does each of those rows look like once all the extra data is attached?

Round one: a cast that defeated the primary key

I started with the location-user path, because it was oddly slow. A location user sees far fewer rows than an admin. Yet their list took 2.29 s, which made no sense for the amount of work.

The check for "which locations make up my building" looked like this. It was an EXISTS subquery tied to each candidate lead, inside a per-row LATERAL join (a join that runs once for every row):

OR (la2.location_id IS NOT NULL AND EXISTS (
        SELECT 1
        FROM locations l_grp
        JOIN locations l_own ON l_own.id::text = ANY(@crm_location_ids)
        WHERE l_grp.id = la2.location_id
          AND COALESCE(l_grp.merged_into_id, l_grp.id)
            = COALESCE(l_own.merged_into_id, l_own.id)
          AND l_grp.crm_id IS NOT DISTINCT FROM l_own.crm_id
   ))

Two things in that snippet make it expensive.

The first is l_own.id::text = ANY(...). The id column is a uuid primary key with a btree index, the standard sorted index. That index holds uuid values in uuid order. Once you write id::text, you're no longer comparing the indexed column. You're comparing a new value computed from it.

PostgreSQL can't use the uuid index to find rows by their text form. So it reads every row of locations, casts each id, and compares. This sat inside an EXISTS that ran per lead, so that full scan could happen once for every candidate row.

The second is the join condition COALESCE(merged_into_id, id) = COALESCE(...). An index on merged_into_id and an index on id don't help when you join on an expression that mixes both. That's another condition the planner could only meet by brute force.

The fix was to stop asking the question per row. The caller's own locations and their building group don't depend on which lead we're looking at. So they can be worked out once, in a CTE (a named subquery at the top of the query, written with WITH):

WITH caller_own AS (
    SELECT l.id, COALESCE(l.merged_into_id, l.id) AS grp, l.crm_id
    FROM locations l
    WHERE l.id = ANY(ARRAY(
            SELECT s::uuid FROM unnest(@crm_location_ids::text[]) AS s
            -- The array also carries a partner's own location references,
            -- which are not uuids. Only cast what actually is one.
            WHERE s ~ '^[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}
#x27;))
),
caller_scope_locations AS (
    SELECT o.id FROM caller_own o
    UNION
    SELECT g.id FROM caller_own o
    JOIN locations g
      ON (g.merged_into_id = o.grp OR (g.merged_into_id IS NULL AND g.id = o.grp))
     AND g.crm_id IS NOT DISTINCT FROM o.crm_id
)
...
OR (la2.location_id IN (SELECT id FROM caller_scope_locations))

There are three changes in there:

1. 1.The cast moved from the column to the parameter. l.id = ANY(ARRAY(SELECT s::uuid ...)) compares the indexed uuid column with uuid values, so the primary key index does the lookup. The regex filter is there because the parameter array also holds references from partner systems that aren't uuids. Casting those would raise an error.
2. 2.COALESCE(merged_into_id, id) = grp became merged_into_id = grp OR (merged_into_id IS NULL AND id = grp). It means the same thing. But each side of the OR is now a plain comparison on one column, so each can use its own index.
3. 3.The whole building lookup became a CTE that runs once. The per-row check became a cheap IN against its result.

This took the location-user list from 2.29 s to 1.84 s, against an unscoped admin list at 3.01 s, on about 55K locations and 50K leads.

Before calling it done, I ran the old and new queries for every location account and compared the results. They were identical: zero rows lost and zero gained. I also added an integration test that times the scoped list against the unscoped one, so this query shape can't quietly come back.

But 1.84 s isn't fast. The commit message for that change says so plainly: the remaining cost was building every row before applying LIMIT. That was the next problem.

Round two: building 50,000 rows to show 25

The second round targeted the whole endpoint, for every role. The symptom was simple: 3 to 5 seconds per page on a production-sized copy. The plan showed two separate causes.

Cause one: enrich everything, keep 25

The query was written in the natural order:

1. 1.Take every lead that matches the filters.
2. 2.Attach its location array, progress, duplicate count and location-mismatch flag.
3. 3.Sort the result.
4. 4.Apply LIMIT 25 OFFSET n.

The problem is that LIMIT came last. PostgreSQL did the expensive enrichment for every matching lead and sorted all of them. Then it threw away everything except one page. At around 50,000 leads, that was roughly 2.8 seconds of work whose output went straight in the bin.

Cause two: a cost estimate that switched on JIT

The second cause was harder to spot. PostgreSQL has a just-in-time (JIT) compiler that can turn parts of a query into machine code. Compiling takes time, so PostgreSQL only does it when the planner thinks the query is expensive enough to be worth it.

The trigger is jit_above_cost. If the plan's estimated cost is above that number, the query gets JIT-compiled.

The planner's estimate for this query was between 1.7 million and 123 million cost units, depending on the role and filters. The real run time, with the data in memory, was about 30 ms. The estimate was off by orders of magnitude.

Because it was above jit_above_cost, PostgreSQL spent 2 to 3 seconds compiling a query that only needed 30 ms to run. On every single request.

The inflated estimate came from how the filters were written:

• The role scope was a correlated EXISTS per row (a subquery that refers back to the outer row), combined with a district check using OR. The planner has to assume the worst case for each branch.
• One branch still compared location_id::text = ANY(...), which no index can serve.
• Region and district filters started from every lead and worked outwards. They should have started from the small set of regions and worked inwards.
• The sort keys were looked up per lead.

Here's the shape of the old role scope, checked for every lead:

AND (
    (@crm_location_ids::text[] IS NULL AND sqlc.narg(district_scopes)::text[] IS NULL)
    OR EXISTS (
        SELECT 1 FROM lead_assignments la_tc
        WHERE la_tc.lead_id = l.id
          AND (
              (la_tc.crm_location_id IS NOT NULL AND la_tc.crm_location_id = ANY(@crm_location_ids))
              OR (la_tc.location_id IS NOT NULL AND la_tc.location_id::text = ANY(@crm_location_ids))
              OR (la_tc.location_id IN (SELECT id FROM caller_scope_locations))
          ) ...)
    OR (
        sqlc.narg(district_scopes)::text[] IS NOT NULL
        AND EXISTS (
            SELECT 1 FROM lead_assignments la_d
            JOIN locations loc_d ON loc_d.id = la_d.location_id
            WHERE la_d.lead_id = l.id
              AND LOWER(TRIM(loc_d.district)) = ANY(
                  SELECT LOWER(TRIM(s)) FROM unnest(sqlc.narg(district_scopes)::text[]) AS s) ...))
)

The rewrite: pick the page first, then build it

The new query splits the two questions apart. First it works out which leads the caller may see, as a set. Then it picks one page of ids, using only the columns the sort and filters need. Only after that does it do the expensive enrichment, and only for those 25 ids.

district_locations AS (
    SELECT loc_dc.id FROM locations loc_dc
    WHERE LOWER(TRIM(loc_dc.district)) IN (
              SELECT LOWER(TRIM(s)) FROM unnest(sqlc.narg(district_scopes)::text[]) AS s)
),
scope_leads AS (
    SELECT la_tc.lead_id FROM lead_assignments la_tc
    WHERE la_tc.crm_location_id = ANY(@crm_location_ids::text[]) AND (...)
    UNION
    SELECT la_cs.lead_id FROM lead_assignments la_cs
    WHERE la_cs.location_id IN (SELECT id FROM caller_scope_locations) AND (...)
    UNION
    SELECT la_d.lead_id FROM lead_assignments la_d
    WHERE la_d.location_id IN (SELECT id FROM district_locations) AND (...)
),
-- Choose the page FIRST, then build it.
page AS (
    SELECT l.id, <sort_asc>, <sort_desc>, l.updated_at AS sort_updated_at
    FROM leads l
    LEFT JOIN ( SELECT la2.lead_id, MIN(la2.assigned_at) ..., MIN(loc2.name) AS first_location_name
                FROM lead_assignments la2 LEFT JOIN locations loc2 ON loc2.id = la2.location_id
                WHERE @sort_by::text IN ('nearest_location_name','delivered_at','assigned_at') ...
                GROUP BY la2.lead_id ) assignments ON assignments.lead_id = l.id
    WHERE ... AND ((@crm_location_ids::text[] IS NULL AND sqlc.narg(district_scopes)::text[] IS NULL)
                   OR l.id IN (SELECT lead_id FROM scope_leads))
    ORDER BY sort_asc ASC, sort_desc DESC, sort_updated_at DESC, l.id DESC
    LIMIT @lim OFFSET @off
)
SELECT l.id, ..., COALESCE(sp_agg.progress, '[]'::jsonb) AS progress
FROM page p
JOIN leads l ON l.id = p.id
LEFT JOIN LATERAL (...)   -- the heavy enrichment now runs for 25 rows only

Each part has one job:

• district_locations turns the caller's districts into location ids once. Before, a district name was re-checked for every lead.
• scope_leads replaces the per-row EXISTS ... OR ... with a UNION of three plain lookups. Each branch is a simple indexed query on lead_assignments, and the result is just a set of lead ids. The planner can estimate a set like that far better than a chain of correlated subqueries joined by OR.
• page selects only ids and sort keys. It applies the scope as l.id IN (SELECT lead_id FROM scope_leads), sorts, and applies LIMIT. The aggregate that produces the sort keys only runs when @sort_by asks for that sort. The final l.id DESC makes the order unique, so the same lead never shows up on two pages.
• The outer SELECT joins back to leads and runs the expensive LATERAL enrichment for the 25 ids in page. Nothing else.

This pattern is often called late materialization. You work out which rows you need using the cheapest columns you can. Then you fetch and build the full rows only for those.

After the rewrite, every list view estimated between 10K and 35K cost units, well below jit_above_cost. I kept everything else the same on purpose: no new index, no change to the JIT settings, no schema change.

Turning JIT off globally would have hidden the symptom. But the query would still have been building tens of thousands of rows to show 25.

Proving the results didn't change

A rewrite this large is easy to get subtly wrong. One misplaced OR in an access filter could show a location user someone else's leads, or hide their own. Faster but wrong is worse than slow.

So before merging, I ran the old and new queries side by side across 40 combinations of role, sort order, page and filter. I compared the results row by row. Both the list and the total count matched exactly in every case.

Results

These timings come from the commit: 10 API calls each, against a production-sized copy, with the server's default JIT setting.

The scoped roles are slower than the admin view because they still build their scope set. But every role went from seconds to well under a quarter of a second.

Takeaways

• Pick the page using only the columns the sort and filters need, then enrich just that page. Expensive work should grow with the page, not the table.
• Rewrite per-row access checks (EXISTS ... OR ...) as a set built once from a UNION of indexed branches.
• Never put a cast or function on an indexed column in a filter. Cast the parameter instead.
• Split COALESCE(a, b) = x into a = x OR (a IS NULL AND b = x) so each side can use an index.
• Watch the planner's cost estimate, not just the run time. A wildly high estimate can trigger JIT compilation that costs more than the query.
• Prove a rewrite returns identical results before you ship it. Add a performance test for query shapes that are easy to bring back.