agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-sql@postgresql.org
Subject: Re: Effective query for listing flags in use by messages in a folder
Date: Tue, 17 Mar 2015 22:02:48 +0100 (CET)
Message-ID: <VisenaEmail.2.f9f015012da4fc36.14c2987ca81@tc7-visena> (raw)
In-Reply-To: <1426599087812.95161@metametrics.co.uk>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
På tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto: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 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 flagsin 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@visena.com <mailto:andreas@visena.com> www.visena.com
<https://www.visena.com; <https://www.visena.com;
view thread (12+ messages) latest in thread
Message-ID: <VisenaEmail.2.f9f015012da4fc36.14c2987ca81@tc7-visena>
Permalink: ../VisenaEmail.2.f9f015012da4fc36.14c2987ca81@tc7-visena/
Also on: postgresql.org/message-id/VisenaEmail.2.f9f015012da4fc36.14c2987ca81@tc7-visena
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: andreas@visena.com
Subject: Re: Effective query for listing flags in use by messages in a folder
In-Reply-To: <VisenaEmail.2.f9f015012da4fc36.14c2987ca81@tc7-visena>
* 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