From: | Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> |
---|---|
To: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Adding LEFT JOIN to a query has increased execution time 10 times |
Date: | 2019-01-06 23:00:36 |
Message-ID: | 87muodqwcf.fsf@news-spur.riddles.org.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>>>>> "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)
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-01-07 03:21:25 | Re: Is there something wrong with my test case? |
Previous Message | legrand legrand | 2019-01-06 20:37:45 | Re: Adding LEFT JOIN to a query has increased execution time 10 times |