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