Adding LEFT JOIN to a query has increased execution time 10 times

Question

On a CentOS 7.6 server (Intel Core i7-6700, 64 GB DDR4 RAM, RAID1 SSD) I run a backend written in PL/pgSQL and Java for a mobile and desktop word game with the following Linux packages:

postgresql10-server-10.6-1PGDG.rhel7.x86_64
pgbouncer-1.9.0-1.rhel7.x86_64
postgresql-jdbc-42.2.5-1.rhel7.noarch

Here are the only modified settings in postgresql.conf:

max_connections = 120
work_mem = 8MB
maintenance_work_mem = 128MB

The 90% of the backend source code are JSON-emitting stored functions and there is one function which is the main core of the game and is a SELECT query over 7 tables.

It is called for every Websocket-connected client and delivers a JSON list of active games for the player.

Until recently the query needed 1-2 seconds for completion, but after I have added a LEFT JOIN with the following table, the query takes 7-10 seconds for completion and makes the game unpleasant to play:

# \d words_geoip;
                 Table "public.words_geoip"
 Column |       Type       | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 block  | inet             |           | not null | 
 lat    | double precision |           |          | 
 lng    | double precision |           |          | 
Indexes:
    "words_geoip_pkey" PRIMARY KEY, btree (block)

# select * from words_geoip order by random() limit 5;
       block       |   lat    |    lng    
-------------------+----------+-----------
 217.72.221.128/25 |    48.26 |    11.434
 71.183.37.0/24    |  40.9357 |  -72.9809
 190.174.132.0/22  | -34.6033 |  -58.3817
 24.72.74.128/25   |  50.5061 | -104.6752
 73.155.238.0/23   |  29.5075 |  -95.0895
(5 rows)

# select count(*) from words_geoip;
  count  
---------
 3073410
(1 row)

Here is the SELECT query (I have removed the stored function and ROW_TO_JSON around it for better readability and have commented the 3 new lines out):

SELECT 
    g.gid,
    EXTRACT(EPOCH FROM g.created)::int AS created,
    EXTRACT(EPOCH FROM g.finished)::int AS finished,
    g.letters AS letters,
    g.values AS values,
    g.bid AS bid,
    CARDINALITY(g.pile) AS pilelen,     
    m.tiles AS tiles,
    m.score AS score,
    CASE WHEN g.player1 = 5 THEN g.player1 ELSE g.player2 END AS player1,
    CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END AS player2,
    CASE WHEN g.player1 = 5 THEN g.score1  ELSE g.score2  END AS score1,
    CASE WHEN g.player1 = 5 THEN g.score2  ELSE g.score1  END AS score2,
    CASE WHEN g.player1 = 5 THEN g.state1  ELSE g.state2  END AS state1,
    CASE WHEN g.player1 = 5 THEN g.hint1   ELSE g.hint2   END AS hint1,
    CASE WHEN g.player1 = 5 THEN g.chat1   ELSE g.chat2   END AS chat1,
    u1.elo AS elo1,
    u2.elo AS elo2,

    -- i2.lat AS lat2,
    -- i2.lng AS lng2, 

    s1.given AS given1,
    s2.given AS given2,
    s1.photo AS photo1,
    s2.photo AS photo2,
    EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played1 ELSE g.played2 END)::int AS played1,
    EXTRACT(EPOCH FROM CASE WHEN g.player1 = 5 THEN g.played2 ELSE g.played1 END)::int AS played2,
    ARRAY_TO_STRING(CASE WHEN g.player1 = 5 THEN g.hand1 ELSE g.hand2 END, '') AS hand1,
    CASE 
            WHEN g.finished IS NOT NULL THEN NULL
            WHEN g.player2 IS NULL THEN NULL
            WHEN g.player1 = 5 AND g.played1 < g.played2 
                    THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
            WHEN g.player2 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
                    THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
            ELSE NULL
    END AS left1,
    CASE 
            WHEN g.finished IS NOT NULL THEN NULL
            WHEN g.player2 IS NULL THEN NULL
            WHEN g.player1 = 5 AND (g.played2 IS NULL OR g.played2 < g.played1)
                    THEN EXTRACT(EPOCH FROM g.played1 + interval '24 hour' - CURRENT_TIMESTAMP)::int
            WHEN g.player2 = 5 AND g.played1 < g.played2 
                    THEN EXTRACT(EPOCH FROM g.played2 + interval '24 hour' - CURRENT_TIMESTAMP)::int
            ELSE NULL
    END AS left2
FROM words_games g 
LEFT JOIN words_moves m ON m.gid = g.gid
        AND NOT EXISTS (SELECT 1
                FROM words_moves m2 
                WHERE m2.gid = m.gid
                AND m2.played > m.played)
LEFT JOIN words_users u1 ON u1.uid = 5
LEFT JOIN words_users u2 ON u2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END)

-- LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = 5 THEN u2.ip ELSE u1.ip END) << i2.block

LEFT JOIN words_social s1 ON s1.uid = 5
        AND NOT EXISTS (SELECT 1
                FROM words_social s 
                WHERE s1.uid = s.uid
                AND s.stamp > s1.stamp)
LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = 5 THEN g.player2 ELSE g.player1 END)
        AND NOT EXISTS (SELECT 1
                FROM words_social s 
                WHERE s2.uid = s.uid
                AND s.stamp > s2.stamp)
WHERE 5 IN (g.player1, g.player2)
AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1 day');

Here the above query with EXPLAIN ANALYZE prepended:

                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=117019.66..323100.50 rows=240 width=1414) (actual time=390.626..1518.867 rows=9 loops=1)
   Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid)
   ->  Nested Loop Left Join  (cost=115853.58..321900.39 rows=240 width=1760) (actual time=384.751..1512.913 rows=9 loops=1)
         ->  Nested Loop Left Join  (cost=115853.58..321042.49 rows=60 width=1672) (actual time=382.754..1510.864 rows=9 loops=1)
               ->  Nested Loop Left Join  (cost=115853.29..321033.43 rows=60 width=1668) (actual time=382.749..1510.850 rows=9 loops=1)
                     ->  Hash Right Join  (cost=115853.00..320535.13 rows=60 width=1664) (actual time=382.736..1510.753 rows=9 loops=1)
                           Hash Cond: (m.gid = g.gid)
                           ->  Hash Anti Join  (cost=103356.07..305406.36 rows=1002521 width=212) (actual time=358.638..1488.564 rows=49721 loops=1)
                                 Hash Cond: (m.gid = m2.gid)
                                 Join Filter: (m2.played > m.played)
                                 Rows Removed by Join Filter: 3841937
                                 ->  Seq Scan on words_moves m  (cost=0.00..77215.81 rows=1503781 width=220) (actual time=0.003..233.670 rows=1499874 loops=1)
                                 ->  Hash  (cost=77215.81..77215.81 rows=1503781 width=12) (actual time=358.205..358.205 rows=1499874 loops=1)
                                       Buckets: 262144  Batches: 16  Memory Usage: 6706kB
                                       ->  Seq Scan on words_moves m2  (cost=0.00..77215.81 rows=1503781 width=12) (actual time=0.003..204.397 rows=1499874 loops=1)
                           ->  Hash  (cost=12496.89..12496.89 rows=3 width=1456) (actual time=19.316..19.316 rows=9 loops=1)
                                 Buckets: 1024  Batches: 1  Memory Usage: 22kB
                                 ->  Seq Scan on words_games g  (cost=0.00..12496.89 rows=3 width=1456) (actual time=15.134..19.304 rows=9 loops=1)
                                       Filter: (((5 = player1) OR (5 = player2)) AND ((finished IS NULL) OR (finished > (CURRENT_TIMESTAMP - '1 day'::interval))))
                                       Rows Removed by Filter: 49730
                     ->  Index Scan using words_users_pkey on words_users u2  (cost=0.29..8.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=9)
                           Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
               ->  Materialize  (cost=0.29..8.31 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=9)
                     ->  Index Scan using words_users_pkey on words_users u1  (cost=0.29..8.30 rows=1 width=4) (actual time=0.003..0.004 rows=1 loops=1)
                           Index Cond: (uid = 5)
         ->  Materialize  (cost=0.00..854.91 rows=4 width=88) (actual time=0.222..0.227 rows=1 loops=9)
               ->  Nested Loop Anti Join  (cost=0.00..854.89 rows=4 width=88) (actual time=1.995..2.039 rows=1 loops=1)
                     Join Filter: ((s.stamp > s1.stamp) AND (s1.uid = s.uid))
                     Rows Removed by Join Filter: 11
                     ->  Seq Scan on words_social s1  (cost=0.00..427.20 rows=6 width=96) (actual time=0.112..0.990 rows=6 loops=1)
                           Filter: (uid = 5)
                           Rows Removed by Filter: 10652
                     ->  Materialize  (cost=0.00..427.23 rows=6 width=8) (actual time=0.015..0.174 rows=3 loops=6)
                           ->  Seq Scan on words_social s  (cost=0.00..427.20 rows=6 width=8) (actual time=0.092..1.043 rows=6 loops=1)
                                 Filter: (uid = 5)
                                 Rows Removed by Filter: 10652
   ->  Hash  (cost=1077.29..1077.29 rows=7104 width=92) (actual time=5.855..5.855 rows=10552 loops=1)
         Buckets: 16384 (originally 8192)  Batches: 1 (originally 1)  Memory Usage: 1419kB
         ->  Hash Anti Join  (cost=533.76..1077.29 rows=7104 width=92) (actual time=1.918..4.456 rows=10552 loops=1)
               Hash Cond: (s2.uid = s_1.uid)
               Join Filter: (s_1.stamp > s2.stamp)
               Rows Removed by Join Filter: 10709
               ->  Seq Scan on words_social s2  (cost=0.00..400.56 rows=10656 width=96) (actual time=0.002..0.576 rows=10658 loops=1)
               ->  Hash  (cost=400.56..400.56 rows=10656 width=8) (actual time=1.906..1.906 rows=10658 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 545kB
                     ->  Seq Scan on words_social s_1  (cost=0.00..400.56 rows=10656 width=8) (actual time=0.001..1.098 rows=10658 loops=1)
 Planning time: 0.409 ms
 Execution time: 1518.996 ms
(48 rows)

And here the same query, but I have removed the -- characters, so that LEFT JOIN against the words_geoip table has been added:

                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=117019.66..5257508.21 rows=3688092 width=1430) (actual time=1383.205..5279.226 rows=9 loops=1)
   Hash Cond: (CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END = s2.uid)
   ->  Nested Loop Left Join  (cost=115853.58..4733610.67 rows=3688092 width=1776) (actual time=1376.856..5272.785 rows=9 loops=1)
         ->  Nested Loop Left Join  (cost=115853.58..4686654.61 rows=922023 width=1688) (actual time=1374.768..5270.644 rows=9 loops=1)
               Join Filter: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END << i2.block)
               Rows Removed by Join Filter: 27660682
               ->  Nested Loop Left Join  (cost=115853.58..321042.49 rows=60 width=1686) (actual time=618.110..1531.740 rows=9 loops=1)
                     ->  Nested Loop Left Join  (cost=115853.29..320544.19 rows=60 width=1675) (actual time=618.105..1531.642 rows=9 loops=1)
                           ->  Hash Right Join  (cost=115853.00..320535.13 rows=60 width=1664) (actual time=618.093..1531.613 rows=9 loops=1)
                                 Hash Cond: (m.gid = g.gid)
                                 ->  Hash Anti Join  (cost=103356.07..305406.36 rows=1002521 width=212) (actual time=363.819..1510.167 rows=49721 loops=1)
                                       Hash Cond: (m.gid = m2.gid)
                                       Join Filter: (m2.played > m.played)
                                       Rows Removed by Join Filter: 4074631
                                       ->  Seq Scan on words_moves m  (cost=0.00..77215.81 rows=1503781 width=220) (actual time=0.007..235.666 rows=1499877 loops=1)
                                       ->  Hash  (cost=77215.81..77215.81 rows=1503781 width=12) (actual time=363.537..363.537 rows=1499877 loops=1)
                                             Buckets: 262144  Batches: 16  Memory Usage: 6706kB
                                             ->  Seq Scan on words_moves m2  (cost=0.00..77215.81 rows=1503781 width=12) (actual time=0.002..204.827 rows=1499877 loops=1)
                                 ->  Hash  (cost=12496.89..12496.89 rows=3 width=1456) (actual time=18.521..18.521 rows=9 loops=1)
                                       Buckets: 1024  Batches: 1  Memory Usage: 22kB
                                       ->  Seq Scan on words_games g  (cost=0.00..12496.89 rows=3 width=1456) (actual time=12.417..18.511 rows=9 loops=1)
                                             Filter: (((5 = player1) OR (5 = player2)) AND ((finished IS NULL) OR (finished > (CURRENT_TIMESTAMP - '1 day'::interval))))
                                             Rows Removed by Filter: 49730
                           ->  Materialize  (cost=0.29..8.31 rows=1 width=11) (actual time=0.001..0.002 rows=1 loops=9)
                                 ->  Index Scan using words_users_pkey on words_users u1  (cost=0.29..8.30 rows=1 width=11) (actual time=0.008..0.010 rows=1 loops=1)
                                       Index Cond: (uid = 5)
                     ->  Index Scan using words_users_pkey on words_users u2  (cost=0.29..8.30 rows=1 width=15) (actual time=0.008..0.008 rows=1 loops=9)
                           Index Cond: (uid = CASE WHEN (g.player1 = 5) THEN g.player2 ELSE g.player1 END)
               ->  Materialize  (cost=0.00..83684.15 rows=3073410 width=23) (actual time=0.004..228.492 rows=3073410 loops=9)
                     ->  Seq Scan on words_geoip i2  (cost=0.00..50308.10 rows=3073410 width=23) (actual time=0.008..248.647 rows=3073410 loops=1)
         ->  Materialize  (cost=0.00..854.91 rows=4 width=88) (actual time=0.232..0.237 rows=1 loops=9)
               ->  Nested Loop Anti Join  (cost=0.00..854.89 rows=4 width=88) (actual time=2.085..2.129 rows=1 loops=1)
                     Join Filter: ((s.stamp > s1.stamp) AND (s1.uid = s.uid))
                     Rows Removed by Join Filter: 11
                     ->  Seq Scan on words_social s1  (cost=0.00..427.20 rows=6 width=96) (actual time=0.114..1.029 rows=6 loops=1)
                           Filter: (uid = 5)
                           Rows Removed by Filter: 10652
                     ->  Materialize  (cost=0.00..427.23 rows=6 width=8) (actual time=0.015..0.183 rows=3 loops=6)
                           ->  Seq Scan on words_social s  (cost=0.00..427.20 rows=6 width=8) (actual time=0.087..1.094 rows=6 loops=1)
                                 Filter: (uid = 5)
                                 Rows Removed by Filter: 10652
   ->  Hash  (cost=1077.29..1077.29 rows=7104 width=92) (actual time=6.326..6.326 rows=10552 loops=1)
         Buckets: 16384 (originally 8192)  Batches: 1 (originally 1)  Memory Usage: 1419kB
         ->  Hash Anti Join  (cost=533.76..1077.29 rows=7104 width=92) (actual time=1.966..4.600 rows=10552 loops=1)
               Hash Cond: (s2.uid = s_1.uid)
               Join Filter: (s_1.stamp > s2.stamp)
               Rows Removed by Join Filter: 10709
               ->  Seq Scan on words_social s2  (cost=0.00..400.56 rows=10656 width=96) (actual time=0.002..0.622 rows=10658 loops=1)
               ->  Hash  (cost=400.56..400.56 rows=10656 width=8) (actual time=1.952..1.952 rows=10658 loops=1)
                     Buckets: 16384  Batches: 1  Memory Usage: 545kB
                     ->  Seq Scan on words_social s_1  (cost=0.00..400.56 rows=10656 width=8) (actual time=0.001..1.130 rows=10658 loops=1)
 Planning time: 0.511 ms
 Execution time: 5292.006 ms
(53 rows)

I am probably missing something minor, but I am not experienced with tuning SQL queries plus the whole query is quite extensive. Please help

UPDATE:

Below are the other 4 tables used in the query -

# \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_reason_index" btree (reason)
    "words_games_state1_index" btree (state1)
    "words_games_state2_index" btree (state2)
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

# \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

# \d words_social
                Table "public.words_social"
 Column |       Type       | Collation | Nullable | Default 
--------+------------------+-----------+----------+---------
 sid    | text             |           | not null | 
 social | integer          |           | not null | 
 given  | text             |           | not null | 
 family | text             |           |          | 
 photo  | text             |           |          | 
 lat    | double precision |           |          | 
 lng    | double precision |           |          | 
 stamp  | integer          |           | not null | 
 uid    | integer          |           | not null | 
Indexes:
    "words_social_pkey" PRIMARY KEY, btree (sid, social)
Check constraints:
    "words_social_given_check" CHECK (given ~ '\S'::text)
    "words_social_photo_check" CHECK (photo ~* '^https?://...'::text)
    "words_social_social_check" CHECK (0 < social AND social <= 64)
Foreign-key constraints:
    "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE
Referenced by:
    TABLE "words_payments" CONSTRAINT "words_payments_sid_fkey" FOREIGN KEY (sid, social) REFERENCES words_social(sid, social) ON DELETE CASCADE

# \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)
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

UPDATE 2:

There are 6 times "Seq Scan" reported, I have to get rid of that by adding indices, correct?

So I have added already 2 and the time has improved:

CREATE INDEX words_moves_gid_index ON words_moves(gid);
CREATE INDEX words_moves_uid_index ON words_social(uid);

But unfortunately I still don't understand, which INDEX to add for words_geoip, because there is already one (the PK)?

Nitpicking: an increase from 1518ms to 5292ms is an increase of approx. 3.5 times, not 10 times. — user1822, Commented Jan 7, 2019 at 9:11
In the question I have pasted my own data for uid=5, while there are players playing much more games - and I believe I've seen 10 times increase there — Alexander Farber, Commented Jan 7, 2019 at 10:14

Alexander Farber · Accepted Answer · 2019-01-13 09:50:40Z

With the friendly help by Mr. Andrew Gierth at the pgsql-general mailing list and the users xocolatl, azeem and aditsu at IRC #postgresql the query has been optimized (7-10 seconds -> 0.0003 second) by adding the following indices:

CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));
CREATE INDEX ON words_moves (gid, played DESC);
CREATE INDEX ON words_social (uid, stamp DESC);
CREATE INDEX ON words_geoip USING SPGIST (block);

and switching to LEFT JOIN LATERAL for finding the most recent records in words_moves and words_social tables:

SELECT
...
-- find move record with the most recent timestamp
LEFT JOIN LATERAL (SELECT * FROM words_moves m WHERE m.gid = g.gid ORDER BY m.played DESC LIMIT 1) AS m ON TRUE
LEFT JOIN words_geoip i2 ON (CASE WHEN g.player1 = in_uid THEN u2.ip ELSE u1.ip END) <<= i2.block
LEFT JOIN LATERAL (SELECT * FROM words_social AS s1 WHERE s1.uid = u1.uid ORDER BY s1.stamp DESC LIMIT 1) AS s1 ON TRUE
LEFT JOIN LATERAL (SELECT * FROM words_social AS s2 WHERE s2.uid = u2.uid ORDER BY s2.stamp DESC LIMIT 1) AS s2 ON TRUE
...
WHERE in_uid IN (g.player1, g.player2)
AND COALESCE(g.finished, 'INFINITY') > (CURRENT_TIMESTAMP - INTERVAL '1 day')

Also I have increased the following parameters in postgresql.conf -

from_collapse_limit = 24
join_collapse_limit = 24

Finally, here the links to the full query and ANALYZE EXPLAIN output.

Stack Exchange Network

Adding LEFT JOIN to a query has increased execution time 10 times

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
postgresql
join
performance
postgresql-10
postgresql-performance
or ask your own question.

Hot Network Questions

Adding LEFT JOIN to a query has increased execution time 10 times

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged postgresqljoinperformancepostgresql-10postgresql-performance or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
join
performance
postgresql-10
postgresql-performance
or ask your own question.