12 ms·

If you have some PostgreSQL performance issues, I'd recommend checking out PGAnalyze - they've offered a much more advanced index advisor for some time now. My

If you have some PostgreSQL performance issues, I'd recommend checking out PGAnalyze - they've offered a much more advanced index advisor for some time now. My company is a paid customer since around 2020 and we are very satisfied, easily beats the Datadog's (which we use for the rest of our infra and apps) observability offering for PostgreSQL.

allan_s 2y ago
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.
- 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.
- magicalhippo 2y ago
  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.
deleted 2y ago
[deleted]

allan_s 2y ago

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.

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.
magicalhippo 2y ago
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.

deleted 2y ago

[deleted]