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

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