From: | Vincent Veyron <vv(dot)lists(at)wanadoo(dot)fr> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Same condition in the CTE and in the subsequent JOIN using it |
Date: | 2018-04-25 19:32:00 |
Message-ID: | 20180425213200.5173fd2d2f2052392948f98c@wanadoo.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 25 Apr 2018 17:45:39 +0200
Alexander Farber <alexander(dot)farber(at)gmail(dot)com> wrote:
> 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? :-)
I would say so, because as you've already applied the filter in the CTE it won't have any effect.
But anyway, since you are not using any column from words_social in your main query, you can do away with it entirely and just remove
> JOIN words_social s USING(uid)
> WHERE s.social = in_social
> AND s.sid = in_sid
--
Bien à vous, Vincent Veyron
https://compta.libremen.com
Logiciel libre de comptabilité générale en partie double
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2018-04-25 20:03:29 | Re: Same condition in the CTE and in the subsequent JOIN using it |
Previous Message | Adrian Klaver | 2018-04-25 18:24:21 | Re: Issue on public schéma with Pg_restore |