Press "Enter" to skip to content

Category: Indexing

Rolling Filtered Indexes in SQL Server

Published 2025-08-19 by Kevin Feasel

Aaron Bertrand only needs recent data:

I recently resolved an issue where a query pulling data from the last 30 days would time out due to the table’s size and the lack of a supporting index. Creating a supporting index is possible, but not ideal; it will be very large and may not be useful for most queries and use cases. I wonder how I could implement a filtered index that follows time and is always limited to the last n days.

Read on to see how Aaron did this. It’s a clever solution and Aaron does make clear the tricky part of filtered indexes: explicit conditions.

Leave a Comment

Thoughts on Index Rebuilds in PostgreSQL

Published 2025-08-12 by Kevin Feasel

Laurenz Albe shares some advice:

People often ask “How can I automatically rebuild by indexes regularly?” or “When should I rebuild my indexes in PostgreSQL?”. That always gives me the feeling that they want to solve a problem that isn’t there. But the REINDEX statement is certainly there for a reason, and sometimes it is perfectly reasonable to rebuild an index. In this article, I’ll explain when it makes sense to rebuild an index and how you can get the relevant data to make that decision.

Read on to learn more.

Leave a Comment

Digging Further into RTABench Q0 Optimization on PostgreSQL

Published 2025-08-08 by Kevin Feasel

Andrei Lepikhov responds to feedback:

In the previous post, I explored some nuances of Postgres related to indexes and parallel workers. This text sparked a lively discussion on LinkedIn, during which one commentator (thanks to Ants Aasma) proposed an index that was significantly more efficient than those discussed in the article. However, an automated comparison of EXPLAINs did not clarify the reasons for its superiority, necessitating further investigation.

Click through for the index and what Andrei learned along the way.

Leave a Comment

Making Leading Wildcard Searches Faster

Published 2025-08-07 by Kevin Feasel

Brent Ozar flips everything around:

99.9% of you are never gonna need this.

But let’s say you need to run queries with leading (not trailing) wildcards, like this search for all the different national versions of the Encabulator, each of which has different prefixes depending on which government it’s being sold to:

This is indeed a pretty uncommon scenario. I’m pretty sure I’ve only ever needed to do this once. Well, twice, but in one case I couldn’t actually use the REVERSE() function because the column was itself an awful non-deterministic function and this solution wouldn’t work.

Leave a Comment

PostgreSQL and Covering Indexes

Published 2025-06-30 by Kevin Feasel

Kendra Little does a bit of index management:

Dear Postgres, Why won’t you use my covering index?

Lately I’ve been learning to tune queries running against PostgreSQL, and it’s pretty delightful. One fun question that I worked through struck me something that most Postgres users probably encounter one way or another: sometimes you may create the perfect index that covers a given query, but the query planner will choose to ignore it and scan the base table.

Why in the world would it do that?

Read on to learn why.

Comments closed

Index Maintenance for Large Databases

Published 2025-06-27 by Kevin Feasel

Jon Russell shares some thoughts:

Most database administrators are comfortable with the daily tasks of backup, CHECKDB, index maintenance, and statistics updates. The available guidance for those topics is extensive. Much less has been written about caring for estates that contain hundreds of databases—or a single database large enough to strain conventional maintenance windows. This post will focus on options that help when individual tables or complete databases grow beyond the point where “standard” maintenance jobs finish in a reasonable time.

Click through for Jon’s advice. My big thing is to stop worrying and learn to love the bomb fragmentation. Index fragmentation was very important back in the days of spinning disk, where accessing pages took much longer if you needed to move the disk head. With NVMe storage, all-flash arrays, and even more prosaic solid state drives, random access is typically at least as fast as sequential access, so there’s no real benefit to having pages of data be contiguous.

Comments closed

Using Filtered Indexes in SQL Server

Published 2025-05-28 by Kevin Feasel

Stephen Planck explains the value (and limitations) of filtered indexes:

When a table holds millions of rows yet most queries touch only a small, well-defined subset, a traditional non-clustered index feels like using a searchlight to find something sitting under a desk lamp: the index still stores an entry for every row even though the workload rarely needs most of them. SQL Server’s answer is the filtered index—introduced in SQL Server 2008 and still under-used today—allowing you to index just the rows that match a predicate you supply in a WHERE clause.

I always thought I would get more value out of filtered indexes. Instead, they’re very useful for specific circumstances, but not the all-purpose performance dynamos you’d want them to be, particularly because of the limitations.

Comments closed

JSON Indexes in SQL Server 2025 CTP 2.0

Published 2025-05-20 by Kevin Feasel

Daniel Hutmacher gives it a try:

Starting today, the public preview of SQL Server 2025 is available to download!

One really interesting new feature that got my attention was the addition of JSON indexes. I’m a big fan of everything that makes working with JSON easier, since JSON blobs are so much easier to work with than table variables when you’re moving data from point A to point B. This is especially true when you’re working with complex, relational data.

Daniel lays out some of the limitations of JSON index creation and also some of the performance gains you might see from it. This will be most helpful in data engineering scenarios, shredding JSON from various services, but the normalization purist in me says that if you’re shredding JSON enough to need indexes, it’s probably time to normalize that data.

1 Comment