11 ms·

The recommended approach is to generate SQL that looks like: SELECT \* FROM users WHERE id = $1 AND ($2 IS NULL OR username = $2) AND (

The recommended approach is to generate SQL that looks like: SELECT \* FROM users WHERE id = $1 AND ($2 IS NULL OR username = $2) AND ($3 IS NULL OR age > $3) AND ($4 IS NULL OR age < $4) It's worth noting that this approach has significant dangers for execution performance--it creates a significant chance that you'll get a query plan that doesn't match your actual query. See: https://use-the-index-luke.com/sql/where-clause/obfuscation/smart-logic https://use-the-index-luke.com/sql/where-clause/obfuscation/... for some related material.

hinkley 2y ago
This may be a situation where you classify the parameters and create a handful of queries that include or exclude each class. The same way we always have a distinct query for selecting by ID, you have one with just username, one with demographics, one with account age or activity ranges, and then C(3,2) combinations of categories.
jasode 2y ago
> 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.
- hyperpape 2y ago
  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 or 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.
  - throwup238 2y ago
    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.
  - nycdotnet 2y ago
    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.
    - deleted 2y ago
      [deleted]
  - hombre_fatal 2y ago
    The use case is basically every /search endpoint or any table view where the user can select filters.
- cwillu 2y ago
  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.
nycdotnet 2y ago
Agree. With patterns like this you are leaning on your db server’s CPU - among your most scarce resources - versus doing this work on the client on a relatively cheap app server. At query time your app server knows if $2 or $3 or $4 is null and can elide those query args. Feels bad to use a fast language like Rust on your app servers and then your perf still sucks because your single DB server is asked to contemplate all possibilities on queries like this instead of doing such simple work on your plentiful and cheap app servers.
- deleted 2y ago
  [deleted]
- pocketarc 2y ago
  This is actually an incredible way of articulating something that's been on my mind for quite a while. Thank you for this, I will use this. The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.
  - dinosaurdynasty 2y ago
    There's a different reason to lean on the DB: it's the final arbiter of your data. Much harder to create bad/poisoned data if the DB has a constraint on it (primary, foreign, check, etc) than if you have to remember it in your application (and unless you know what serializable transactions are, you are likely doing it wrong). Also you can't do indexes outside of the DB (well, you can try).
    - bruce511 2y ago
      Replying to this whole sub-thread, not just this post specifically; All SQL advice has to take _context_ into account. In SQL, perhaps more than anywhere else, context matters. There's lots of excellent SQL advice, but most of it is bound to a specific context, and in a different context it's bad advice. Take for example the parent comment above; In their context the CPU of the database server is their constraining resource. I'm guessing the database if "close" to the app servers (ie low network latency, high bandwidth), and I'm also guessing the app developers "own" the database. In this context moving CPU to the app server makes complete sense. Client-side validation of data makes sense because they are the only client. Of course if the context changes, then the advice has to change as well. If the network bandwidth to the server was constrained (cost, distance etc) then transporting the smallest amount of data becomes important. In this case it doesn't matter if the filter is more work for the server, the goal is the smallest result set. And so it goes. Write-heavy systems prefer fewer indexes. Read-heavy systems prefer lots of indexes. Databases where the data client is untrusted need more validation, relation integrity, access control - databases with a trusted client need less of that. In my career I've followed a lot of good SQL advice - advice that was good for my context. I've also broken a lot of SQL "rules" because those rules were not compatible, or were harmful, in my context. So my advice is this - understand your own context. Understand where you are constrained, and where you have plenty. And tailor your patterns around those parameters.
BeefWellington 2y ago
If your column named simply `id` isn't a unique index you've gone very wrong. The rest of the query plan probably won't need much power.
- hyperpape 2y ago
  https://news.ycombinator.com/item?id=42825171 https://news.ycombinator.com/item?id=42825171
- hobs 2y ago
  It really depends on your query engine, this would be considered a "catch all query" in SQL Server, and you're going to have really bad parameter sniffing blowing you up, you do not want to do this usually.
  - BeefWellington 2y ago
    I would expect the query plan for SQL server to essentially return records matching `id` first (which again should be a situation where uniqueness comes into play) and then performing the rest of the execution on the subset that matches, which is hopefully one. I leave allowances for `id` to be a stand-in for some other identity column that may represent a foreign key to another table. In which case I'd still expect SQL server's query planner to execute as: initial set is those where said column matches the supplied number, then further applies the logic to that subset. In fact I'd love to see where that isn't the case against a transactional DB.
    - hobs 2y ago
      If you include a clustered index/primary key on every query, you might be right, but in practice there's no benefit to include any other params if you have the pk, the catch all query is easy to modify into something that supports batches AND single values (which is where it really gets bad for parameter sniffing) and that's what I usually see in production causing problems.
crazygringo 2y ago
AFAIK, that's only a risk if you're using prepared statements. If you're just running each query individually, the parser should be smart and ignore the Boolean clauses that can be skipped, and use the appropriate indexes each time. But yes, if you're trying to optimize performance with prepared statements, you almost certainly do not want to follow this approach if any of columns are indexed (which of course you will often want them to be).
rw-access 2y ago
I have hundreds of queries like this in production (Go + Postgres + pgx), and don't have issues leveraging the right indexes. Make sure when using prepared statements, you have custom query plans per query for this via `SET plan_cache_mode = force_custom_plan`. These optimizations are trivial for Postgres to make optimization/plan time, so there's no runtime hit. But as always, profile and double check. You're definitely right that assuming can get you in trouble. I don't have experience with the other databases to speak to their quirks. But for my specific setup, I haven't had issues.

hinkley 2y ago

This may be a situation where you classify the parameters and create a handful of queries that include or exclude each class. The same way we always have a distinct query for selecting by ID, you have one with just username, one with demographics, one with account age or activity ranges, and then C(3,2) combinations of categories.

jasode 2y ago

> 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.

hyperpape 2y ago
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 or 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.
- throwup238 2y ago
  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.
- nycdotnet 2y ago
  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.
  - deleted 2y ago
    [deleted]
- hombre_fatal 2y ago
  The use case is basically every /search endpoint or any table view where the user can select filters.
cwillu 2y ago
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.

nycdotnet 2y ago

Agree. With patterns like this you are leaning on your db server’s CPU - among your most scarce resources - versus doing this work on the client on a relatively cheap app server. At query time your app server knows if $2 or $3 or $4 is null and can elide those query args. Feels bad to use a fast language like Rust on your app servers and then your perf still sucks because your single DB server is asked to contemplate all possibilities on queries like this instead of doing such simple work on your plentiful and cheap app servers.

deleted 2y ago
[deleted]
pocketarc 2y ago
This is actually an incredible way of articulating something that's been on my mind for quite a while. Thank you for this, I will use this. The received wisdom is, of course, to lean on the DB as much as possible, put all the business logic in SQL because of course the DB is much more efficient. I myself have always been a big proponent of it. But, as you rightly point out, you're using up one of your infrastructure's most scarce and hard-to-scale resources - the DB's CPU.
- dinosaurdynasty 2y ago
  There's a different reason to lean on the DB: it's the final arbiter of your data. Much harder to create bad/poisoned data if the DB has a constraint on it (primary, foreign, check, etc) than if you have to remember it in your application (and unless you know what serializable transactions are, you are likely doing it wrong). Also you can't do indexes outside of the DB (well, you can try).
  - bruce511 2y ago
    Replying to this whole sub-thread, not just this post specifically; All SQL advice has to take _context_ into account. In SQL, perhaps more than anywhere else, context matters. There's lots of excellent SQL advice, but most of it is bound to a specific context, and in a different context it's bad advice. Take for example the parent comment above; In their context the CPU of the database server is their constraining resource. I'm guessing the database if "close" to the app servers (ie low network latency, high bandwidth), and I'm also guessing the app developers "own" the database. In this context moving CPU to the app server makes complete sense. Client-side validation of data makes sense because they are the only client. Of course if the context changes, then the advice has to change as well. If the network bandwidth to the server was constrained (cost, distance etc) then transporting the smallest amount of data becomes important. In this case it doesn't matter if the filter is more work for the server, the goal is the smallest result set. And so it goes. Write-heavy systems prefer fewer indexes. Read-heavy systems prefer lots of indexes. Databases where the data client is untrusted need more validation, relation integrity, access control - databases with a trusted client need less of that. In my career I've followed a lot of good SQL advice - advice that was good for my context. I've also broken a lot of SQL "rules" because those rules were not compatible, or were harmful, in my context. So my advice is this - understand your own context. Understand where you are constrained, and where you have plenty. And tailor your patterns around those parameters.

BeefWellington 2y ago

If your column named simply `id` isn't a unique index you've gone very wrong. The rest of the query plan probably won't need much power.

hyperpape 2y ago
https://news.ycombinator.com/item?id=42825171 https://news.ycombinator.com/item?id=42825171
hobs 2y ago
It really depends on your query engine, this would be considered a "catch all query" in SQL Server, and you're going to have really bad parameter sniffing blowing you up, you do not want to do this usually.
- BeefWellington 2y ago
  I would expect the query plan for SQL server to essentially return records matching `id` first (which again should be a situation where uniqueness comes into play) and then performing the rest of the execution on the subset that matches, which is hopefully one. I leave allowances for `id` to be a stand-in for some other identity column that may represent a foreign key to another table. In which case I'd still expect SQL server's query planner to execute as: initial set is those where said column matches the supplied number, then further applies the logic to that subset. In fact I'd love to see where that isn't the case against a transactional DB.
  - hobs 2y ago
    If you include a clustered index/primary key on every query, you might be right, but in practice there's no benefit to include any other params if you have the pk, the catch all query is easy to modify into something that supports batches AND single values (which is where it really gets bad for parameter sniffing) and that's what I usually see in production causing problems.

crazygringo 2y ago

AFAIK, that's only a risk if you're using prepared statements. If you're just running each query individually, the parser should be smart and ignore the Boolean clauses that can be skipped, and use the appropriate indexes each time. But yes, if you're trying to optimize performance with prepared statements, you almost certainly do not want to follow this approach if any of columns are indexed (which of course you will often want them to be).

rw-access 2y ago

I have hundreds of queries like this in production (Go + Postgres + pgx), and don't have issues leveraging the right indexes. Make sure when using prepared statements, you have custom query plans per query for this via `SET plan_cache_mode = force_custom_plan`. These optimizations are trivial for Postgres to make optimization/plan time, so there's no runtime hit. But as always, profile and double check. You're definitely right that assuming can get you in trouble. I don't have experience with the other databases to speak to their quirks. But for my specific setup, I haven't had issues.