Alexander Farber wrote > Good evening, thank you for the useful hints! > > With the further help of the IRC folks the query has been optimized (7-10 > seconds -> 0.3 second) by adding the following indices: > > CREATE INDEX ON words_games (player1, COALESCE(finished, 'INFINITY')); > CREATE INDEX ON words_games (player2, COALESCE(finished, 'INFINITY')); > CREATE INDEX ON words_moves (gid, played DESC); > CREATE INDEX ON words_social (uid, stamp DESC); > CREATE INDEX ON words_geoip USING SPGIST (block); > > and switching to LEFT JOIN LATERAL for finding the most recent records in > words_moves and words_social tables: > > [...] > > Planning time: 0.587 ms > Execution time: 0.367 ms > (36 rows) > > I was told that it still could be improved (by rearranging WHERE clauses?) > > Regards > Alex Hi Alexander, It seems that you have done a very nice tuning exercise with this query, that finishes now in less than 1 ms !!! and I have learned about LEFT JOIN LATERAL syntax too ! As you didn't spoke about DML activity ... May I suggest you to take some time to monitor the application before to continue optimizing this query ? Take time to check that: - the result is ok, - performances are stable, - there is no regression on other queries, - inserts,updates, deletes, copy are still working fast, - size of added objects are coherent and stable, - query complexity stay manageable, - there is no other application part to optimize, - ... Regards PAscal -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
