Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1YXydm-0006WH-Jc for pgsql-sql@arkaria.postgresql.org; Tue, 17 Mar 2015 21:03:07 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1YXydl-0006yD-6g for pgsql-sql@arkaria.postgresql.org; Tue, 17 Mar 2015 21:03:05 +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 1YXydk-0006y7-2L for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 21:03:04 +0000 Received: from post.visena.com ([46.226.10.50]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1YXydb-00083C-9O for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 21:03:01 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=visena.com; s=20141101.wh; h=Content-Type:MIME-Version:Subject:In-Reply-To:Message-ID:To:From:Date; bh=goKy+lbLAh5yj6k+isu3+a81074tcsYI0DEYfyja1PA=; b=m3cXmW/83tz5OUyxct0OSFqap6Aga4lRuLCInw3arT4DBwf8S4vZmGZjbcyqGkGF4weQDEFS2Pz50s1xa1rFUN3G1Nf4Q3i3ojiQEX48ujQzHLDSgIYUybN/rffZz1fYsU+mK8rcS6yK2tUwR8mtTXQt98C9zjoheilvVQqEpcU=; Received: from [10.0.1.10] (helo=tc7-visena.wh.internal.visena.com) by post.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1YXydU-0004Q7-1v for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 22:02:50 +0100 Received: from localhost ([127.0.0.1] helo=tc7-visena.wh.internal.visena.com) by tc7-visena.wh.internal.visena.com with esmtp (Exim 4.82) (envelope-from ) id 1YXydU-0000zJ-M5 for pgsql-sql@postgresql.org; Tue, 17 Mar 2015 22:02:48 +0100 Date: Tue, 17 Mar 2015 22:02:48 +0100 (CET) From: Andreas Joseph Krogh To: pgsql-sql@postgresql.org Message-ID: In-Reply-To: <1426599087812.95161@metametrics.co.uk> Subject: Re: Effective query for listing flags in use by messages in a folder MIME-Version: 1.0 X-Mailer: Visena Mail 1.9.0-SNAPSHOT X-Spam-Score: -1.0 X-Spam-Report: SpamAssasin (score=-1.0, required 5.0 ALL_TRUSTED=-1, HTML_MESSAGE=0.001) X-Pg-Spam-Score: -2.0 (--) Content-Type: multipart/related; boundary="----=_Part_11_848343039.1426626168485" 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 ------=_Part_10_970455311.1426626168485 Content-Type: multipart/related; boundary="----=_Part_11_848343039.1426626168485" ------=_Part_11_848343039.1426626168485 Content-Type: multipart/alternative; boundary="----=_Part_12_1570353735.1426626168527" ------=_Part_12_1570353735.1426626168527 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable P=C3=A5 tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass < hector.vass@metametrics.co.uk >: A co= uple=20 of ideas to try ... the approach you take will depend on volume of records,= how=20 quickly/how much they change and the overheads of updating/maintaining this= data =C2=A0 /** you could consider holding the flags as integers as you can do more stu= ff=20 with simple math **/ =C2=A0 drop table if exists message; create table mess= age( =C2=A0 =C2=A0=20 folder_id integer not NULL, =C2=A0 =C2=A0 is_seen int not null default 0, = =C2=A0 =C2=A0 is_replied=20 int not null default 0, =C2=A0 =C2=A0 is_forwarded int not null default 0, = =C2=A0 =C2=A0 is_deleted=20 int not null default 0, =C2=A0 =C2=A0 is_draft int not null default 0, =C2= =A0 =C2=A0 is_flagged int=20 not null default 0 ); =C2=A0insert into message values (1,0,0,0,0,0,0),=20 (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= =20 message where is_seen+is_replied+is_forwarded+is_deleted+is_draft+is_flagge= d>0;=20 =C2=A0 /** of course holding flags as columns can be inefficient and not as= flexible=20 as holding them as key value pairs **/ drop table if exists message; create= =20 table message( =C2=A0 =C2=A0 folder_id integer not NULL, =C2=A0 =C2=A0 is_f= lag char(1), =C2=A0 =C2=A0 is_val=20 int ); =C2=A0insert into message values=20 (1,'s',0),(1,'r',0),(1,'f',0),(1,'d',0),(1,'a',0),(1,'f',0),=20 (2,'s',0),(2,'r',0),(2,'f',0),(2,'d',0),(2,'a',0),(2,'f',1),=20 (3,'s',0),(3,'r',0),(3,'f',0),(3,'d',0),(3,'a',1),(3,'f',1),=20 (4,'s',0),(4,'r',0),(4,'f',0),(4,'d',1),(4,'a',0),(4,'f',1) ; select folder= _id=20 from message where is_val>0 group by 1; =C2=A0 /** key value pairs can use = a lot of=20 space as the folder_id has to be repeated ... **/ /** so=C2=A0why bother ho= lding=20 value of Zero at all just hold those with a flag**/ /** key value approach = has=20 advantages as you simply add flags so no update or delete operations **/ de= lete=20 from message where is_val=3D0; select folder_id from message group by 1; = =C2=A0 /**=20 Commonly the bottleneck comes down to the speed at which you can store/upda= te=20 the flags sql not the best at generating bit map fields but you can or you= =C2=A0can=20 consider=C2=A0using=C2=A0a stored procedure in C=C2=A0... then this would b= e my preferred=20 approach **/ =C2=A0 drop table if exists message; create table message( =C2= =A0 =C2=A0=20 folder_id integer not NULL, =C2=A0 =C2=A0 is_flag int ); insert into messag= e values (1,0), (2,1), (3,3), (4,5) ; select folder_id from message where is_flag>0; select= =20 folder_id,(is_flag::bit(7))::char(7) from message where is_flag>0; --or can= do=20 bit level operations which are v fast select 'deleted flag set',folder_id f= rom=20 message where is_flag&4>0; select 'deleted flag set or seen draft set=20 ',folder_id from message where is_flag&6>0; =C2=A0 /** if you can compress = the data=20 into bit field you may not need to bother with overhead of indexes as scann= ing=20 whole table or partition can be very fast **/ =C2=A0 =C2=A0 Thanks for your= comments. =C2=A0 I=20 don't see how any of your suggestions help with listing flagsin use in a=20 folder. My example-query lists a distinct set of flags in use in a folder. = =C2=A0 --=20 Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963=20 andreas@visena.com www.visena.com=20 =C2=A0 ------=_Part_12_1570353735.1426626168527 Content-Type: text/html;charset=UTF-8 Content-Transfer-Encoding: quoted-printable
P=C3=A5 tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass <hector.vass@metametrics.co.uk<= /a>>:

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

=C2=A0

/** you could consider holding the flags as integers as you can do mor= e stuff with simple math **/
=C2=A0
drop table if exists message;
create table message(
=C2=A0 =C2=A0 folder_id integer not NULL,
=C2=A0 =C2=A0 is_seen int not null default 0,
=C2=A0 =C2=A0 is_replied int not null default 0,
=C2=A0 =C2=A0 is_forwarded int not null default 0,
=C2=A0 =C2=A0 is_deleted int not null default 0,
=C2=A0 =C2=A0 is_draft int not null default 0,
=C2=A0 =C2=A0 is_flagged int not null default 0
);
=C2=A0insert 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;
=C2=A0
/** 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(
=C2=A0 =C2=A0 folder_id integer not NULL,
=C2=A0 =C2=A0 is_flag char(1),
=C2=A0 =C2=A0 is_val int
);
=C2=A0insert 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;
=C2=A0
/** key value pairs can use a lot of space as the folder_id has to be = repeated ... **/
/** so=C2=A0why 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;
=C2=A0
/** 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=C2=A0can consider=C2= =A0using=C2=A0a stored procedure in C=C2=A0... then this would be my prefer= red approach **/
=C2=A0
drop table if exists message;
create table message(
=C2=A0 =C2=A0 folder_id integer not NULL,
=C2=A0 =C2=A0 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;
=C2=A0
/** 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 **/
=C2=A0
=C2=A0
Thanks for your comments.
=C2=A0
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 us= e in a folder.
=C2=A0
=C2=A0
------=_Part_12_1570353735.1426626168527-- ------=_Part_11_848343039.1426626168485 Content-Type: image/png Content-Transfer-Encoding: base64 Content-Disposition: inline Content-ID: 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= ------=_Part_11_848343039.1426626168485-- ------=_Part_10_970455311.1426626168485--