Alban Hertroys <alban@magproductions.nl> writes:
> My conclusion is that this query time is mostly limited to the somewhat
> complex COUNT expressions. Is there any way to do this more efficiently?
Offhand I would bet on the bitstring-AND operations being the
bottleneck; you could test this by comparing the speed of queries that
are doing different mixes of the same number of COUNT()s. If you're
happy with a fixed-width 32-bit field, consider using an integer field
and integer & operations, instead of bitstring. Bitstring is a
pass-by-reference type and so inherently a lot less efficient than an
integer.
Another suggestion is to replace
count(nullif(boolean_expr, false))
with
sum((boolean_expr)::int)
I think this would be a marginal speed win at best (basically replacing
a Const and a NullIf node with a Cast node), but it just seems to me
to be more natural ... it took me a bit to figure out what your query
was trying to accomplish.
regards, tom lane