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

Oh ok, so it is not as simple as eliminating all "Seq Scan" occurrences...

Thank you for replying Andrew -

On Sat, Jan 5, 2019 at 9:18 PM Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

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.
>
>
I will try to digest your information and to follow up... Thanks again

For IRC I am unfortunately too tired right now (evening in Germany)

Regards
Alex