4 ms·

I'm sorry but you just cannot say that "OR is bad" - it being a key part of SQL . It's most likely your use that is bad (e.g. your intermediate result exceeding

by xvinci 2y ago

I'm sorry but you just cannot say that "OR is bad" - it being a key part of SQL . It's most likely your use that is bad (e.g. your intermediate result exceeding some cache size). But "bad performance always due to indexes" gives a hint that you are somewhat new: No, bad performance in my experience was almost always due to developers either not understanding their ORM framework, or writing too expensive queries with or without index. Just adding indexes seldom solved the problem (maybe 1/5 of the time).

mrklol 2y ago
OR is indeed not bad, but you have to think about when to use it. It can easily make queries slower compared to other operators. That’s exactly why we have the terms "ugly OR" / "bad OR".
- thom 2y ago
  It’s worth having a mental model of _why_ OR can be suboptimal. Often it’s because you’re only hitting an index on half the conditional, or forcing PG into a bitmap scan, or worse turning and index lookup into a sequential scan. Not to bang on about indexes too much but a partial index on the OR condition works if you’re lazy, although splitting into two queries is often a great solution as it gives you two fast queries to combine instead of one slow one (although sometimes that implies PG’s statistics are incorrect because it might have been able to know which side of the conditional cuts out more data).
dz08dl 2y ago
It's complicated; that's why there isn't a one-size-fits-all solution. In the end, you want to have a good execution plan, and there's usually not just one and the same action to achieve that.
magicalhippo 2y ago
> Just adding indexes seldom solved the problem We write all our queries by hand. We've got decades of experience and I'd say we're pretty proficient. For us adding an index is almost always the solution, assuming the statistics are fine. Either we plain forgot, or a customer required new functionality we didn't predict so no index on the fields required. Sure sometimes a poorly constructed query slips out or the optimizer needs some help by reorganizing the query, but it's rare.

by xvinci 2y ago

mrklol 2y ago

OR is indeed not bad, but you have to think about when to use it. It can easily make queries slower compared to other operators. That’s exactly why we have the terms "ugly OR" / "bad OR".

thom 2y ago
It’s worth having a mental model of _why_ OR can be suboptimal. Often it’s because you’re only hitting an index on half the conditional, or forcing PG into a bitmap scan, or worse turning and index lookup into a sequential scan. Not to bang on about indexes too much but a partial index on the OR condition works if you’re lazy, although splitting into two queries is often a great solution as it gives you two fast queries to combine instead of one slow one (although sometimes that implies PG’s statistics are incorrect because it might have been able to know which side of the conditional cuts out more data).

dz08dl 2y ago

It's complicated; that's why there isn't a one-size-fits-all solution. In the end, you want to have a good execution plan, and there's usually not just one and the same action to achieve that.

magicalhippo 2y ago

> Just adding indexes seldom solved the problem We write all our queries by hand. We've got decades of experience and I'd say we're pretty proficient. For us adding an index is almost always the solution, assuming the statistics are fine. Either we plain forgot, or a customer required new functionality we didn't predict so no index on the fields required. Sure sometimes a poorly constructed query slips out or the optimizer needs some help by reorganizing the query, but it's rare.