Indexing Basics for .NET Developers

Indexes are for predicates, not for tables

The single most useful reframing: you do not index a table, you index the shape of a predicate. Start from the SQL your repository emits.

const string sql = @"
    SELECT PostId, Title, Slug
    FROM BlogPost
    WHERE Published = TRUE AND IsDeleted = FALSE
    ORDER BY CreatedOn DESC
    LIMIT @PageSize OFFSET @Offset";

That query wants one index:

CREATE INDEX IdxBlogPostPublished
    ON BlogPost (Published, CreatedOn DESC);

Partial indexes

When a predicate is nearly always the same constant, push it into the index and shrink it dramatically.

CREATE UNIQUE INDEX IdxBlogUserUserName
    ON BlogUser (UserName)
    WHERE UserName IS NOT NULL;

Expression indexes

Case-insensitive lookups need the expression indexed, not the column.

CREATE UNIQUE INDEX IdxVerifiedEmailEmail
    ON VerifiedEmail (LOWER(Email));

Query it the same way or the index is ignored:

• WHERE LOWER(Email) = LOWER(@Email) - index used
• WHERE Email ILIKE @Email - sequential scan

Every index you add is paid for on every write. Add them because a plan told you to, not because a column "looks searchable".

A checklist

1. Find the slow query.
2. Read its plan (that is part two).
3. Add the narrowest index that removes the sequential scan.
4. Re-measure. Delete the index if nothing improved.