Re: Effective query for listing flags in use by messages in a folder - Mailing list pgsql-sql

From Andreas Joseph Krogh
Subject Re: Effective query for listing flags in use by messages in a folder
Date
Msg-id VisenaEmail.3e.a23660ba6b81553b.14c2d1978d6@tc7-visena
Whole thread Raw
In response to Re: Effective query for listing flags in use by messages in a folder  (Hector Vass <hector.vass@metametrics.co.uk>)
List pgsql-sql
På onsdag 18. mars 2015 kl. 14:19:18, skrev Hector Vass <hector.vass@metametrics.co.uk>:

My recommendation is to hold the data as key value pairs not as a table with columns for each flag.. 

 

Taking your table message ... and loading into this key value pair table messaage2..

 

drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message2;
create table message2(
    folder_id integer not NULL,
    msg varchar(200),
    is_flag myflags
);
insert into message2 select folder_id,msg,'is_seen' from message where is_seen is TRUE;
insert into message2 select folder_id,msg,'is_replied' from message where is_replied is TRUE;
insert into message2 select folder_id,msg,'is_forwarded' from message where is_forwarded is TRUE;
insert into message2 select folder_id,msg,'is_deleted' from message where is_deleted is TRUE;
insert into message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message where is_flagged is TRUE;
 
select is_flag from message2 where folder_id=1 group by 1;
work=# select is_flag from message2 where folder_id=1 group by 1;
  is_flag
------------
 is_seen
 is_deleted
 is_replied
(3 rows)
 
New messages are inserted into the message-table "all the time" and it seems quite expensive to keep this key-value table updated (which it must be, using triggers).
 
My version returns in sub-millisecond for a folder with > 100K messages in it, which I think is not bad, I just don't like the looks of the query and all the indexes required.
 
Thanks for looking into this.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Attachment

pgsql-sql by date:

Previous
From: Hector Vass
Date:
Subject: Re: Effective query for listing flags in use by messages in a folder
Next
From: Hector Vass
Date:
Subject: Re: Effective query for listing flags in use by messages in a folder