Erik Darling asks, what’s in a name?
When we talk about finding queries to tune, there’s an unfortunate term that gets thrown around: Expensive Queries.
Why is it unfortunate? Well, it reinforces the wrong mindset when it comes to query tuning, and leads people down the wrong path, looking at the wrong metrics.
I disagree on the “bad name” bit but agree on the substance. The term “expensive query” has a very useful connotation: this is a query which requires a significant amount of resources. Where I fully agree with Erik is that “query cost” from the optimizer does not do a great job of describing “significant amount of resources.” There is also a relevant point that expensive queries may not be the most important ones to look at. Reasons why can include:
- The query runs at a time when there’s little load on the system, so it does not impact anybody else.
- The query runs within acceptable performance boundaries for customers: it may take 10 minutes to run but it’s a batch process and the relevant business unit might only need it within an hour.
- The amount of work that the query is doing is such that further optimizations are either not possible at all or they are only possible with a significant restructure that the business is unwilling to accept.
Even so, the term “expensive query” is still very useful. So is “expensive query relative to what it could be,” although we do tend to conflate the latter with the former. But now we’re getting deep into semantics and I forgot my waders.