Press "Enter" to skip to content

Category: Indexing

Metadata-Based Counting and Filtered Indexes

Published 2023-12-21 by Kevin Feasel

Aaron Bertrand counts more efficiently:

That’s great when you want to count the whole table without size-of-entire-table reads. It gets more complicated if you need to retrieve the count of rows that meet – or don’t meet – some criteria. Sometimes an index can help, but not always, depending on how complex the criteria might be.

For me, counting more efficiently typically means I take off my shoes.

One other note is, if you just need a guesstimate, or if the cardinality of that column you’re splitting by is fairly low, you could also look at the histogram, especially if there’s a statistic on the column (or columns) you’re interested in. It’s rare that I think to go that way, but it is one of the tools the optimizer itself uses, so it’s fair game.

Comments closed

B-Tree Indexes in Postgres

Published 2023-12-13 by Kevin Feasel

Henrietta Dombrovskaya continues a series on indexing in PostgreSQL:

In the previous article we learned that the most helpful indexes are indexes with the lowest selectivity, which means that each distinct value in an index corresponds to a small number of rows. The smallest number of rows is one, thereby, the most useful indexes are unique indexes.

The definition of a unique index states just that: an index is unique if for each indexed value there is exactly one matching row in the table. PostgreSQL automatically creates a unique index to support any primary key or unique constraint on a table.

Read on for more about unique indexes, compound indexes, and bitmaps.

Comments closed

Optimizing Sort Operators in Window Functions

Published 2023-12-06 by Kevin Feasel

Andy Brownsword talks about window function query tuning:

We’re on quite a roll with window functions these past few weeks. Last week we looked at the operators we’d see in execution plans when using a window function. This week I wanted to tackle one of the more troublesome ones specifically: the Sort operator.

We know that sort operators are expensive in our queries. To use a window function our data needs to be sorted. How about if we need multiple functions? What if we’d like the output sorted too? Can we optimise any of those out of the execution plan?

Read on for several tips.

Comments closed

Self-Join Optimizations and Index Intersection

Published 2023-11-27 by Kevin Feasel

Daniel Hutmacher shows off a possibility:

This blog post started as a “what if” contemplation in my head: Suppose you have a reasonably large table with a clustered index and a number of non-clustered indexes. If your WHERE clause filters by multiple columns covered by those non-clustered indexes, could it potentially be faster to rewrite that WHERE clause to use those non-clustered indexes?

The answer might surprise you.

To be honest, when I learned about the concept originally, I expected that there would be a great deal of use cases for it. But SQL Server rarely comes up with this answer on its own and I think that’s because in most scenarios, we’d need to do additional sorting or other expensive operations to get the multiple indexes aligned just right to make this the faster option.

Comments closed

Primer on Indexing and Partitioning in Postgres

Published 2023-11-20 by Kevin Feasel

Salman Ahmed gives us a 10,000 foot view of two topics:

When it comes to managing large and complex databases in PostgreSQL, an important decision you’ll face is how to optimize your data storage and retrieval strategies. Two common techniques for improving database performance and manageability are indexing and partitioning in PostgreSQL.

Read on for a quick overview of each topic, including the variety of index types and partitioning strategies available.

Comments closed

Setting up Ola’s Index Maintenance with Azure Runbooks and Terraform

Published 2023-10-20 by Kevin Feasel

Josephine Bush builds on prior work:

Yes, you still need to do some work to maintain indexes in Azure SQL Database. This post will walk you through setting up statistic updates and index maintenance using Terraform.

Thanks to Tracy Boggiano for her directions for setting up the runbooks. If you want to do this manually instead of with Terraform, Tracy’s post walks you through it step by step. I only modified the role assignment so it had read to the entire subscription level to loop through every DB in the subscription.

Thanks to Kendra for blogging about index maintenance in Azure SQL. Her post helped me decide on index maintenance thresholds.

Click through for a link to Josephine’s GitHub repo and a walkthrough of how it all works.

Comments closed

Indexing in PostgreSQL

Published 2023-10-10 by Kevin Feasel

Henrietta Dombrovskaya continues a series on Postgres:

What is an index? One might assume that any person who works with databases knows what an index is. However, a surprising number of people, including database developers and report writers and, in some cases, even DBAs, use indexes, even create indexes, with only a vague understanding of what indexes are and how they are structured. Having this in mind, let’s start with defining what is an index.

Since there are many different index types in PostgreSQL (and new index types are constantly created) we won’t focus on structural properties to produce an index definition. Instead, we define an index based on its usage.

Indexing is one area in which SQL Server and Postgres differ, as SQL Server relies on clustered indexes for storage and “default” operations, whereas Postgres has a different model.

Comments closed

Restoring Azure SQL DB Indexes

Published 2023-09-13 by Kevin Feasel

Brent Ozar answers a question:

I got an interesting request for consulting, and I’m going to paraphrase it:

We were using Azure SQL DB with automatic index tuning enabled for months. Things were going great, but… we just deployed a new version of our code. Our deployment tool made the database schema match our source control, which… dropped the indexes Azure had created. How do we get them back?

Read on for Brent’s answer.

Comments closed

An Overview of SQL Server Indexes

Published 2023-09-07 by Kevin Feasel

Adron Hall gives us a tour of indexes in several relational database management systems:

1. Structure of the Craft: Dive deep, and you’d find tree-like structures, be it the B-tree or its illustrious cousins – the B+ tree and B* tree. These aren’t ordinary trees; they’re a labyrinth that efficiently guides the system to the row it seeks in a jiffy.
2. Guiding Stars – Pointers: Each entry in this labyrinth isn’t just a dead end. It carries a key value and – wait for it – a pointer. Think of it as a magical compass pointing directly to the treasure, or in this case, the row in the table.
3. Supercharged Searches: Now, imagine sifting through a library without a catalog – agonizingly slow, right? That’s how a database without an index feels. But bring in an index, and suddenly even the vastest of tables become a playground of swift searches.

Read on for an overview of what purposes indexes fulfill in these data platforms. I use “purposes they fulfill” rather than “types of indexes” because there are a couple entries on the list which are not, strictly speaking, actual types of indexes.

Comments closed

Index Maintenance in Azure SQL DB with Elastic Jobs

Published 2023-08-22 by Kevin Feasel

Scott Klein continues a series on index maintenance in Azure SQL Database:

It’s finally here: the third and final blog post about Azure automation. The first blog covered how to automate Azure using Runbooks, the second blog post showed how to do it using Azure Functions, and this blog post will cover how to do it using Azure Elastic jobs.

To be fair, I titled this blog “Automating Azure with Elastic Jobs”, but Elastic Jobs isn’t part of Azure Automation, so please don’t get confused. The goal with this is to demonstrate how to automate some Azure database DBA tasks.

Read on for a brief primer on elastic jobs and how to use them.

Comments closed