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

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 **/

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com