Re: Operator performance question

Tom Lane wrote:
> Alban Hertroys <alban(at)magproductions(dot)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.

Hmm... I picked bitstrings because a quick test seemed to show it
performing better than ints. Apparently my test wasn't right.
Definitely a thing to test again.

So far I have some bits of the 32 to spare, but the set of options to
filter on isn't fixed yet - I expect it to grow, and I don't know by how
much yet. I might (although I doubt it) get beyond 64 bits, in which
case even a bigint wouldn't suffice...

> Another suggestion is to replace
>
> count(nullif(boolean_expr, false))
>
> with
>
> sum((boolean_expr)::int)

I hadn't realized true::int = 1 and false::int = 0. Thanks for the
suggestions.

> 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.

It took me a bit to come up with that solution; I agree it's not a very
obvious one. I basically hacked my expression to evaluate to NULL if
false, so that count wouldn't pick it up.

NULLIF did almost what I wanted. I would've been happier with a function
NULLIF(boolean_expr) - with just one boolean parameter. It may be even
better to have a COUNTIF(boolean_expr) aggregate that would only count
values where the expression evaluates true - Come to think of it, that
looks awfully familiar... Oracle maybe?

Anyway, I now have something to go on again. Thanks.

--
Alban Hertroys
alban(at)magproductions(dot)nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //