[freekh]

It is not count() that is slow, it is iterating through the rows that is / can be slow :)

For me, the trick to basic understanding of perf in PG was exactly this: it is all about limiting the amount of rows you have to iterate over. It is true for count() but also for every other operation you do.

PG is surprisingly non-magical (at least in my experience) in that you won't get much perf for free, but on the other hand you can reason about perf & optimize pretty reliably once you come to terms with this.