Re: Adding LEFT JOIN to a query has increased execution time 10 times - Mailing list pgsql-general

That seems slow in itself, even before adding the extra join - the
explain suggests that you're both short on indexes and you're getting
pretty bad plans, possibly due to exceeding join_collapse_limit.

(You might try increasing that in your config, along with
from_collapse_limit; the default values are a legacy of the days when
CPUs were much slower and planning time more of an issue.)

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

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

And here's yet another missing index, resulting in your query having to
process and discard 27 million rows in the course of generating a result
of only 9 rows:

  Join Filter: (CASE WHEN (g.player1 = 5) THEN u2.ip ELSE u1.ip END << i2.block)
  Rows Removed by Join Filter: 27660682

(you probably wanted <<= rather than << as that comparison, if there's
any chance your geoip table might have entries for single IPs)

Fortunately, this being pg10, you can use either of these indexes:

CREATE INDEX ON words_geoip USING gist (block inet_ops);

or

CREATE INDEX ON words_geoip USING spgist (block);

As for the rest of the query, here are places you could probably
work on:

 AF>     LEFT JOIN words_moves m ON m.gid = g.gid
 AF>             AND NOT EXISTS (SELECT 1
 AF>                     FROM words_moves m2
 AF>                     WHERE m2.gid = m.gid
 AF>                     AND m2.played > m.played)

Whar you're asking for here is that the words_moves row that you're
joining not have a matching row with a larger "played" value. You can do
this far more efficiently with a lateral join, given the right index.

 AF>     LEFT JOIN words_social s1 ON s1.uid = 5
 AF>             AND NOT EXISTS (SELECT 1
 AF>                     FROM words_social s
 AF>                     WHERE s1.uid = s.uid
 AF>                     AND s.stamp > s1.stamp)
 AF>     LEFT JOIN words_social s2 ON s2.uid = (CASE WHEN g.player1 = 5 THEN
 AF> g.player2 ELSE g.player1 END)
 AF>             AND NOT EXISTS (SELECT 1
 AF>                     FROM words_social s
 AF>                     WHERE s2.uid = s.uid
 AF>                     AND s.stamp > s2.stamp)

Similar considerations apply to both of the above.

 AF>     WHERE 5 IN (g.player1, g.player2)
 AF>     AND (g.finished IS NULL OR g.finished > CURRENT_TIMESTAMP - INTERVAL '1
 AF> day');

This WHERE clause could be written as

  WHERE 5 IN (g.player1, g.player2)
    AND coalesce(g.finished,'infinity') > (current_timestamp - interval '1 day')

and you could then create the following indexes,

CREATE INDEX ON words_games (player1, coalesce(finished,'infinity'));
CREATE INDEX ON words_games (player2, coalesce(finished,'infinity'));

which should get you a BitmapOr plan for that condition.

 AF> I have also asked my question at [dba.stack]

If you ask questions like this on the IRC channel (#postgresql on
chat.freenode.net - see http://freenode.net for info or web-based client
access), you can usually get feedback in real time (I rarely answer
performance questions in email because getting responses just takes too
long). You may have to be patient.