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

Поиск
Список
Период
Сортировка
От Alexander Farber
Тема Same condition in the CTE and in the subsequent JOIN using it
Дата
Msg-id CAADeyWj2uzPYFWOH3hhSJ94f=mdWV0MQ7vYb+fW_pXYmkKFoxw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Same condition in the CTE and in the subsequent JOIN using it  (Andreas Kretschmer <andreas@a-kretschmer.de>)
Re: Same condition in the CTE and in the subsequent JOIN using it  (Vincent Veyron <vv.lists@wanadoo.fr>)
Re: Same condition in the CTE and in the subsequent JOIN using it  (Alban Hertroys <haramrae@gmail.com>)
Список 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);

В списке pgsql-general по дате отправления:

Предыдущее
От: Charlin Barak
Дата:
Сообщение: Re: Using the public schema
Следующее
От: Andreas Kretschmer
Дата:
Сообщение: Re: Same condition in the CTE and in the subsequent JOIN using it