Re: Need another way to do this, my sql much to slow...

>
> I am attempting to write a spam management add-on for my corporate
> server, I have the database and the apps written but one part is WAY
to
> slow to be usefull.
>
> The following view:
>
> Column | Type | Modifiers
> -----------------------+---------+-----------
> env_sender_num | integer |
> msg_from_claims_count | bigint |
>
> is built on the follow table:
>
> Column | Type | Modifiers
> -----------------+-----------------------------+-----------
> msg_sender_num | integer |
> env_sender_num | integer |
> msg_from_domain | character varying(255) |
> env_from_domain | character varying(255) |
> recorded_date | timestamp without time zone |
> Indexes: record_of_claims_env_sender_num_idx btree (env_sender_num),
> record_of_claims_msg_sender_num_idx btree (msg_sender_num)
>
> With the following SQL:
>
>
> SELECT record_of_claims.env_sender_num, count(DISTINCT
> record_of_claims.msg_sender_num)
> AS msg_from_claims_count=20
> FROM record_of_claims=20
> GROUP BY record_of_claims.env_sender_num;
>
> A sample dataset follows:
>
[snip]
Not sure, if this can speed up things

SELECT env_sender_num, COUNT(msg_sender_num) AS msg_from_claims_count
FROM (
SELECT DISTINCT ON (msg_sender_num) msg_sender_num,env_sender_num
FROM record_of_claims ORDER BY msg_sender_num,env_sender_num DESC
) foo GROUP BY env_sender_num;

but possibly it inspires you or someone else for a better one.

Regards, Christoph