Performance and indexing

Pagination turns a screen into a query shape your database will serve thousands of times, and the config is where that shape gets decided. This page is what to index, what a page actually costs, and where the design stops being cheap.

What one request costs

Every page is two queries: a COUNT(*) over the filtered set, then the page fetch. They share the same WHERE, so a filter that is expensive is expensive twice.

There is one saving built in: when the count comes back 0, the second query is never sent. A filter that cannot match anything therefore costs exactly one count, and a page past the end costs the same.

An unlimited read is the exception to the whole paragraph: it is one query and no count at all, because the rows it fetches are the count. It is not free, though — that single statement still orders the entire ceiling-bounded set, so everything under Index the sort applies to it with maxRows in place of limit.

The count is usually the expensive half. It cannot use LIMIT to stop early — it has to resolve the whole matching set, however large — while the page fetch stops after limit rows once the ordering is satisfied by an index.

Index what you exposed, not what you query

The config is a published list of query shapes. Read it as an index checklist:

• every Sortable field is an ORDER BY a client can ask for;
• every Filterable field is a WHERE clause, and every operator you granted is a different predicate against it;
• every Searchable field is a LIKE '%term%' — which no ordinary B-tree index can serve.

The ones that hurt are the ones you granted without meaning to. $ilike on an unindexed text column is a sequential scan any caller can trigger, on demand, as often as they like. Granting Eq and In and stopping there is a performance decision, not just a security one.

Four cases where the predicate the engine emits is not the one the checklist above suggests:

Shape	What is emitted	Index consequence
$sw on a string, portable strategy	col LIKE 'Wid%'	the one anchored pattern the engine produces, and a B-tree (text_pattern_ops on PostgreSQL, or a C-locale database) serves it as a range scan
$sw after .UsePostgreSql()	col ILIKE 'Wid%'	PostgreSQL serves ILIKE from a B-tree only when the pattern starts with non-alphabetic characters (§11.2), so an alphabetic prefix drops to a sequential scan. A digit-leading SKU, order number or phone prefix is unaffected. Registering the strategy is what makes that choice — see PostgreSQL
$ilike / $contains on a string	col LIKE '%term%'	never index-servable by a B-tree under either strategy. A pg_trgm GIN or GiST index is the answer on PostgreSQL, for both strategies
$contains on a collection	one value = ANY(col) predicate per value, AND-ed	a PostgreSQL array_ops GIN index indexes &&, @>, <@ and = over two arrays, not a scalar against an array column, so it does not serve this. The array is unnested per row, and the SQL text grows with the caller's value count

$in is the well-behaved one: the whole list is sent as a single collection parameter, so one SQL shape and one plan-cache entry serve every cardinality. That is deliberate and is not configurable — EF Core 10's UseParameterizedCollectionMode and EF.Constant(...) do not reach it, because the engine wraps the array in EF.Parameter before EF sees it. Without the wrap the values would be inlined as literals and you would get one statement per distinct list length instead. The emitted shape is col = ANY(@p) on PostgreSQL and col IN (SELECT value FROM json_each(@p)) on SQLite, identical at 3, 30 and 100 values.

The single parameter has a cost on PostgreSQL, and it is worth knowing before you blame the index. A parameter's contents are invisible to the planner, so once a statement is planned generically the row estimate for = ANY(@p) is a fixed guess rather than anything derived from the list you sent. Measured on a 200 000-row table with a deliberately skewed distribution, a two-value $in matching 2 324 rows was planned for 37 265 — a 16× over-estimate, enough to move the planner off a plan that suits the rows actually returned. Inlined literals would estimate almost exactly, which is the trade EF Core 10 made when it moved its own default; this engine keeps the parameter because the alternative is a new query text, and a new plan-cache entry, for every distinct list a client sends. If a highly selective $in over a large skewed table plans badly, that is the mechanism — pg_hint_plan, a partial index, or splitting the query are the levers, not a library setting.

The first paginated call of a process is slow

Roughly 20 ms of type initialisers plus JIT, measured as ~24 ms for the first ApplyPagination against 0.006 ms once warm — about 3 600×. Most of it is one type: the filter parser's frozen operator tables cost ~13 ms to build, and they are built once per process, not per request.

It is a constant, not a leak, and on a long-lived host it disappears into startup. It matters in two places: a cold-started serverless instance, where the first request a container ever serves pays it, and a p99 measured across scale-out, where every new instance contributes one slow request. If either describes your deployment, issue one throwaway paginated query at startup — an ApplyPagination against an empty queryable is enough, since it composes without touching the database — and the cost moves out of the request path.

Index the sort, including the tie-breaker

The emitted ORDER BY is not what you declared — the tie-breaker is appended to it:

ORDER BY "p"."Status", "p"."Rank" DESC, "p"."Id"

So a covering index has to include the tie-breaker column as its last key, in that order, or the database sorts anyway:

CREATE INDEX ix_products_status_rank_id ON products (status, rank DESC, id);

This is worth checking against a real plan rather than assuming. An index on (status, rank) alone looks right and still leaves a sort node in the plan, because two rows tied on both still need ordering by id.

Sorts your callers actually send are worth indexing; the full cross-product of Sortable fields is not. Look at what the clients ask for before adding five indexes.

One sort no index on this table can serve. A Sortable whose selector crosses a navigation — Sortable("category", p => p.Category!.Name) — emits a LEFT JOIN and then orders by a column of the joined table with the tie-breaker of the paged one:

ORDER BY "c"."Name", "p"."Id"

No index on products has that as a prefix, because its leading key lives elsewhere. Deep pages are the worst of it: OFFSET n on top of a join that has to be ordered first. If such a field is exposed, either keep it shallow with WithMaxOffset, denormalise the column onto the paged table, or leave it out of the config.

Deep pages are the cliff

Skip(n) compiles to OFFSET n, and a database serves OFFSET 50000 by producing fifty thousand rows and discarding them. Cost grows with the page number, so page 1 benchmarks fine and page 500 does not.

Three responses, in increasing order of effort:

1. Cap it. WithMaxOffset(n) rejects a request that would skip more than n rows. The check is arithmetic and runs before the count, so a guarded deep page costs no query at all — which is the point: a 400 is cheaper than the query it prevents.
2. Raise limit instead of page. Walking a set in 500-row pages touches the offset problem twenty times less often than 25-row pages do. This is what a batch export should do — see Pagination without ASP.NET Core.
3. Filter instead of paging. A client that wants the tail usually wants a different sort, not page 500. ?sortBy=createdAt:DESC beats paging to the end of createdAt:ASC, and costs the same as page 1.

Keyset pagination avoids the problem entirely, and this library does not implement it: the contract is page and limit, which is what makes a total count and random page access possible in the first place.

Keep MaxLimit honest

MaxLimit is the worst case you have agreed to serve — one request, that many rows, plus whatever the projection pulls per row. Set it against the width of the projection, not by habit. A MaxLimit of 1000 over a DTO with a sub-collection is a very different promise from 1000 over four scalar columns.

Keep the projection narrow

The strategy you pick decides how many columns cross the wire. Only PaginateMapAsync materialises the whole entity; the other three send a SELECT list built from what the DTO actually names. On a wide table that difference dwarfs anything above.

The response repeats the query string

Everything above is about the database. One cost sits on the way out instead: every parameter the request sent is re-emitted in all five navigation links, and again in each of the four rels of the opt-in Link header. That includes parameters the library does not recognise — it carries them so client-side state survives paging.

The measured multipliers, and why the Link header is the half that bites rather than the body, are on the response reference. This page is the what-to-do-about-it half, and it only matters if your callers send long query strings and you write the header:

• Cap the request line below your proxy's header buffer ÷ 4 — the divisor is the four rels the header carries, so the usable ceiling is lower than the buffer suggests.
• Or build the link context from a filtered parameter list, keeping only what the client needs echoed back across pages.
• Or do not write the header. The five links in the body carry the same navigation, and the body is usually noise beside the rows.

Measuring it

ApplyPagination composes the page query and hands it back unexecuted, so ToQueryString() gives you the statement before it runs — no database round-trip, no log scraping:

string sql = db.Products.ApplyPagination(request, config).Query.ToQueryString();

Filters, search, ordering and Skip/Take are exactly what the engine would run, because both compose through one code path. The projection is not — the composer adds no Select, so the SELECT list you see is the whole entity rather than the narrowed one the section above is about. To measure column width, apply your own selector to Query (or to the source directly) before printing. See Query composers for both composers and what each validates.

To see what ran in production, where you cannot re-issue the request, log it instead:

options.UseNpgsql(connectionString).LogTo(Console.WriteLine, LogLevel.Information);

Two statements per request, and both are worth putting through EXPLAIN once with realistic data volumes. Values arrive as parameters rather than literals, so one plan is reused across everything your callers send — which is good for the cache and means a plan you check once stays the plan you get.

Shapes are not parameters

Values are parameterised; shapes are not, and cannot be. A request filtering name builds a different expression tree from one filtering rank, so every distinct combination of filter fields, operators, $not, connectors, sort keys and directions is its own EF compiled-query cache entry — two of them, in fact, one for the count and one for the page. EF's cache holds on the order of a thousand entries and evicts beyond that; measured here, a first compilation costs roughly six times a warm request, all of it CPU.

For an ordinary API this never shows up: real clients reuse a handful of shapes and everything stays cached. It matters when a resource with many filterable fields is exposed to untrusted or merely erratic callers — fourteen fields alone give over sixteen thousand legal field combinations, multiplied again by the operator choice, and every one of those requests is perfectly valid. No guard can see it, because WithGuards counts values and fields, not distinct shapes. The lever is the size of the config: a field you did not declare is a shape nobody can ask for.