Hacker News Reader

Comment by xvinci | Hacker News Reader

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

View on HN

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

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.

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

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

Reply on news.ycombinator.com