Richard Huxton wrote:
> Pollard, Mike wrote:
> >>Firstly, if you just want a count, what's wrong with count(1) or
> >>count(*).
> >>
> >
> >
> > Because unless the column does not allow nulls, they will not return
the
> > same value.
>
> Ah, but in the example given the column was being matched against a
> value, so nulls were already excluded.
>
> --

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.

Mike Pollard
SUPRA Server SQL Engineering and Support
Cincom Systems, Inc.
--------------------------------
Better to remain silent and be thought a fool than to speak out and
remove all doubt.
Abraham Lincoln


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Pollard, Mike schrieb:
> Richard Huxton wrote:
>
>>Pollard, Mike wrote:
>>
>>>>Firstly, if you just want a count, what's wrong with count(1) or
>>>>count(*).
>>>>
>>>
>>>
>>>Because unless the column does not allow nulls, they will not return
>
> the
>
>>>same value.
>>
>>Ah, but in the example given the column was being matched against a
>>value, so nulls were already excluded.
>>
>>--
>
>
> 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

Why convert? A null is always null no matter in which datatype.

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

Which would be totally silly :-) no matter if its 0 or 1
it counts as 1. Do you mean sum() maybe?
Even then you dont need coalesce to convert null to 0
because sum() just ignores null.


> And count just adds the number to the running tally.

Which number here?

>
> Mike Pollard
> SUPRA Server SQL Engineering and Support
strange...

> Cincom Systems, Inc.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDeze2IB7bNG8LQkwRAiRKAJ46iexMClKpjYFhP/DorhBWAtKotQCfctrL
UtFLhMFKbxq6KPFzSRItyUk=
=QxgH
-----END PGP SIGNATURE-----