From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
To: wintrojr@tripos.com
Subject: Re: Need another way to do this, my sql much to slow...
Date: Thu, 20 Nov 2003 16:38:14 +0100
Message-ID: <3FBCDFE5.9F3AEF69@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, wintrojr@tripos.com
Subject: Re: Need another way to do this, my sql much to slow...
In-Reply-To: <3FBCDFE5.9F3AEF69@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox