Hacker News Reader

Comment by jasode | Hacker News Reader

> WHERE id = $1

>It's worth noting that this approach has significant dangers for execution performance

This extra WHERE id=$1 clause makes it behave different from the slow examples you cited from the Markus Winand blog. The query planner should notice that id column is a selective index with high cardinality (and may even be the unique ids primary key). The query optimizer can filter on id to return a single row before the dynamic NULL checks of $2,$3,$4 to avoid a full-table scan.

The crucial difference is the id clause doesn't have an extra "OR id IS NULL" or "$1 IS NULL" -- like the blog examples.

View on HN

Note that null is being used here in the “unknown value” sense in order to combine several possible queries into one query plan. Which is a bad idea for a query you need to be performant: $1 can be null (which is possible in the original stackoverflow question that this blog post is a reaction to), and if the parameters are passed in via a bound argument in a prepared statement (not uncommon), then the query plan won't necessarily be taking the passed parameters into account when deciding on the plan.

You are right. If that’s the query you need to write, you’ll be ok.

That said, I don’t think I’ve ever had occasion to write a query quite like that. I’ve written

  select * from blah where id in (1,2,3…) and condition

  select * from blah where condition1 and condition2

but never a query quite like this. Do you know of use cases for it?

Given that most queries don't look like that, I think my criticism is reasonable. For most use cases, this query will have performance downsides, even if it doesn't for some very narrow use-cases.

That sounds like the developer use case. Data scientists doing ETL and analyzing messy data with weird rules like the ones above are common (although the id is usually a contains/in to handle lists of rows that don’t fit any of the conditions but must be included).

I’ve had to do some weird things to clean up data from vendor databases in several industries.

Record selector to drive a data grid. Ex: Filter employees by location, or active/terminated, or salary/hourly, etc. and let the user choose one or many of these filters.

The use case is basically every /search endpoint or any table view where the user can select filters.

Reply on news.ycombinator.com