Hacker News Reader

Comment by allan_s | Hacker News Reader

Re: performance issue

I used to think that performance issue in relational database was always a matter of :

* missing indexes * non-used indexes due to query order (where A, B instead of B, A)

But we had the case recently where we optimized a query in postgresql which was taking 100% of cpu during 1s (enough to trigger our alerting) by simply splitting a OR in two separate query.

So if you are looking for optimisation it may be good to know about "OR is bad". The two queries run in some ms both.

View on HN

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

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

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.

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

We are transitioning from SQLAnywhere to MSSQL, and saw the same for a key query.

SQLAnywhere handled the single OR fine, but we had to split the query into two using UNION ALL for MSSQL not to be slow as a snail burning tons of CPU.

No idea why the MSSQL optimizer doesn't do that itself, it's essentially what SQLAnywhere does.

Reply on news.ycombinator.com