Re: Operator performance question

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