On Tue, 11 May 2021 at 11:34, Jonathan Chen <jonc@chen.org.nz> wrote:
> PG13 LIMIT 1 : https://explain.depesz.com/s/GFki
> PG13 no LIMIT: https://explain.depesz.com/s/U4vR
> PG12 LIMIT 1 : https://explain.depesz.com/s/JAp4
The difference is coming from the fact that PostgreSQL 13 has
incremental sort and can use the account_move_date_index to provide
partially sorted input for the ORDER BY clause. PG12 didn't have this
feature, so there was no index that could help getting pre-sorted
input to the ORDER BY.
You'd get along much better if you got rid of the
account_move_date_index index and replaced it with:
CREATE INDEX account_move_date_id_index ON account_move (date,id);
Or instead or removing account_move_date_index, you could add an index such as:
CREATE INDEX account_move_journal_id_date_index (journal_id, date, id);
That should allow the query to run a bit more quickly. However, if the
first of the two is fast enough then it might be better to not add too
many extra indexes.
David