Message-ID: <3FBCDFE5.9F3AEF69@rodos.fzk.de>
Date: Thu, 20 Nov 2003 16:38:14 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org, wintrojr@tripos.com
Subject: Re: Need another way to do this, my sql much to slow...
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

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