>>>>> "Alexander" == Alexander Farber <alexander.farber@gmail.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)