Re: question about count(b) where b is a custom type

On Wed, Nov 16, 2005 at 08:28:28AM -0500, Pollard, Mike wrote:
> Details, details. But there is a valid general question here, and
> changing the semantics of the query will not address it. When doing a
> count(col), why convert col into a string just so you can determine if
> it is null or not? This isn't a problem on a small amount of data, but
> it seems like a waste, especially if you are counting millions of
> records. Is there some way to convert this to have the caller convert
> nulls to zero and non-nulls to 1, and then just pass an int? So
> logically the backend does:
>
> Select count(case <col> when null then 0 else 1) from <table>
>
> And count just adds the number to the running tally.

Actually, something is wrong with this whole thread. count(x) is
defined to take any type, hence count(b) won't convert anything to text
or anything else.

Which seems to imply that in the original query it's the '~' operator
that has the text conversion. Can you post an EXPLAIN VERBOSE for that
query so we can see where the conversion is being called.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.