Category: Query Tuning

Umair Shahid takes us through the concept of incremental sort in PostgreSQL:

Incremental sort is a database optimization feature, introduced in PostgreSQL 13, that allows sorting to be done incrementally during the query execution process. Sorting is a common operation in database queries, often necessary when retrieving data in a specific order. PostgreSQL’s query planner uses incremental sort to improve query performance, particularly for large datasets. This feature is enabled by default in PostgreSQL 13 and above. 

Read on to see how it works and some good practices which help maximize the likelihood that you can take advantage of the feature.

Erik Darling has some thoughts:

And generally, the more NOLOCK hints I see, the more money I know I’m going to make.

It shows me four things right off the bat:

• The developers need a lot of training
• The code needs a lot of tuning
• The indexes need a lot of adjusting
• There are probably some serious bugs in the software

Perhaps the only other thing that signals just how badly someone needs a lot of help is hearing “we’re an Entity Framework only shop”.

Cha-ching.

I have to admit, even being a consultant doesn’t soften the pain of walking into a place and seeing people use NOLOCK like they picked up a fresh pallet of it from Costco and need to use it up before it goes bad.

Chad Callihan gives us a hint:

Have you ever heard of the READPAST query hint? I would say it’s kind of a sibling to NOLOCK. With NOLOCK, you’ll get data back faster but risk dirty reads. With READPAST, SQL Server will skip right past locked data altogether. This can either be helpful or, if you don’t know how it’s used, incredibly dangerous.

Read on to see what it does. I’m not sure I’ve ever used READPAST before, but I’m also using read committed snapshot isolation whenever I can, so that would limit the utility of the READPAST hint. I don’t have the visceral disgust with READPAST that I do with NOLOCK, though I suppose the reason for that is just how uncommon the former is, whereas every bad code base I run into is inundated with NOLOCK everywhere.

Erik Darling sets MAXDOP to 1:

It’s August, and that means one thing: Family Vacation. I’m taking this month off from regular blogging, and posting some of my paid beginner training content for you to enjoy, while I enjoy not blogging.

Click through for a good video and no text I can use as a helpful graf.

Reitse Eskens looks at an oddity:

I was teaching a class and during an interesting discussing an attendee told me that views with a filter took a long time to produce results, even if the result set itself was quite small. I wanted to test this out for myself to see what was happening. I’ll take you along this short journey in this blog. The outcomes have been validated against a SQL 2017, SQL 2019 and SQL 2022 instance.

Click through for an example.