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

Andreas ... your code and one of my examples ... I have modified my option 2 to give an example with data that gives you I believe exactly the same output (one row for each flag set for folder_id=3 with the text representation of the flag) ... when you satisfy yourself this produces the same results you might then want to go back and re-read my original post which rather than feeding you verbatim how to produce exactly the same results gave the the pro's and con's of 3x different approaches... I chose to illustrate my option 2 because it is easy to understand and is a reasonable production solution, option 1 was really just to get you thinking differently about how to do this and option 3 I concede was more advanced and probably but requires skills other than plain SQL to implement.

Your code (I have added data so that this is a full working code snippet)

drop table if exists message;
create table message(
folder_id integer not NULL,
is_seen boolean NOT NULL default false,
is_replied boolean not null default false,
is_forwarded boolean not null default false,
is_deleted boolean not null default false,
is_draft boolean not null default false,
is_flagged boolean not null default false
);
insert into message values
(1,'f','f','f','f','f','f'),
(2,'f','f','f','f','f','t'),
(3,'f','f','f','f','t','t'),
(4,'f','f','f','t','f','t')
;

--I need the "flags" to be in the message-table for other queries to be as efficient as possible (no JOIN'ing), the system contains millions of messages.

create index message_folder_id_deleted_idx ON message(folder_id) where is_deleted = TRUE;
create index message_folder_id_forwarded_idx ON message(folder_id) where is_forwarded = TRUE;
create index message_folder_id_replied_idx ON message(folder_id) where is_replied = TRUE;
create index message_folder_id_seen_idx ON message(folder_id) where is_seen = TRUE;
create index message_folder_id_flagged_idx ON message(folder_id) where is_flagged = TRUE;
create index message_folder_id_draft_idx ON message(folder_id) where is_draft = TRUE;

select 'is_deleted' from (select * from message where folder_id = 3 AND is_deleted limit 1) as q
UNION
select 'is_forwarded' from (select * from message where folder_id = 3 AND is_forwarded limit 1) as q
UNION
select 'isreplied' from (select * from message where folder_id = 3 AND is_replied limit 1) as q
UNION
select 'is_seen' from (select * from message where folder_id = 3 AND is_seen limit 1) as q
UNION
select 'is_flagged' from (select * from message where folder_id = 3 AND is_flagged limit 1) as q
UNION
select 'is_draft' from (select * from message where folder_id = 3 AND is_draft limit 1) as q
;

​

My No 2

drop type if exists myflags;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
is_flag myflags
);
insert into message values
(2,'is_flagged'),
(3,'is_draft'),(3,'is_flagged'),
(4,'is_deleted'),(4,'is_flagged')
;
select is_flag from message where folder_id=3;

Hector Vass

+44(0)7773 352 559
• Metametrics, International House, 107 Gloucester Road, Malmesbury, Wiltshire, SN16 0AJ
• www.metametrics.co.uk<http://www.metametrics.co.uk/>

________________________________
From: pgsql-sql-owner(at)postgresql(dot)org <pgsql-sql-owner(at)postgresql(dot)org> on behalf of Andreas Joseph Krogh <andreas(at)visena(dot)com>
Sent: 17 March 2015 21:02
To: pgsql-sql(at)postgresql(dot)org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder

På tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass <hector(dot)vass(at)metametrics(dot)co(dot)uk<mailto:hector(dot)vass(at)metametrics(dot)co(dot)uk>>:

A couple of ideas to try ... the approach you take will depend on volume of records, how quickly/how much they change and the overheads of updating/maintaining this data

/** you could consider holding the flags as integers as you can do more stuff with simple math **/

drop table if exists message;
create table message(
folder_id integer not NULL,
is_seen int not null default 0,
is_replied int not null default 0,
is_forwarded int not null default 0,
is_deleted int not null default 0,
is_draft int not null default 0,
is_flagged int not null default 0
);
insert into message values
(1,0,0,0,0,0,0),
(2,0,0,0,0,0,1),
(3,0,0,0,0,1,1),
(4,0,0,0,1,0,1)
;
select folder_id from message where is_seen+is_replied+is_forwarded+is_deleted+is_draft+is_flagged>0;

/** of course holding flags as columns can be inefficient and not as flexible as holding them as key value pairs **/
drop table if exists message;
create table message(
folder_id integer not NULL,
is_flag char(1),
is_val int
);
insert into message values
(1,'s',0),(1,'r',0),(1,'f',0),(1,'d',0),(1,'a',0),(1,'f',0),
(2,'s',0),(2,'r',0),(2,'f',0),(2,'d',0),(2,'a',0),(2,'f',1),
(3,'s',0),(3,'r',0),(3,'f',0),(3,'d',0),(3,'a',1),(3,'f',1),
(4,'s',0),(4,'r',0),(4,'f',0),(4,'d',1),(4,'a',0),(4,'f',1)
;
select folder_id from message where is_val>0 group by 1;

/** key value pairs can use a lot of space as the folder_id has to be repeated ... **/
/** so why bother holding value of Zero at all just hold those with a flag**/
/** key value approach has advantages as you simply add flags so no update or delete operations **/
delete from message where is_val=0;
select folder_id from message group by 1;

/** Commonly the bottleneck comes down to the speed at which you can store/update the flags sql not the best
at generating bit map fields but you can or you can consider using a stored procedure in C ... then this would be my preferred approach **/

drop table if exists message;
create table message(
folder_id integer not NULL,
is_flag int
);
insert into message values
(1,0),
(2,1),
(3,3),
(4,5)
;
select folder_id from message where is_flag>0;
select folder_id,(is_flag::bit(7))::char(7) from message where is_flag>0;
--or can do bit level operations which are v fast
select 'deleted flag set',folder_id from message where is_flag&4>0;
select 'deleted flag set or seen draft set ',folder_id from message where is_flag&6>0;

/** if you can compress the data into bit field you may not need to bother with overhead of indexes as scanning whole table or partition can be very fast **/

Thanks for your comments.

I don't see how any of your suggestions help with listing flags in use in a folder. My example-query lists a distinct set of flags in use in a folder.

--
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>
[cid:part_F7646460741003PFML0]<https://www.visena.com>