PERFORMANCE GUIDE
Why Are My Keyset Queries Slow Even When Returning Only 25 Records?
Keyset pagination using primary-key ordering is supposed to be one of the fastest ways to page through large datasets, so a 9-16 second response time returning only 25 records is a clear signal that something upstream of the fetch itself is broken. The delay almost never comes from reading those 25 rows -- it comes from index misses, implicit type coercions, missing composite indexes, or planner mistakes that force a full or partial sequential scan before the keyset filter can take effect. Understanding exactly where the time is going is the first step to cutting that latency to under 100 milliseconds.
Research this with AI
Research smarter with AI
Analyze opportunities and compare insights across leading AI models.
What Keyset Pagination Is Supposed to Do
Keyset pagination -- sometimes called the seek method -- works by recording the last primary key seen on the previous page and using a WHERE clause like WHERE id > :last_seen_id ORDER BY id LIMIT 25. Because the database can use the B-tree index on id to seek directly to that position, it should read almost nothing extra before returning the 25 rows you asked for. In a healthy setup on a table with millions of rows, this pattern routinely executes in single-digit milliseconds.
The pattern fails to deliver that speed when the query the database actually executes differs from the logical query you wrote. Type mismatches, implicit casts, join order problems, and missing covering indexes can all cause the planner to abandon the index seek and fall back to a much more expensive access path. The result is a query that looks simple in your application code but hides enormous work inside the database engine.
Step 1 -- Read the Actual Query Plan Before Guessing
The single most reliable diagnostic tool is EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL, or EXPLAIN FORMAT=JSON in MySQL. Run it against the exact parameterized query your application sends -- not a hand-typed approximation. The plan will show you whether the database is performing an Index Scan, a Bitmap Heap Scan, or a Sequential Scan (Seq Scan). A Seq Scan or a Bitmap Heap Scan with a high Rows Removed by Filter count is the clearest sign that the keyset filter is not being applied at the index level.
Pay attention to the Buffers line as well. A hit count in the tens of thousands for a 25-row result means the engine is reading far more pages from cache or disk than it should. The difference between 50 buffer hits (healthy) and 50,000 buffer hits (broken) on the same logical query is usually the entire explanation for your 9-16 second runtime.
- Capture the real queryEnable query logging or use pg_stat_statements to capture the exact SQL with bound parameter values, not placeholders.
- Run EXPLAIN ANALYZEPrefix the captured query with EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) and run it in a read-only session during off-peak hours.
- Look for Seq Scan and high Rows RemovedEither of these in the plan is a confirmed bottleneck. Note the node that produces them -- that is where to focus.
Type Mismatches and Implicit Casts Kill Index Seeks
One of the most common and least obvious causes is a type mismatch between the column type and the parameter type sent by the application. If your id column is a BIGINT but your ORM sends a VARCHAR or a NUMERIC, the database must cast every row in the index before comparing values. That casting operation makes the index non-sargable -- the planner cannot use the index efficiently and may fall back to a sequential scan.
A concrete example: a Python application using SQLAlchemy passes the keyset cursor value as a string '1048576' instead of an integer 1048576. PostgreSQL may still execute the query correctly because of implicit coercion, but the query plan will show a Filter node instead of an Index Cond node, and that filter runs after reading far more rows. Fix this by ensuring the cursor value is explicitly cast to the correct type in both the application layer and, if needed, in the SQL itself using ::BIGINT or CAST(:last_id AS BIGINT).
Missing or Mismatched Composite Indexes on Multi-Column Sort Orders
Keyset pagination breaks down when the ORDER BY clause does not match the available index. If your query orders by created_at DESC, id DESC but only a single-column index on id exists, the planner cannot use a pure index scan for the ordering. It must sort after reading a large intermediate result set, which is expensive at scale.
The fix is to create a composite index that matches your exact ORDER BY column list in the same direction: CREATE INDEX idx_records_created_at_id ON records (created_at DESC, id DESC). The index must be declared in the same sort direction as the query, because a forward B-tree scan and a backward B-tree scan are not always interchangeable at the planner level depending on your database version and statistics.
Joins and Subqueries That Block Filter Pushdown
If your keyset query involves a JOIN or a subquery, the WHERE id > :last_id condition may not be pushed down to the base table scan. Instead, the planner may execute the full join or subquery first, materialize the result, and then apply the keyset filter to that materialized set. On a table with 10 million rows, materializing the join result before filtering is catastrophically slow.
Examine the plan for Materialize and Hash nodes that appear before the keyset filter is applied. Restructuring the query to move the keyset filter into a CTE that runs first, or rewriting the join so the primary table is filtered before the join, can eliminate the materialization step. In some cases, adding an index hint or adjusting the join order via a lateral join resolves the pushdown failure.
Stale Statistics Cause the Planner to Choose the Wrong Access Path
Database query planners rely on table statistics -- row counts, value distributions, null fractions -- to estimate the cost of each access path. If those statistics are stale because ANALYZE has not run recently or the table has grown rapidly, the planner may underestimate how selective the keyset condition is and choose a sequential scan because it believes the index seek will return too many rows to be worth using.
Run ANALYZE records; on the affected table and re-run your EXPLAIN ANALYZE. If the row count estimate in the plan changes significantly and the plan switches from a Seq Scan to an Index Scan, stale statistics were your root cause. Adjust autovacuum settings or schedule more frequent manual ANALYZE runs on high-write tables to prevent recurrence.
- Check last analyzed timeQuery pg_stat_user_tables for the last_analyze and last_autoanalyze columns on the affected table.
- Run ANALYZE manuallyExecute ANALYZE verbose records; and compare the new plan with the old one.
- Tune autovacuum thresholdsIf the table grows faster than autovacuum triggers, lower autovacuum_analyze_scale_factor for that specific table using ALTER TABLE ... SET (autovacuum_analyze_scale_factor = 0.01).
Connection Pool Exhaustion and Network Round-Trips as Hidden Contributors
Sometimes the query itself executes in under a second but total application latency is 9-16 seconds because the thread is waiting for a connection from an exhausted pool, or because multiple synchronous round-trips are happening before the keyset query fires. Use application performance monitoring (APM) traces or database wait event data to separate actual query execution time from connection wait time.
If pg_stat_activity shows many sessions in the ClientRead or Lock state, or if your connection pool metrics show high wait times, the latency is not a query optimization problem -- it is a concurrency or infrastructure problem. Increasing pool size, switching to a pgBouncer-style pooler in transaction mode, or reducing unnecessary round-trips in the request lifecycle will resolve it without touching the query at all.
Summary: Ordered Checklist to Resolve Slow Keyset Queries
Slow keyset pagination on a small result set is always a solvable problem because the root cause is deterministic -- the database is doing more work than necessary before returning your 25 rows. The fixes are mechanical once you know which bottleneck applies. Work through the checklist below in order, re-running EXPLAIN ANALYZE after each change to confirm the plan improves before moving to the next step.
- Run EXPLAIN (ANALYZE, BUFFERS) on the exact query with real parameter values.
- Confirm the keyset predicate appears as Index Cond, not as a post-fetch Filter.
- Check column types vs. parameter types -- correct any mismatch in the application or with an explicit CAST.
- Verify a composite index exists matching your full ORDER BY column list and direction.
- Check for Materialize nodes caused by joins or subqueries that block filter pushdown.
- Run ANALYZE on the table and compare new vs. old plan estimates.
- Measure connection wait time and query execution time separately using APM or pg_stat_activity.
Frequently asked questions
Why is my keyset query slow if it only returns 25 rows?
The 25 returned rows are not the problem. The slowness comes from work the database does before reaching those rows -- typically a sequential scan, a type coercion that makes the index non-sargable, missing composite indexes, or a join that prevents filter pushdown. Run EXPLAIN ANALYZE to identify which of these applies.
Does a primary key always have an index I can use for keyset pagination?
Yes, in PostgreSQL and MySQL a primary key constraint automatically creates a B-tree index. However, if your ORDER BY clause includes additional columns beyond the primary key, that single-column primary key index may not be sufficient to avoid a sort step. You may need a composite index covering all ORDER BY columns.
What is the difference between Index Cond and Filter in a query plan?
Index Cond means the predicate is evaluated inside the index structure, reading only matching entries. Filter means the predicate is evaluated after rows are fetched from the heap. For a keyset predicate on a primary key, you want Index Cond. Filter at that node signals an index miss.
Can stale table statistics really cause a 9-16 second slowdown on a simple query?
Yes. If statistics suggest the table has far fewer rows than it actually does, or if value distributions are inaccurate, the planner may choose a sequential scan because it underestimates the selectivity of your keyset condition. Running ANALYZE on the table and comparing before-and-after plans will confirm or rule out this cause.
What if my EXPLAIN ANALYZE shows fast execution but the application still sees 9-16 seconds?
This means the latency is not inside the query itself. Common external causes include connection pool exhaustion (the thread waits for a connection), network round-trip overhead, application-side serialization, or multiple sequential queries firing before the keyset query. Use APM tooling to trace where time is actually spent in the full request lifecycle.
Set Up Opportunity Alerts
CaptureIQ supports capture and proposal workflows with human review required. It does not automatically submit proposals to any agency portal.