News for hackers. Stuff that matters.
Re:The SQL filter clause: selective aggregates by MichaelBurge on Monday May 30, 2016 @09:31AM (#11801216)
If you're talking specifically about the comment at the end, that was because the example query will do a seq scan on the table twice. The filter equivalent will only do one scan.

I find subselects are more predictable, but I wouldn't say they're generally more performant. The explain plan's tree maps more closely to the subquery dependency tree with selects, so someone tweaking their query and staring at the explain plan to get it how they want will have an easier time with the subselect strategy.

Be careful of CTEs if you try that, though. A long chain of CTEs with the transformation I gave is almost guaranteed to give a bad plan(unless you want seq scans all the way down).

There are no comments on this comment yet.