SUM(CASE WHEN rating = 'R' THEN length END), RAISE INFO 'Run %, Statement 1: %', r, (clock_timestamp() - v_ts) Repeat the whole benchmark several times to avoid warmup penalty
#COUNT FILTER POSTGRESQL CODE#
Benchmark codeĪs promised, this was the benchmark code used for this blog post: Do measure things yourself, and if you cannot reproduce a performance problem, then don’t necessarily touch perfectly sound logic just because a blog post told you so. While in this case, the improvement is worth it irrespective of actual measurements (because performance can hardly be worse, and readability actually improves), always be careful with such benchmark results. You can safely switch to this nice standard SQL syntax already now. ) is such a popular idiom, and 8% is quite the significant improvement, that I think PostgreSQL should fix this. I think this is a case where it’s worth looking into simple patterns of expressions in aggregate functions. In RDBMS that don’t have such a cache, optimisation time is more costly per query, so less can be expected. Db2, Oracle, SQL Server), in case of which the optimisation needs to be done only once per cached plan, and then the plan can be reused. There was a tendency of such optimisations being favoured by RDBMS that have an execution plan cache (e.g. In a previous blog post (which is probably outdated by now), I’ve shown a lot of these cases, where the optimisation decision doesn’t depend on any cost model and data sets and should always be done, ideally. But this isn’t always the case in the real world, where optimisers make tradeoffs between: In a perfect world, two provably equivalent SQL syntaxes also perform the same way. But still, the FILTER clause outperforms CASE clause usage. The optimiser doesn’t optimise this as well as it could).
So, indeed, the redundant predicate improved things (in a perfect world, it shouldn’t, but here we are.
0 kommentar(er)
