Tom Lane wrote: > Alban Hertroys <alban@xxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxxxxx 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 //
