Here is a scenario that I've encountered a number of times. We needed to serve up some data for an API endpoint. Great! We'll write a SQL query for it. Just ne

Here is a scenario that I've encountered a number of times. We needed to serve up some data for an API endpoint. Great! We'll write a SQL query for it. Just need a couple of joins and where-clause parameters -- it will be speedy, and easy to maintain. Before long, we are asked to make our query more robust, including a couple of optional parameters. Some null-coalescing in our join expressions and where-clauses is introduced. Performance immediately suffers. (I've seen the same kind of impact when introducing unions or function calls within such expressions -- the query plan can get very messy when you try to scale in this manner.) So what do we do? Restructuring our data (maybe with a materialized view or something) is one option. A reasonable alternative is to break up the single query into a few more specialized queries, and then use application logic to inspect parameters and choose the best query to use for each request. Plenty of trade-offs to consider in both cases.