Not sure which part of the query needs optimization

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Not sure which part of the query needs optimization

Alexander Farber
Good afternoon,

for each visitor of my website I generate a JSON list of 30 top players ( https://slova.de/words/top.php ), who played in the past week, with their average scores and average time between moves.

With 5 seconds this query is taking quite a bit of time: https://explain.depesz.com/s/wMMV

I have noticed the

    Seq Scan on words_moves (cost=0.00..81,448.79 rows=1,747 width=4) (actual time=0.443..161.673 rows=15,009 loops=30)
    Filter: (uid = u.uid)
    Rows Removed by Filter: 1494728

and added a:

    CREATE INDEX ON words_moves(uid);

which has improved the query time to 800ms: https://explain.depesz.com/s/xgv1

However now I am not sure anymore, what else could be improved in my query:

WITH last_week_moves AS (
                        SELECT
                                m.gid,
                                m.uid,
                                m.played - LAG(m.played) OVER(PARTITION BY m.gid ORDER BY played) AS diff
                        FROM words_moves m
                        JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2))
                        -- only show players who where active in the last week
                        WHERE m.played > CURRENT_TIMESTAMP - interval '1 week'
                )
                SELECT
                        u.uid,
                        u.elo,
                        (SELECT TO_CHAR(AVG(diff), 'HH24:MI') FROM last_week_moves WHERE uid = u.uid) AS avg_time,
                        (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score,
                        s.given,
                        s.photo
                FROM words_users u
                JOIN words_social s USING (uid)
                WHERE u.elo > 1500
                -- take the most recent record from words_social
                AND NOT EXISTS (SELECT 1
                                FROM words_social x
                                WHERE s.uid = x.uid
                                AND x.stamp > s.stamp)
                -- only show players who where active in the last week
                AND EXISTS (SELECT 1
                            FROM last_week_moves m
                            WHERE u.uid = m.uid
                            AND m.diff IS NOT NULL)
                ORDER BY u.elo DESC
                LIMIT 30;

I have tried changing to LEFT JOIN LATERAL as in -

WITH last_week_moves AS (
                        SELECT
                                m.gid,
                                m.uid,
                                m.played - LAG(m.played) OVER(PARTITION BY m.gid ORDER BY played) AS diff
                        FROM words_moves m
                        JOIN words_games g ON (m.gid = g.gid AND m.uid in (g.player1, g.player2))
                        -- only show players who where active in the last week
                        WHERE m.played > CURRENT_TIMESTAMP - interval '1 week'
                )
                SELECT
                        u.uid,
                        u.elo,
                        (SELECT TO_CHAR(AVG(diff), 'HH24:MI') FROM last_week_moves WHERE uid = u.uid) AS avg_time,
                        (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS avg_score,
                        s.given,
                        s.photo
                FROM words_users u
                -- take the most recent record from words_social
                LEFT JOIN LATERAL (SELECT * FROM words_social AS s WHERE s.uid = u.uid ORDER BY s.stamp DESC LIMIT 1) AS s ON TRUE
                WHERE u.elo > 1500
                -- only show players who where active in the last week
                AND EXISTS (SELECT 1
                            FROM last_week_moves m
                            WHERE u.uid = m.uid
                            AND m.diff IS NOT NULL)
                ORDER BY u.elo DESC
                LIMIT 30;

But this has not helped much (please see https://explain.depesz.com/s/PrF or the same output below) :

                                                                                 QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=89502.34..272686.22 rows=30 width=160) (actual time=215.796..842.446 rows=30 loops=1)
   CTE last_week_moves
     ->  WindowAgg  (cost=87308.08..87316.34 rows=367 width=32) (actual time=183.075..193.613 rows=33221 loops=1)
           ->  Sort  (cost=87308.08..87309.00 rows=367 width=16) (actual time=183.069..184.359 rows=33221 loops=1)
                 Sort Key: m_1.gid, m_1.played
                 Sort Method: quicksort  Memory: 4132kB
                 ->  Gather  (cost=13632.94..87292.45 rows=367 width=16) (actual time=37.204..172.827 rows=33221 loops=1)
                       Workers Planned: 2
                       Workers Launched: 2
                       ->  Hash Join  (cost=12632.94..86255.75 rows=153 width=16) (actual time=36.183..168.676 rows=11074 loops=3)
                             Hash Cond: (m_1.gid = g.gid)
                             Join Filter: ((m_1.uid = g.player1) OR (m_1.uid = g.player2))
                             ->  Parallel Seq Scan on words_moves m_1  (cost=0.00..73600.05 rows=8666 width=16) (actual time=0.761..130.844 rows=11074 loops=3)
                                   Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
                                   Rows Removed by Filter: 492241
                             ->  Hash  (cost=12007.42..12007.42 rows=50042 width=12) (actual time=35.236..35.236 rows=50044 loops=3)
                                   Buckets: 65536  Batches: 1  Memory Usage: 2663kB
                                   ->  Seq Scan on words_games g  (cost=0.00..12007.42 rows=50042 width=12) (actual time=0.007..28.981 rows=50044 loops=3)
   ->  Result  (cost=2186.01..1278367.01 rows=209 width=160) (actual time=215.795..842.425 rows=30 loops=1)
         ->  Sort  (cost=2186.01..2186.53 rows=209 width=96) (actual time=205.069..205.087 rows=30 loops=1)
               Sort Key: u.elo DESC
               Sort Method: top-N heapsort  Memory: 32kB
               ->  Nested Loop Left Join  (cost=12.19..2179.83 rows=209 width=96) (actual time=203.009..205.040 rows=110 loops=1)
                     ->  Hash Semi Join  (cost=11.90..440.43 rows=209 width=8) (actual time=202.984..204.451 rows=110 loops=1)
                           Hash Cond: (u.uid = m.uid)
                           ->  Seq Scan on words_users u  (cost=0.00..415.96 rows=418 width=8) (actual time=0.005..1.422 rows=418 loops=1)
                                 Filter: (elo > 1500)
                                 Rows Removed by Filter: 10139
                           ->  Hash  (cost=7.34..7.34 rows=365 width=4) (actual time=202.975..202.975 rows=31549 loops=1)
                                 Buckets: 32768 (originally 1024)  Batches: 1 (originally 1)  Memory Usage: 1366kB
                                 ->  CTE Scan on last_week_moves m  (cost=0.00..7.34 rows=365 width=4) (actual time=183.080..200.197 rows=31549 loops=1)
                                       Filter: (diff IS NOT NULL)
                                       Rows Removed by Filter: 1672
                     ->  Limit  (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110)
                           ->  Index Scan using words_social_uid_stamp_idx on words_social s  (cost=0.29..8.30 rows=1 width=180) (actual time=0.005..0.005 rows=1 loops=110)
                                 Index Cond: (uid = u.uid)
         SubPlan 2
           ->  Aggregate  (cost=8.27..8.28 rows=1 width=32) (actual time=1.838..1.838 rows=1 loops=30)
                 ->  CTE Scan on last_week_moves  (cost=0.00..8.26 rows=2 width=16) (actual time=0.176..1.775 rows=403 loops=30)
                       Filter: (uid = u.uid)
                       Rows Removed by Filter: 32818
         SubPlan 3
           ->  Aggregate  (cost=6097.83..6097.84 rows=1 width=32) (actual time=19.401..19.401 rows=1 loops=30)
                 ->  Bitmap Heap Scan on words_moves  (cost=33.97..6093.45 rows=1748 width=4) (actual time=1.680..18.153 rows=15011 loops=30)
                       Recheck Cond: (uid = u.uid)
                       Heap Blocks: exact=216312
                       ->  Bitmap Index Scan on words_moves_uid_idx  (cost=0.00..33.54 rows=1748 width=0) (actual time=0.979..0.979 rows=15011 loops=30)
                             Index Cond: (uid = u.uid)
 Planning time: 0.508 ms
 Execution time: 843.322 ms
(50 rows)

Does anybody please have a suggestion, what else could be improved?

Below are the tables from the above query and I use PostgreSQL 10.6  -

words=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |                 Default
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null | nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE

words=> \d words_users
                                         Table "public.words_users"
    Column     |           Type           | Collation | Nullable |                 Default
---------------+--------------------------+-----------+----------+------------------------------------------
 uid           | integer                  |           | not null | nextval('words_users_uid_seq'::regclass)
 created       | timestamp with time zone |           | not null |
 visited       | timestamp with time zone |           | not null |
 ip            | inet                     |           | not null |
 fcm           | text                     |           |          |
 apns          | text                     |           |          |
 adm           | text                     |           |          |
 motto         | text                     |           |          |
 vip_until     | timestamp with time zone |           |          |
 grand_until   | timestamp with time zone |           |          |
 banned_until  | timestamp with time zone |           |          |
 banned_reason | text                     |           |          |
 elo           | integer                  |           | not null |
 medals        | integer                  |           | not null |
 coins         | integer                  |           | not null |
Indexes:
    "words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
    "words_users_banned_reason_check" CHECK (length(banned_reason) > 0)
    "words_users_elo_check" CHECK (elo >= 0)
    "words_users_medals_check" CHECK (medals >= 0)
Referenced by:
    TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
    TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE

words=> \d words_games
                                      Table "public.words_games"
  Column  |           Type           | Collation | Nullable |                 Default
----------+--------------------------+-----------+----------+------------------------------------------
 gid      | integer                  |           | not null | nextval('words_games_gid_seq'::regclass)
 created  | timestamp with time zone |           | not null |
 finished | timestamp with time zone |           |          |
 player1  | integer                  |           | not null |
 player2  | integer                  |           |          |
 played1  | timestamp with time zone |           |          |
 played2  | timestamp with time zone |           |          |
 state1   | text                     |           |          |
 state2   | text                     |           |          |
 reason   | text                     |           |          |
 hint1    | text                     |           |          |
 hint2    | text                     |           |          |
 score1   | integer                  |           | not null |
 score2   | integer                  |           | not null |
 chat1    | integer                  |           | not null |
 chat2    | integer                  |           | not null |
 hand1    | character(1)[]           |           | not null |
 hand2    | character(1)[]           |           | not null |
 pile     | character(1)[]           |           | not null |
 letters  | character(1)[]           |           | not null |
 values   | integer[]                |           | not null |
 bid      | integer                  |           | not null |
 friendly | boolean                  |           |          |
Indexes:
    "words_games_pkey" PRIMARY KEY, btree (gid)
    "words_games_player1_coalesce_idx" btree (player1, COALESCE(finished, 'infinity'::timestamp with time zone))
    "words_games_player2_coalesce_idx" btree (player2, COALESCE(finished, 'infinity'::timestamp with time zone))
Check constraints:
    "words_games_chat1_check" CHECK (chat1 >= 0)
    "words_games_chat2_check" CHECK (chat2 >= 0)
    "words_games_check" CHECK (player1 <> player2)
    "words_games_score1_check" CHECK (score1 >= 0)
    "words_games_score2_check" CHECK (score2 >= 0)
Foreign-key constraints:
    "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE
    "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE
    "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE
    TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE

Thank you
Alex

Reply | Threaded
Open this post in threaded view
|

Re: Not sure which part of the query needs optimization

Andrew Gierth
>>>>> "Alexander" == Alexander Farber <[hidden email]> writes:

 Alexander> Good afternoon,

 Alexander> for each visitor of my website I generate a JSON list of 30
 Alexander> top players ( https://slova.de/words/top.php ), who played
 Alexander> in the past week, with their average scores and average time
 Alexander> between moves.

 -> Parallel Seq Scan on words_moves m_1
     (cost=0.00..73600.05 rows=8666 width=16)
     (actual time=0.761..130.844 rows=11074 loops=3)
      Filter: (played > (CURRENT_TIMESTAMP - '7 days'::interval))
      Rows Removed by Filter: 492241

This is telling you that an index on words_moves(played) would likely
help.

But the real hot point in the query is here (reformatted for clarity):

 -> Aggregate  (cost=6097.83..6097.84 rows=1 width=32)
               (actual time=19.401..19.401 rows=1 loops=30)
    -> Bitmap Heap Scan on words_moves
        (cost=33.97..6093.45 rows=1748 width=4)
        (actual time=1.680..18.153 rows=15011 loops=30)
        Recheck Cond: (uid = u.uid)
        Heap Blocks: exact=216312
       -> Bitmap Index Scan on words_moves_uid_idx
           (cost=0.00..33.54 rows=1748 width=0)
           (actual time=0.979..0.979 rows=15011 loops=30)
           Index Cond: (uid = u.uid)

(the explain.depesz.com view points this out with an orange highlight)

This corresponds to this subquery:

  (SELECT ROUND(AVG(score), 1)
     FROM words_moves
    WHERE uid = u.uid) AS avg_score,

The basic issue here is that you're calculating an average over around
15k rows per user, for each user in the query result (so 30 times,
making 450k rows). You can see from the "Heap Blocks" stat that this is
having to scan a lot of data; it's taking on average 19.4ms per user,
but multiply that by 30 users and you get ~580ms total, or about 70% of
the total execution time.

The obvious thing to do is to keep a computed average score for each
user - either in a separate table which you update based on changes to
words_moves, which you could do with a trigger, or using a materialized
view which you refresh at suitable intervals (this has the drawback that
the data will not be immediately up-to-date).

Combining these two changes should get you to under 100ms, maybe.

--
Andrew (irc:RhodiumToad)

Reply | Threaded
Open this post in threaded view
|

Re: Not sure which part of the query needs optimization

Alexander Farber
Thank you for the comments, Andrew -

On Mon, Jan 7, 2019 at 8:40 PM Andrew Gierth <[hidden email]> wrote:
The obvious thing to do is to keep a computed average score for each
user - either in a separate table which you update based on changes to
words_moves, which you could do with a trigger, or using a materialized
view which you refresh at suitable intervals (this has the drawback that
the data will not be immediately up-to-date).

Combining these two changes should get you to under 100ms, maybe.


I was considering creating a cronjob, but now I will better create a trigger as you have suggested

Regards
Alex