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

>>>>> "Alexander" == Alexander Farber <alexander(dot)farber(at)gmail(dot)com> writes:

Alexander> Good evening, thank you for the useful hints!

Alexander> With the further help of the IRC folks the query has been
Alexander> optimized (7-10 seconds -> 0.3 second)

0.3 MILLIseconds, actually.

(You chanced not to catch me around on IRC, but I see that didn't
matter.)

Alexander> by adding the following indices:

Alexander> CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY'));
Alexander> CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY'));

Alexander> CREATE INDEX ON words_moves (gid, played DESC);
Alexander> CREATE INDEX ON words_social (uid, stamp DESC);

I'm not a big fan of using DESC on indexes; it's almost never needed,
because any btree index can be scanned in reverse. (You only actually
need it if you're mixing ASC/DESC orderings in an ORDER BY and want an
index that matches it.)

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

Alexander> from_collapse_limit = 24
Alexander> join_collapse_limit = 24

Ironically, I think these settings don't affect the query now since
removing the EXISTS conditions (which count as joins) means there are
now less than 8 joined tables. But keeping them high is probably a good
idea so that you don't get problems if you ever add another join or two.

Alexander> Now the whole query looks as following and the EXPLAIN
Alexander> output pasted is below -

Just for future reference, when you include explain output in your email
(which you should, for the benefit of the archives - paste sites and
explain.depesz.com have limited lifetimes), it's best if you can make
sure your email client doesn't word-wrap them into near-unreadability.

Alexander> I was told that it still could be improved (by rearranging
Alexander> WHERE clauses?)

Maybe, but once you're down to sub-millisecond execution times, further
optimization is usually only worthwhile for very heavily executed
queries.

--
Andrew (irc:RhodiumToad)