> > 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]