Same condition in the CTE and in the subsequent JOIN using it

From: Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Same condition in the CTE and in the subsequent JOIN using it
Date: 2018-04-25 15:45:39
Message-ID: CAADeyWj2uzPYFWOH3hhSJ94f=mdWV0MQ7vYb+fW_pXYmkKFoxw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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);

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andreas Kretschmer 2018-04-25 17:36:41 Re: Same condition in the CTE and in the subsequent JOIN using it
Previous Message Charlin Barak 2018-04-25 15:35:17 Re: Using the public schema