Re: Effective query for listing flags in use by messages in a folder - Mailing list pgsql-sql
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
From: pgsql-sql-owner@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Joseph Krogh <andreas@visena.com>
Sent: 17 March 2015 21:02
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
Sent: 17 March 2015 21:02
To: pgsql-sql@postgresql.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.vass@metametrics.co.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 bestat 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 fastselect '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