Re: Effective query for listing flags in use by messages in a folder

På onsdag 18. mars 2015 kl. 14:19:18, skrev Hector Vass <
hector(dot)vass(at)metametrics(dot)co(dot)uk <mailto:hector(dot)vass(at)metametrics(dot)co(dot)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 andreas(at)visena(dot)com
<mailto:andreas(at)visena(dot)com> www.visena.com <https://www.visena.com>
<https://www.visena.com>