Good evening,
I have written a custom function which works, but wonder if using same
condition twice looks suspicious and can be optimized.
Here is calling my function, it returns average score / day and average
time between moves / day:
# select * from words_stat_scores(1, '199928440415755383271');
out_day | out_diff | out_score
------------+----------+-----------
26.03.2018 | 75 | 10.5
27.03.2018 | 3 | 10.2
28.03.2018 | 324 | 17.8
29.03.2018 | 801 | 10.0
30.03.2018 | 12 | 19.5
31.03.2018 | 64 | 20.8
01.04.2018 | 48 | 12.3
02.04.2018 | 342 | 11.0
03.04.2018 | 12 | 14.5
04.04.2018 | 44 | 15.0
05.04.2018 | 116 | 13.6
06.04.2018 | 102 | 19.7
07.04.2018 | 54 | 14.8
08.04.2018 | 252 | 19.0
09.04.2018 | 272 | 10.4
10.04.2018 | 140 | 18.2
11.04.2018 | 41 | 11.4
12.04.2018 | 61 | 13.3
13.04.2018 | 182 | 15.3
14.04.2018 | 76 | 13.7
15.04.2018 | 199 | 20.1
16.04.2018 | 116 | 19.1
17.04.2018 | 390 | 20.1
18.04.2018 | 150 | 16.6
19.04.2018 | 448 | 15.9
20.04.2018 | 163 | 14.6
(26 rows)
And here is the function source code:
CREATE OR REPLACE FUNCTION words_stat_scores(
in_social integer,
in_sid text
) RETURNS TABLE (
out_day text,
out_diff numeric,
out_score numeric
) AS
$func$
WITH cte AS (
SELECT
DATE_TRUNC('day', m.played) AS day,
m.mid,
EXTRACT(EPOCH FROM m.played - LAG(m.played) OVER
(PARTITION BY m.gid ORDER BY m.played))::int/60 AS diff
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid)
JOIN words_social s ON (s.uid IN (g.player1, g.player2))
WHERE s.social = in_social --
CAN THIS BE REFERRED TO FROM BELOW?
AND s.sid = in_sid
AND m.played > CURRENT_TIMESTAMP - interval '1 month'
)
SELECT
TO_CHAR(c.day, 'DD.MM.YYYY'),
ROUND(AVG(c.diff)),
ROUND(AVG(m.score), 1)
FROM words_moves m
JOIN cte c using(mid)
JOIN words_social s USING(uid)
WHERE s.social = in_social
AND s.sid = in_sid
AND m.action = 'play'
GROUP BY c.day
ORDER BY c.day;
$func$ LANGUAGE sql STABLE;
By looking at the above source code, do you think, that the condition being
used twice (the s.social = in_social AND s.sid = in_sid) is "too much" and
can be optimized? :-)
Thank you for any hints, I apologize if my question is too specific and
difficult to answer...
Regards
Alex
P.S. My 3 tables are below -
CREATE TABLE words_social (
sid text NOT NULL,
social integer NOT NULL CHECK (0 < social AND social <= 64),
given text NOT NULL CHECK (given ~ '\S'),
family text,
photo text CHECK (photo ~* '^https?://...'),
lat float,
lng float,
stamp integer NOT NULL,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
PRIMARY KEY(sid, social)
);
CREATE TABLE words_moves (
mid BIGSERIAL PRIMARY KEY,
action text NOT NULL,
gid integer NOT NULL REFERENCES words_games ON DELETE CASCADE,
uid integer NOT NULL REFERENCES words_users ON DELETE CASCADE,
played timestamptz NOT NULL,
tiles jsonb,
letters text,
score integer CHECK(score >= 0)
);
CREATE TABLE words_games (
gid SERIAL PRIMARY KEY,
created timestamptz NOT NULL,
finished timestamptz,
player1 integer REFERENCES words_users(uid) ON DELETE CASCADE NOT
NULL CHECK (player1 <> player2),
player2 integer REFERENCES words_users(uid) ON DELETE CASCADE,
played1 timestamptz,
played2 timestamptz,
reason text, -- regular, resigned, expired, banned
state1 text, -- tie, winning, losing, draw, won, lost
state2 text, -- tie, winning, losing, draw, won, lost
hint1 text,
hint2 text,
chat1 integer NOT NULL CHECK (chat1 >= 0),
chat2 integer NOT NULL CHECK (chat2 >= 0),
score1 integer NOT NULL CHECK (score1 >= 0),
score2 integer NOT NULL CHECK (score2 >= 0),
hand1 char[7] NOT NULL,
hand2 char[7] NOT NULL,
pile char[116] NOT NULL,
letters char[15][15] NOT NULL,
values integer[15][15] NOT NULL,
bid integer NOT NULL REFERENCES words_boards ON DELETE CASCADE,
friendly boolean NOT NULL
);
CREATE INDEX words_games_state1_index ON words_games(state1);
CREATE INDEX words_games_state2_index ON words_games(state2);
CREATE INDEX words_games_reason_index ON words_games(reason);
