agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedEffective query for listing flags in use by messages in a folder
12+ messages / 2 participants
[nested] [flat]
* Effective query for listing flags in use by messages in a folder
@ 2015-03-17 07:45 Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-17 07:45 UTC (permalink / raw)
To: pgsql-sql
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_seenboolean 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_idxON message(folder_id) where is_replied = TRUE;
create indexmessage_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)
whereis_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
limit1) 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 wherefolder_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;
<https://www.visena.com;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-17 13:31 Hector Vass <hector.vass@metametrics.co.uk>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Hector Vass @ 2015-03-17 13:31 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; 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 **/
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
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-17 21:02 Andreas Joseph Krogh <andreas@visena.com>
parent: Hector Vass <hector.vass@metametrics.co.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-17 21:02 UTC (permalink / raw)
To: pgsql-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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 11:07 Hector Vass <hector.vass@metametrics.co.uk>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Hector Vass @ 2015-03-18 11:07 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
Andreas ... your code and one of my examples ... I have modified my option 2 to give an example with data that gives you I believe exactly the same output (one row for each flag set for folder_id=3 with the text representation of the flag) ... when you satisfy yourself this produces the same results you might then want to go back and re-read my original post which rather than feeding you verbatim how to produce exactly the same results gave the the pro's and con's of 3x different approaches... I chose to illustrate my option 2 because it is easy to understand and is a reasonable production solution, option 1 was really just to get you thinking differently about how to do this and option 3 I concede was more advanced and probably but requires skills other than plain SQL to implement.
Your code (I have added data so that this is a full working code snippet)
drop table if exists message;
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
);
insert into message values
(1,'f','f','f','f','f','f'),
(2,'f','f','f','f','f','t'),
(3,'f','f','f','f','t','t'),
(4,'f','f','f','t','f','t')
;
--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
;
My No 2
drop type if exists myflags;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
is_flag myflags
);
insert into message values
(2,'is_flagged'),
(3,'is_draft'),(3,'is_flagged'),
(4,'is_deleted'),(4,'is_flagged')
;
select is_flag from message where folder_id=3;
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 21:02
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
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 flags in 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;
[cid:part_F7646460741003PFML0]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../../1426676819968.86115@metametrics.co.uk/3-ATT00001.png)
download | view image
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 11:59 Andreas Joseph Krogh <andreas@visena.com>
parent: Hector Vass <hector.vass@metametrics.co.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-18 11:59 UTC (permalink / raw)
To: pgsql-sql
På onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto:hector.vass@metametrics.co.uk>>: Andreas
... your code and one of my examples ... I have modified my option 2 to give
an example with data that gives you I believe exactly the same output (one row
for each flag set for folder_id=3 with the text representation of the
flag) ... when you satisfy yourself this produces the same results you might
then want to go back and re-read my original post which rather than feeding
you verbatim how to produce exactly the same results gave the the pro's and
con's of 3x different approaches... I chose to illustrate my option 2 because
it is easy to understand and is a reasonable production solution, option 1 was
really just to get you thinking differently about how to do this and option 3 I
concede was more advanced and probably but requires skills other than plain SQL
to implement.
It's not that I didn't read you post, I just don't see how it solves the
problem of listing a distinct set of flags being set on messages in a folder.
AFAICS your examples list messages with any or a specific set of flags set,
which is not what I'm after. I see now that I didn't specify the "msg"-column
so maybe it wasn't clear that the there's only one tuple in "message" for each
message and a message may have several flags set. This is a more realistic
table, with "msg" as varchar holding the actual text of the message: create
table message( folder_id integer not NULL, msg varchar NOT NULL, is_seen
boolean NOT NULL default false, is_replied boolean not null default false,
is_forwardedboolean 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); -- 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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 12:08 Hector Vass <hector.vass@metametrics.co.uk>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Hector Vass @ 2015-03-18 12:08 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
do you want to post some insert statements to populate the table message with some realistic example data..
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: 18 March 2015 11:59
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
På onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <hector.vass@metametrics.co.uk<mailto:hector.vass@metametrics.co.uk>>:
Andreas ... your code and one of my examples ... I have modified my option 2 to give an example with data that gives you I believe exactly the same output (one row for each flag set for folder_id=3 with the text representation of the flag) ... when you satisfy yourself this produces the same results you might then want to go back and re-read my original post which rather than feeding you verbatim how to produce exactly the same results gave the the pro's and con's of 3x different approaches... I chose to illustrate my option 2 because it is easy to understand and is a reasonable production solution, option 1 was really just to get you thinking differently about how to do this and option 3 I concede was more advanced and probably but requires skills other than plain SQL to implement.
It's not that I didn't read you post, I just don't see how it solves the problem of listing a distinct set of flags being set on messages in a folder. AFAICS your examples list messages with any or a specific set of flags set, which is not what I'm after.
I see now that I didn't specify the "msg"-column so maybe it wasn't clear that the there's only one tuple in "message" for each message and a message may have several flags set.
This is a more realistic table, with "msg" as varchar holding the actual text of the message:
create table message(
folder_id integer not NULL,
msg varchar 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
);
--
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_F764646534471GSEI0S]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../../1426680525528.15871@metametrics.co.uk/3-ATT00001.png)
download | view image
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 12:20 Andreas Joseph Krogh <andreas@visena.com>
parent: Hector Vass <hector.vass@metametrics.co.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-18 12:20 UTC (permalink / raw)
To: pgsql-sql
På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto:hector.vass@metametrics.co.uk>>: do you
want to post some insert statements to populate the table message with
some realistic example data..
Sure: drop table if EXISTS message; create table message( folder_id integer
not NULL, msg varchar NOT NULL, is_seen boolean NOT NULL default false,
is_repliedboolean 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 ); INSERT INTO message(
folder_id, msg, is_seen, is_replied, is_forwarded, is_deleted, is_draft,
is_flagged) values(1, 'msg a', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1,
'msg b', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg c', TRUE, FALSE,
FALSE, FALSE, FALSE, FALSE) , (1, 'msg d', TRUE, TRUE, FALSE, FALSE, FALSE,
FALSE) , (1, 'msg e', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg f',
FALSE, FALSE, FALSE, FALSE, FALSE, FALSE) , (1, 'msg g', FALSE, FALSE, FALSE,
FALSE, FALSE, FALSE) , (1, 'msg h', TRUE, FALSE, FALSE, TRUE, FALSE, FALSE) ;
create indexmessage_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_idxON message(folder_id) where is_flagged = TRUE;
create indexmessage_folder_id_draft_idx ON message(folder_id) where is_draft =
TRUE; select 'is_deleted' as falgs from (select * from message where folder_id =
1AND is_deleted limit 1) as q UNION select 'is_forwarded' from (select * from
message wherefolder_id = 1 AND is_forwarded limit 1) as q UNION select
'is_replied'from (select * from message where folder_id = 1 AND is_replied limit
1) as q UNION select 'is_seen' from (select * from message where folder_id = 1
ANDis_seen limit 1) as q UNION select 'is_flagged' from (select * from message
wherefolder_id = 1 AND is_flagged limit 1) as q UNION select 'is_draft' from (
select* from message where folder_id = 1 AND is_draft limit 1) as q ; Yields:
falgs
------------
is_deleted
is_replied
is_seen
(3 rows) -- 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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 12:54 Hector Vass <hector.vass@metametrics.co.uk>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 1 reply; 12+ messages in thread
From: Hector Vass @ 2015-03-18 12:54 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
OK I get it ..
drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message values
(1,'msg b','is_seen'),
(1,'msg c','is_seen'),
(1,'msg d','is_seen'),
(1,'msg d','is_replied'),
(1,'msg e','is_seen'),
(1,'msg e','is_replied'),
(1,'msg h','is_deleted')
;
select is_flag from message where folder_id=1 group by 1;
work=# select is_flag from message where folder_id=1 group by 1;
is_flag
------------
is_seen
is_replied
is_deleted
(3 rows)
?
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: 18 March 2015 12:20
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <hector.vass@metametrics.co.uk<mailto:hector.vass@metametrics.co.uk>>:
do you want to post some insert statements to populate the table message with some realistic example data..
Sure:
drop table if EXISTS message;
create table message(
folder_id integer not NULL,
msg varchar 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
);
INSERT INTO message(folder_id, msg, is_seen, is_replied, is_forwarded, is_deleted, is_draft, is_flagged)
values(1, 'msg a', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg b', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg c', TRUE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg d', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg e', TRUE, TRUE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg f', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg g', FALSE, FALSE, FALSE, FALSE, FALSE, FALSE)
, (1, 'msg h', TRUE, FALSE, FALSE, TRUE, FALSE, FALSE)
;
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' as falgs from (select * from message where folder_id = 1 AND is_deleted limit 1) as q
UNION
select 'is_forwarded' from (select * from message where folder_id = 1 AND is_forwarded limit 1) as q
UNION
select 'is_replied' from (select * from message where folder_id = 1 AND is_replied limit 1) as q
UNION
select 'is_seen' from (select * from message where folder_id = 1 AND is_seen limit 1) as q
UNION
select 'is_flagged' from (select * from message where folder_id = 1 AND is_flagged limit 1) as q
UNION
select 'is_draft' from (select * from message where folder_id = 1 AND is_draft limit 1) as q
;
Yields:
falgs
------------
is_deleted
is_replied
is_seen
(3 rows)
--
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_F764646561758A3NHED]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../../1426683287617.19714@metametrics.co.uk/3-ATT00001.png)
download | view image
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 13:01 Andreas Joseph Krogh <andreas@visena.com>
parent: Hector Vass <hector.vass@metametrics.co.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-18 13:01 UTC (permalink / raw)
To: pgsql-sql
På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto:hector.vass@metametrics.co.uk>>: OK I get
it ..
drop type if exists myflags cascade; create type myflags as
enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message; create table message( folder_id integer not
NULL, msg varchar(200), is_flag myflags ); insert into message values
(1,'msg b','is_seen'), (1,'msg c','is_seen'), (1,'msg d','is_seen'), (1,'msg
d','is_replied'), (1,'msg e','is_seen'), (1,'msg e','is_replied'), (1,'msg
h','is_deleted') ; Not quite; you have 2 entries, one for each flag, for "msg
d". I must haveone tuple per message in this table. -- 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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 13:19 Hector Vass <hector.vass@metametrics.co.uk>
parent: Andreas Joseph Krogh <andreas@visena.com>
0 siblings, 2 replies; 12+ messages in thread
From: Hector Vass @ 2015-03-18 13:19 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
My recommendation is to hold the data as key value pairs not as a table with columns for each flag..
Taking your table message ... and loading into this key value pair table messaage2..
drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message2;
create table message2(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message2 select folder_id,msg,'is_seen' from message where is_seen is TRUE;
insert into message2 select folder_id,msg,'is_replied' from message where is_replied is TRUE;
insert into message2 select folder_id,msg,'is_forwarded' from message where is_forwarded is TRUE;
insert into message2 select folder_id,msg,'is_deleted' from message where is_deleted is TRUE;
insert into message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message where is_flagged is TRUE;
select is_flag from message2 where folder_id=1 group by 1;
?
work=# select is_flag from message2 where folder_id=1 group by 1;
is_flag
------------
is_seen
is_deleted
is_replied
(3 rows)
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: 18 March 2015 13:01
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <hector.vass@metametrics.co.uk<mailto:hector.vass@metametrics.co.uk>>:
OK I get it ..
drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message values
(1,'msg b','is_seen'),
(1,'msg c','is_seen'),
(1,'msg d','is_seen'),
(1,'msg d','is_replied'),
(1,'msg e','is_seen'),
(1,'msg e','is_replied'),
(1,'msg h','is_deleted')
;
Not quite; you have 2 entries, one for each flag, for "msg d". I must have one tuple per message in this table.
--
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_F764646621869HEPVJS]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../../1426684757283.55625@metametrics.co.uk/3-ATT00001.png)
download | view image
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-18 13:37 Andreas Joseph Krogh <andreas@visena.com>
parent: Hector Vass <hector.vass@metametrics.co.uk>
1 sibling, 0 replies; 12+ messages in thread
From: Andreas Joseph Krogh @ 2015-03-18 13:37 UTC (permalink / raw)
To: pgsql-sql
På onsdag 18. mars 2015 kl. 14:19:18, skrev Hector Vass <
hector.vass@metametrics.co.uk <mailto:hector.vass@metametrics.co.uk>>: My
recommendation is to hold the data as key value pairs not as a table with
columns for each flag..
Taking your table message ... and loading into this key value pair table
messaage2..
drop type if exists myflags cascade; create type myflags as
enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message2; create table message2( folder_id integer not
NULL, msg varchar(200), is_flag myflags ); insert into message2 select
folder_id,msg,'is_seen' from message where is_seen is TRUE; insert into
message2 select folder_id,msg,'is_replied' from message where is_replied is
TRUE; insert into message2 select folder_id,msg,'is_forwarded' from message
where is_forwarded is TRUE; insert into message2 select
folder_id,msg,'is_deleted' from message where is_deleted is TRUE; insert into
message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message where
is_flagged is TRUE; select is_flag from message2 where folder_id=1 group by 1;
work=# select is_flag from message2 where folder_id=1 group by 1; is_flag
------------ is_seen is_deleted is_replied (3 rows) New messages are
inserted into the message-table "all the time" and it seems quite expensive to
keep this key-value table updated (which it must be, using triggers). My
version returns in sub-millisecond for a folder with > 100K messages in it,
which I think is not bad, I just don't like the looks of the query and all the
indexes required. Thanks for looking into this. -- 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;
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: Effective query for listing flags in use by messages in a folder
@ 2015-03-19 08:28 Hector Vass <hector.vass@metametrics.co.uk>
parent: Hector Vass <hector.vass@metametrics.co.uk>
1 sibling, 0 replies; 12+ messages in thread
From: Hector Vass @ 2015-03-19 08:28 UTC (permalink / raw)
To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-sql
If you cannot change the message table and only want something more elegant than union & limit 1 then could produce array of flags and unnest them ... dont know what performance would be like ... i
select
-- folder_id,
flags
from (
select
folder_id,
unnest(
array[
case when max(is_seen::int)=1 then 'is_seen' end,
case when max(is_replied::int)=1 then 'is_replied' end,
case when max(is_forwarded::int)=1 then 'is_forwarded' end,
case when max(is_deleted::int)=1 then 'is_deleted' end,
case when max(is_draft::int)=1 then 'is_draft' end,
case when max(is_flagged::int)=1 then 'is_flagged' end
]
) as flags
from message
group by 1
)x
where folder_id=1 and flags is not null;
?
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 Hector Vass <hector.vass@metametrics.co.uk>
Sent: 18 March 2015 13:19
To: Andreas Joseph Krogh; pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
My recommendation is to hold the data as key value pairs not as a table with columns for each flag..
Taking your table message ... and loading into this key value pair table messaage2..
drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message2;
create table message2(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message2 select folder_id,msg,'is_seen' from message where is_seen is TRUE;
insert into message2 select folder_id,msg,'is_replied' from message where is_replied is TRUE;
insert into message2 select folder_id,msg,'is_forwarded' from message where is_forwarded is TRUE;
insert into message2 select folder_id,msg,'is_deleted' from message where is_deleted is TRUE;
insert into message2 select folder_id,msg,'is_draft' from message where is_draft is TRUE;
insert into message2 select folder_id,msg,'is_flagged' from message where is_flagged is TRUE;
select is_flag from message2 where folder_id=1 group by 1;
?
work=# select is_flag from message2 where folder_id=1 group by 1;
is_flag
------------
is_seen
is_deleted
is_replied
(3 rows)
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: 18 March 2015 13:01
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Effective query for listing flags in use by messages in a folder
På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <hector.vass@metametrics.co.uk<mailto:hector.vass@metametrics.co.uk>>:
OK I get it ..
drop type if exists myflags cascade;
create type myflags as enum('is_seen','is_replied','is_forwarded','is_deleted','is_draft','is_flagged');
drop table if exists message;
create table message(
folder_id integer not NULL,
msg varchar(200),
is_flag myflags
);
insert into message values
(1,'msg b','is_seen'),
(1,'msg c','is_seen'),
(1,'msg d','is_seen'),
(1,'msg d','is_replied'),
(1,'msg e','is_seen'),
(1,'msg e','is_replied'),
(1,'msg h','is_deleted')
;
Not quite; you have 2 entries, one for each flag, for "msg d". I must have one tuple per message in this table.
--
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_F764646621869HEPVJS]<https://www.visena.com;
Attachments:
[image/png] ATT00001.png (1.9K, ../../1426753712227.33226@metametrics.co.uk/3-ATT00001.png)
download | view image
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2015-03-19 08:28 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-03-17 07:45 Effective query for listing flags in use by messages in a folder Andreas Joseph Krogh <andreas@visena.com>
2015-03-17 13:31 ` Hector Vass <hector.vass@metametrics.co.uk>
2015-03-17 21:02 ` Andreas Joseph Krogh <andreas@visena.com>
2015-03-18 11:07 ` Hector Vass <hector.vass@metametrics.co.uk>
2015-03-18 11:59 ` Andreas Joseph Krogh <andreas@visena.com>
2015-03-18 12:08 ` Hector Vass <hector.vass@metametrics.co.uk>
2015-03-18 12:20 ` Andreas Joseph Krogh <andreas@visena.com>
2015-03-18 12:54 ` Hector Vass <hector.vass@metametrics.co.uk>
2015-03-18 13:01 ` Andreas Joseph Krogh <andreas@visena.com>
2015-03-18 13:19 ` Hector Vass <hector.vass@metametrics.co.uk>
2015-03-18 13:37 ` Andreas Joseph Krogh <andreas@visena.com>
2015-03-19 08:28 ` Hector Vass <hector.vass@metametrics.co.uk>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox