Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXrcT-0004yA-S2 for pgsql-sql@arkaria.postgresql.org; Tue, 17 Mar 2015 13:33:18 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXrcT-0002uX-4O for pgsql-sql@arkaria.postgresql.org; Tue, 17 Mar 2015 13:33:17 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1YXrcR-0002uN-KX for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 13:33:15 +0000 Received: from mail-am1on0702.outbound.protection.outlook.com ([2a01:111:f400:fe00::702] helo=emea01-am1-obe.outbound.protection.outlook.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXrcI-0007XY-6I for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 13:33:13 +0000 Received: from DB3PR05MB426.eurprd05.prod.outlook.com (10.141.7.13) by DB3PR05MB425.eurprd05.prod.outlook.com (10.141.7.12) with Microsoft SMTP Server (TLS) id 15.1.106.15; Tue, 17 Mar 2015 13:31:28 +0000 Received: from DB3PR05MB426.eurprd05.prod.outlook.com ([10.141.7.13]) by DB3PR05MB426.eurprd05.prod.outlook.com ([10.141.7.13]) with mapi id 15.01.0106.007; Tue, 17 Mar 2015 13:31:28 +0000 From: Hector Vass To: Andreas Joseph Krogh , "pgsql-sql@postgresql.org" Subject: Re: Effective query for listing flags in use by messages in a folder Thread-Topic: [SQL] Effective query for listing flags in use by messages in a folder Thread-Index: AQHQYIZgPcF8AjH69E2ZKdgW2he4CZ0glMZw Date: Tue, 17 Mar 2015 13:31:28 +0000 Message-ID: <1426599087812.95161@metametrics.co.uk> References: In-Reply-To: Accept-Language: en-GB, en-US Content-Language: en-GB X-MS-Has-Attach: yes X-MS-TNEF-Correlator: x-originating-ip: [81.149.172.36] authentication-results: visena.com; dkim=none (message not signed) header.d=none; x-microsoft-antispam: UriScan:;BCL:0;PCL:0;RULEID:;SRVR:DB3PR05MB425; x-forefront-antispam-report: BMV:1; SFV:NSPM; SFS:(10019020)(53754006)(2501003)(40100003)(76176999)(122556002)(19617315012)(19580405001)(19580395003)(18206015028)(19627595001)(46102003)(36756003)(74482002)(16236675004)(19625215002)(2656002)(107886001)(87936001)(19627405001)(99936001)(17760045003)(106116001)(15975445007)(102836002)(2900100001)(92566002)(2950100001)(2420400003)(117636001)(77156002)(66066001)(54356999)(77096005)(50986999)(62966003); DIR:OUT; SFP:1102; SCL:1; SRVR:DB3PR05MB425; H:DB3PR05MB426.eurprd05.prod.outlook.com; FPR:; SPF:None; MLV:sfv; LANG:en; x-microsoft-antispam-prvs: x-exchange-antispam-report-test: UriScan:; x-exchange-antispam-report-cfa-test: BCL:0; PCL:0; RULEID:(601004)(5002010)(5005006); SRVR:DB3PR05MB425; BCL:0; PCL:0; RULEID:; SRVR:DB3PR05MB425; x-forefront-prvs: 0518EEFB48 Content-Type: multipart/related; boundary="_004_142659908781295161metametricscouk_"; type="multipart/alternative" MIME-Version: 1.0 X-OriginatorOrg: metametrics.co.uk X-MS-Exchange-CrossTenant-originalarrivaltime: 17 Mar 2015 13:31:28.2023 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 0245920b-b0ee-44df-a137-423185636e7a X-MS-Exchange-Transport-CrossTenantHeadersStamped: DB3PR05MB425 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --_004_142659908781295161metametricscouk_ Content-Type: multipart/alternative; boundary="_000_142659908781295161metametricscouk_" --_000_142659908781295161metametricscouk_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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/ma= intaining this data /** you could consider holding the flags as integers as you can do more stu= ff 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_dele= ted+is_draft+is_flagged>0; /** of course holding flags as columns can be inefficient and not as flexib= le 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 repea= ted ... **/ /** 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=3D0; 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 i= s_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 f= ast **/ Hector Vass +44(0)7773 352 559 * Metametrics, International House, 107 Gloucester Road, Malmesbury, Wilt= shire, SN16 0AJ * www.metametrics.co.uk ________________________________ From: pgsql-sql-owner@postgresql.org on be= half of Andreas Joseph Krogh Sent: 17 March 2015 07:45 To: pgsql-sql@postgresql.org Subject: [SQL] Effective query for listing flags in use by messages in a fo= lder 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 on= e or more messages in that folder has the value=3Dtrue. 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 ef= ficient as possible (no JOIN'ing), the system contains millions of messages= . create index message_folder_id_deleted_idx ON message(folder_id) where is_d= eleted =3D TRUE; create index message_folder_id_forwarded_idx ON message(folder_id) where is= _forwarded =3D TRUE; create index message_folder_id_replied_idx ON message(folder_id) where is_r= eplied =3D TRUE; create index message_folder_id_seen_idx ON message(folder_id) where is_seen= =3D TRUE; create index message_folder_id_flagged_idx ON message(folder_id) where is_f= lagged =3D TRUE; create index message_folder_id_draft_idx ON message(folder_id) where is_dra= ft =3D TRUE; select 'is_deleted' from (select * from message where folder_id =3D 3 AND i= s_deleted limit 1) as q UNION select 'is_forwarded' from (select * from message where folder_id =3D 3 AND= is_forwarded limit 1) as q UNION select 'isreplied' from (select * from message where folder_id =3D 3 AND is= _replied limit 1) as q UNION select 'is_seen' from (select * from message where folder_id =3D 3 AND is_s= een limit 1) as q UNION select 'is_flagged' from (select * from message where folder_id =3D 3 AND i= s_flagged limit 1) as q UNION select 'is_draft' from (select * from message where folder_id =3D 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 www.visena.com [cid:part_F12291144747920LR13D] --_000_142659908781295161metametricscouk_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

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 mor= e 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_forw= arded+is_deleted+is_draft+is_flagged>0;

/** of course holding flags as columns can be inefficient and not as f= lexible 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 wi= th a flag**/
/** key value approach has advantages as you simply add flags so no up= date or delete operations **/
delete from message where is_val=3D0;
select folder_id from message group by 1;

/** Commonly the bottleneck comes down to the speed at which you can s= tore/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 preferre= d 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&g= t;0;
select 'deleted flag set or seen draft set ',folder_id from message wh= ere is_flag&6>0;

/** if you can compress the data into bit field you may not need to bo= ther with overhead of indexes as scanning whole table or partition can be v= ery fast **/




Hector Vass




+44(0)7773 352 559

*  Metametrics, International House, 107 Glouc= ester Road,  Malmesbury, Wiltshire, SN16 0AJ

8   www.metam= etrics.co.uk

 

From: pgsql-sql-owner@postg= resql.org <pgsql-sql-owner@postgresql.org> on behalf of Andreas Josep= h 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 consider= ed used when one or more messages in that folder has the value=3Dtrue.
 
The schema is like this:
cr=
eate table message=
(=0A=
    folder_id integer not NULL,=0A=
    is_seen =
boolean NOT NULL defau=
lt false,=0A=
    is_replied boolean not null de=
fault false,=0A=
    is_forwarded boolean not null =
default false,=0A=
    is_deleted boolean not null de=
fault false,=0A=
    is_draft boolean not null defa=
ult false,=0A=
    is_flagged boolean not null de=
fault false=0A=
);=0A=
 
I need the "flags" to be in the message-table for other quer= ies to be as efficient as possible (no JOIN'ing), the system contains milli= ons of messages.
 
create index message_folder_id=
_deleted_idx ON messag=
e(folder_id<=
/span>) where <=
span style=3D"color:rgb(102,14,122); font-weight:bold">is_deleted =
=3D TRUE;=0A=
create index me=
ssage_folder_id_forwarded_idx ON message(folder_id) where is_=
forwarded =3D T=
RUE;=0A=
create index me=
ssage_folder_id_replied_idx ON message(folder_id) where is_re=
plied =3D TRUE<=
/span>;=0A=
create index me=
ssage_folder_id_seen_idx ON message(wh=
ere is_seen =
=3D TRUE=
;=0A=
create index me=
ssage_folder_id_flagged_idx ON message(folder_id) where is_fl=
agged =3D TRUE<=
/span>;=0A=
create index me=
ssage_folder_id_draft_idx ON message(folder_id) w=
here is_draf=
t =3D TRUE;=0A=
=0A=
select 'is_deleted' from (select * from message where folder_id =3D 3 AND is_de=
leted limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_forwarded' <=
span style=3D"color:rgb(0,0,128); font-weight:bold">from (select * from message where folder_id =3D 3 AND is_forwarded limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'isreplied' from (select * from message where folder_id =3D 3 AND is_replied limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_seen' from (select * from message where folder_id =3D 3 AND i=
s_seen limit 1) as q=0A=
UNION=0A=
select <=
span style=3D"color:rgb(0,128,0); font-weight:bold">'is_flagged' from (select * from message where folder_id =3D 3 AND is_flagged limit 1) as q=0A=
UNION=0A=
select 'is_draft' from (select =
* from message where folder_id =3D 3 AND is_draft 1) as =
q=0A=
;=0A=
 
Are there better ways to do this?
 
Thanks.
 
--
Andreas= Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47= 909 56 963
=3D""
--_000_142659908781295161metametricscouk_-- --_004_142659908781295161metametricscouk_ Content-Type: image/png; name="ATT00001.png" Content-Description: ATT00001.png Content-Disposition: inline; filename="ATT00001.png"; size=1913; creation-date="Tue, 17 Mar 2015 07:45:45 GMT"; modification-date="Tue, 17 Mar 2015 07:45:45 GMT" Content-ID: Content-Transfer-Encoding: base64 iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --_004_142659908781295161metametricscouk_--