Re: [GENERAL] Optimize query for listing un-read messages
On 01 May 2014, at 13:06, Andreas Joseph Krogh andr...@visena.com wrote: I have the following query to list all un-read messages for person with id=1: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 WHERE 1 = 1 AND NOT EXISTS(SELECT * FROM message_property pr WHERE pr.message_id = m.id AND pr.person_id = prop.person_id AND prop.is_read = TRUE) ; Since most messages will have prop.is_read = TRUE, that part of the query suffers from low selectivity. Querying for the opposite is probably much faster, which you may even be able to speed up more with a partial index on is_read = FALSE. Does anyone have suggestions on how to optimize the query or schema? It's important that any message not having an entry in message_property for a user is considered un-read. Do you really need to query message_property twice? I would think this would give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 AND prop.is_read = FALSE ; Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimize query for listing un-read messages
På lørdag 03. mai 2014 kl. 11:51:08, skrev Alban Hertroys haram...@gmail.com mailto:haram...@gmail.com: On 01 May 2014, at 13:06, Andreas Joseph Krogh andr...@visena.com wrote: I have the following query to list all un-read messages for person with id=1: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 WHERE 1 = 1 AND NOT EXISTS(SELECT * FROM message_property pr WHERE pr.message_id = m.id AND pr.person_id = prop.person_id AND prop.is_read = TRUE) ; Since most messages will have prop.is_read = TRUE, that part of the query suffers from low selectivity. Querying for the opposite is probably much faster, which you may even be able to speed up more with a partial index on is_read = FALSE. Does anyone have suggestions on how to optimize the query or schema? It's important that any message not having an entry in message_property for a user is considered un-read. Do you really need to query message_property twice? I would think this would give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 AND prop.is_read = FALSE ; That query doesn't produce the same reesult. -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com mailto:andr...@visena.com www.visena.com https://www.visena.com https://www.visena.com
Re: [GENERAL] Optimize query for listing un-read messages
On 03 May 2014, at 12:45, Andreas Joseph Krogh andr...@visena.com wrote: Do you really need to query message_property twice? I would think this would give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 AND prop.is_read = FALSE ; Ah yes, of course that would match a bit too much. This however does give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 WHERE prop.is_read IS NULL OR prop.is_read = FALSE ; That shaves off half the time of the query here, namely one indexscan. The remaining time appears to be spent finding the rows in “message that do not have a corresponding “message_property for the given (message_id, person_id) tuple. It’s basically trying to find no needle in a haystack, you won’t know that there is no needle until you’ve searched the entire haystack. It does seem to help a bit to create separate indexes on message_property.message_id and message_property.person_id; that reduces the sizes of the indexes that the database needs to match and merge other in order to find the missing message_id’s. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Optimize query for listing un-read messages
På lørdag 03. mai 2014 kl. 23:21:21, skrev Alban Hertroys haram...@gmail.com mailto:haram...@gmail.com: On 03 May 2014, at 12:45, Andreas Joseph Krogh andr...@visena.com wrote: Do you really need to query message_property twice? I would think this would give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 AND prop.is_read = FALSE ; Ah yes, of course that would match a bit too much. This however does give the same results: SELECT m.id AS message_id, prop.person_id, coalesce(prop.is_read, FALSE) AS is_read, m.subject FROM message m LEFT OUTER JOIN message_property prop ON prop.message_id = m.id AND prop.person_id = 1 WHERE prop.is_read IS NULL OR prop.is_read = FALSE ; That shaves off half the time of the query here, namely one indexscan. The remaining time appears to be spent finding the rows in “message that do not have a corresponding “message_property for the given (message_id, person_id) tuple. It’s basically trying to find no needle in a haystack, you won’t know that there is no needle until you’ve searched the entire haystack. It does seem to help a bit to create separate indexes on message_property.message_id and message_property.person_id; that reduces the sizes of the indexes that the database needs to match and merge other in order to find the missing message_id’s. I think the consesus here is to create a caching-table, there's no way around it as PG is unable to index the difference between two sets. -- Andreas Jospeh Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com mailto:andr...@visena.com www.visena.com https://www.visena.com https://www.visena.com
Re: [GENERAL] Server continuously enters to recovery mode.
DrakoRod wrote Hi everybody! I have a problem (really huge problem), I have one server of production, but yesterday in the night I saw this error: * ERROR: could not access status of transaction 2410303155 DETAIL: Could not open file pg_clog/08FA: No such file or directory * Solution: * dd if=/dev/zero/ of=data/pg_clog/08FA bs=256K count=1 * So I ran this solutions, no problem so far, but then (after 1 or 2 hours approximately), the server crash, I think somebody did something but did not tell me, no one did nothing! cool!! ¬¬. I started the server and this began in recovery mode, he started. But after some time (without apparent pattern), the server came into the recovery mode again. After that, the server continuously entering recovery mode, like I said without apparent pattern, between 3, 5 or 10 minutes run normally but then enter in the recovery mode again. I restart the server (began in recovery mode again), started, but after sometime he enter in recovery mode again. Try to recover the server with the PITR and nothing. The server version is 9.0.x in a Linux SUSE. The database size is the 336 GB. Please give me any help to recover the server! Thanks! The only help I'm good for on this topic is to tell you to supply log file data; especially those entries at and just before the server enters recovery mode. Good Luck! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Server-continuously-enters-to-recovery-mode-tp5802321p5802322.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Manipulating jsonb
Hello Oleg,
how are the odds that the '||' and '-' operators from jsonbx will be
included in the public 9.4 release?
Andreas
On 2 May 2014 21:21, Oleg Bartunov obartu...@gmail.com wrote:
Andreas,
take a look on https://github.com/erthalion/jsonbx. This is a place,
where all hstore functionality will be eventually ported. See this
table - https://gist.github.com/erthalion/10890778
Oleg
On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk ashei...@gmail.com wrote:
Hello,
I'm playing with a 9.4 preview and the upcoming jsonb datatype. But
I'm missing the basic modification syntax.
Given the following situation:
CREATE TABLE test(key int, jsonval jsonb);
INSERT INTO test VALUES(1, '{a: 1, c: 3}');
How can I UPDATE that row with '{b: 2, d: 4}' ? Something like
this does not work:
UPDATE test SET jsonval = jsonval || '{a: 1, c: 3}'::jsonb
where key = 1;
The result should be
{a: 1, b: 2, c: 3, d: 4}
The same goes for removing values.
Did I overlook something obvious or is there really no way to do that right
now?
Best regards,
Andreas Heiduk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Normalization in text search ranking
Hi all Another question regarding full text, this time about ranking. The ts_ranking() and ts_ranking_cd() accept a normalization integer/bit mask. In the documentation the different integers are somewhat laid out and it is said that some take into account the document length (1 and 2) while others take into account the number of unique words (8 and 16). To illustrate my following questions, take this tsvector: 'ate':9 'cat':3 'fat':2,11 Now, I was wondering how document length and unique words are calculated (from a high level perspective). I am correct in saying that, when counting the document length, the number of total pointers is summed up, meaning that in the above tsvector we have 4 words (resulting in an integer of 4 to use to divide the float). And when counting unique words, the calculation for the above tsvector would be 3, only counting the actual lexemes in there and not the amount of pointers? Also, final question, if you use integer 8 or 16 to influence the ranking float calculated, you would actual punish documents who are more unique? Meaning that this is just another way of giving shorter documents precedence over longer ones? Thanks again! Cheers, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Manipulating jsonb
Hello Oleg,
that's a pitty. I think at least some server-side processing for jsonb
would make a round package. But I will monitor Dimitri's extension.
Thank you very much for your answer.
Andreas
On 3 May 2014 00:26, Oleg Bartunov obartu...@gmail.com wrote:
No way, Andreas !
But, we hope, Dimitri will release his extension before 9.4, so
anybody could install it.
Oleg
On Sat, May 3, 2014 at 1:21 AM, Andreas Heiduk ashei...@gmail.com wrote:
Hello Oleg,
how are the odds that the '||' and '-' operators from jsonbx will be
included in the public 9.4 release?
Andreas
On 2 May 2014 21:21, Oleg Bartunov obartu...@gmail.com wrote:
Andreas,
take a look on https://github.com/erthalion/jsonbx. This is a place,
where all hstore functionality will be eventually ported. See this
table - https://gist.github.com/erthalion/10890778
Oleg
On Fri, May 2, 2014 at 10:32 PM, Andreas Heiduk ashei...@gmail.com wrote:
Hello,
I'm playing with a 9.4 preview and the upcoming jsonb datatype. But
I'm missing the basic modification syntax.
Given the following situation:
CREATE TABLE test(key int, jsonval jsonb);
INSERT INTO test VALUES(1, '{a: 1, c: 3}');
How can I UPDATE that row with '{b: 2, d: 4}' ? Something like
this does not work:
UPDATE test SET jsonval = jsonval || '{a: 1, c: 3}'::jsonb
where key = 1;
The result should be
{a: 1, b: 2, c: 3, d: 4}
The same goes for removing values.
Did I overlook something obvious or is there really no way to do that
right now?
Best regards,
Andreas Heiduk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Server continuously enters to recovery mode.
Hi everybody! I have a problem (really huge problem), I have one server of production, but yesterday in the night I saw this error: *ERROR: could not access status of transaction 2410303155 DETAIL: Could not open file pg_clog/08FA: No such file or directory* Solution: * dd if=/dev/zero/ of=data/pg_clog/08FA bs=256K count=1* So I ran this solutions, no problem so far, but then (after 1 or 2 hours approximately), the server crash, I think somebody did something but did not tell me, no one did nothing! cool!! ¬¬. I started the server and this began in recovery mode, he started. But after some time (without apparent pattern), the server came into the recovery mode again. After that, the server continuously entering recovery mode, like I said without apparent pattern, between 3, 5 or 10 minutes run normally but then enter in the recovery mode again. I restart the server (began in recovery mode again), started, but after sometime he enter in recovery mode again. Try to recover the server with the PITR and nothing. The server version is 9.0.x in a Linux SUSE. The database size is the 336 GB. Please give me any help to recover the server! Thanks! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Server-continuously-enters-to-recovery-mode-tp5802321.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Server continuously enters to recovery mode.
Try dumping the database, you might have a damaged table as in this case: http://www.postgresql.org/message-id/cagwygjwpiffet_k6qvxjjqo_jrqiueu+bszr1hurrh1obv8...@mail.gmail.com good luck. 2014-05-03 18:59 GMT-05:00 David G Johnston david.g.johns...@gmail.com: DrakoRod wrote Hi everybody! I have a problem (really huge problem), I have one server of production, but yesterday in the night I saw this error: * ERROR: could not access status of transaction 2410303155 DETAIL: Could not open file pg_clog/08FA: No such file or directory * Solution: * dd if=/dev/zero/ of=data/pg_clog/08FA bs=256K count=1 * So I ran this solutions, no problem so far, but then (after 1 or 2 hours approximately), the server crash, I think somebody did something but did not tell me, no one did nothing! cool!! ¬¬. I started the server and this began in recovery mode, he started. But after some time (without apparent pattern), the server came into the recovery mode again. After that, the server continuously entering recovery mode, like I said without apparent pattern, between 3, 5 or 10 minutes run normally but then enter in the recovery mode again. I restart the server (began in recovery mode again), started, but after sometime he enter in recovery mode again. Try to recover the server with the PITR and nothing. The server version is 9.0.x in a Linux SUSE. The database size is the 336 GB. Please give me any help to recover the server! Thanks! The only help I'm good for on this topic is to tell you to supply log file data; especially those entries at and just before the server enters recovery mode. Good Luck! David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Server-continuously-enters-to-recovery-mode-tp5802321p5802322.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- El genio es 1% inspiración y 99% transpiración. Thomas Alva Edison http://pglearn.blogspot.mx/
