Alexander Farber schrieb am 20.02.2021 um 19:39:
So I am trying:
# SELECT
JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day,
JSONB_AGG(SUM(CASE WHEN reason='regular' or reason='resigned'
THEN 1 ELSE 0 END)::int) AS completed,
JSONB_AGG(SUM(CASE
On Sat, Feb 20, 2021 at 12:34 PM Alexander Farber <
alexander.far...@gmail.com> wrote:
> Ah, thank you...
>
> JSON support in PostgreSQL is cool and seems to be extended with each
> release.
>
> But standard tasks of returning a JSON map of lists or JSON list of list
> seem to be difficult to use.
Ah, thank you...
JSON support in PostgreSQL is cool and seems to be extended with each
release.
But standard tasks of returning a JSON map of lists or JSON list of list
seem to be difficult to use.
Greetings
Alex
On Sat, Feb 20, 2021 at 11:46 AM Alexander Farber <
alexander.far...@gmail.com> wrote:
> Then I have to split the query in 3 similar ones (with same condition)?
>
> I try:
>
> SELECT
> JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day
> FROM words_games
> WHERE fini
Then I have to split the query in 3 similar ones (with same condition)?
I try:
SELECT
JSONB_AGG(TO_CHAR(finished, '-MM-DD')) AS day
FROM words_games
WHERE finished > CURRENT_TIMESTAMP - INTERVAL '2 week'
GROUP BY day
ORDER BY day;
ERROR: aggreg
On Sat, Feb 20, 2021 at 11:39 AM Alexander Farber <
alexander.far...@gmail.com> wrote:
>
> Or is the syntax error about being able to use JSONB_AGG only once per
> SELECT query?
>
>
That.
David J.
Good evening,
I have the following query in 13.2:
# SELECT
TO_CHAR(finished, '-MM-DD') AS day,
SUM(CASE WHEN reason='regular' or reason='resigned' THEN 1
ELSE 0 END)::int AS completed,
SUM(CASE WHEN reason='expired' THEN 1 ELSE 0 END)::int AS
ex
With shared_buffers=16GB, pg_top shows:
last pid: 2613; load avg: 0.49, 0.45, 0.37; up 0+00:19:21
16:41:16
16 processes: 16 sleeping
CPU states: 9.2% user, 0.0% nice, 0.2% system, 90.4% idle, 0.1% iowait
Memory: 21G used, 42G free, 29M buffers, 18G cached
DB activity: 39 tps, 0
Thank you all, I will try at first
shared_buffers = 16GBand
index on words_scores(uid, length(word) desc)
On 2021-02-20 06:43:28 -0700, Michael Lewis wrote:
> Using auto_explain may be needed to catch the plan when it is slow, if the
> other suggestions don't resolve things for you.
Yes, auto_explain is very helpful. Unfortunately, by default it only
does an explain, not the equivalent of an explain a
Using auto_explain may be needed to catch the plan when it is slow, if the
other suggestions don't resolve things for you.
On 2021-02-19 21:25:36 +0100, Alexander Farber wrote:
> In the PostgreSQL log I have noticed that the duration for 2 particular
> queries
> have increased, especially in the evenings:
[...]
> One of the queries is actually a simple SELECT ... LIMIT 15 to find the 15
> longest words played by a use
On 2021-02-20 10:24:36 +0100, Alexander Farber wrote:
> The query when I try it does run in 15ms, but evening logs show the query (I
> think only small percentage of it) running 1-3s.
>
> At the same time my CentOS 8 server with 64 GB RAM is never loaded, the load
> average show by top is 0.35-0.4
Yes, Michael, that I have noticed too, but should have written more in my
original mail.
The query when I try it does run in 15ms, but evening logs show the query
(I think only small percentage of it) running 1-3s.
At the same time my CentOS 8 server with 64 GB RAM is never loaded, the
load avera
14 matches
Mail list logo
