Обсуждение: Effective query for listing flags in use by messages in a folder

Поиск
Список
Период
Сортировка

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

От
Andreas Joseph Krogh
Дата:
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
Вложения

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

От
Hector Vass
Дата:

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

8   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
Вложения

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

От
Andreas Joseph Krogh
Дата:
På tirsdag 17. mars 2015 kl. 14:31:28, skrev Hector Vass <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
 
Вложения

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

От
Hector Vass
Дата:

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

8   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>:

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
 
Вложения

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

От
Andreas Joseph Krogh
Дата:
På onsdag 18. mars 2015 kl. 12:07:00, skrev Hector Vass <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
 
Вложения

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

От
Hector Vass
Дата:

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

8   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>:

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
 
Вложения

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

От
Andreas Joseph Krogh
Дата:
På onsdag 18. mars 2015 kl. 13:08:45, skrev Hector Vass <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
 
Вложения

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

От
Hector Vass
Дата:

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

8   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>:

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
 
Вложения

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

От
Andreas Joseph Krogh
Дата:
På onsdag 18. mars 2015 kl. 13:54:50, skrev Hector Vass <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
 
Вложения

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

От
Hector Vass
Дата:

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

8   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>:

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
 
Вложения

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

От
Andreas Joseph Krogh
Дата:
På onsdag 18. mars 2015 kl. 14:19:18, skrev Hector Vass <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
 
Вложения

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

От
Hector Vass
Дата:

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

8   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

8   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>:

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
 
Вложения