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

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Hector Vass <hector.vass@metametrics.co.uk>
To: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Effective query for listing flags in use by messages in a folder
Date: Tue, 17 Mar 2015 13:31:28 +0000
Message-ID: <1426599087812.95161@metametrics.co.uk> (raw)
In-Reply-To: <VisenaEmail.12.442a4269e3e66e11.14c26b11f46@tc7-visena>
References: <VisenaEmail.12.442a4269e3e66e11.14c26b11f46@tc7-visena>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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 **/




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@postgresql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Joseph Krogh <andreas@visena.com>
Sent: 17 March 2015 07:45
To: pgsql-sql@postgresql.org
Subject: [SQL] Effective query for listing flags in use by messages in a folder

Hi all.

On PG-9.3 (no JSONB),

For an IMAP-like system; I'm trying to figure out an effective way to query for "what flags are in use in a folder". A flag is considered used when one or more messages in that folder has the value=true.

The schema is like this:

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
);



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
;



Are there better ways to do this?

Thanks.

--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com<mailto:andreas@visena.com>
www.visena.com<https://www.visena.com;
[cid:part_F12291144747920LR13D]<https://www.visena.com;

Attachments:

  [image/png] ATT00001.png (1.9K, ../1426599087812.95161@metametrics.co.uk/3-ATT00001.png)
  download | view image

view thread (12+ messages)  latest in thread

Message-ID: <1426599087812.95161@metametrics.co.uk>
Permalink:  ../1426599087812.95161@metametrics.co.uk/
Also on:    postgresql.org/message-id/1426599087812.95161@metametrics.co.uk

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: hector.vass@metametrics.co.uk, andreas@visena.com
  Subject: Re: Effective query for listing flags in use by messages in a folder
  In-Reply-To: <1426599087812.95161@metametrics.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox