On Tue, 11 May 2021 at 12:49, David Rowley <dgrowleyml@xxxxxxxxx> wrote: > > On Tue, 11 May 2021 at 11:34, Jonathan Chen <jonc@xxxxxxxxxxx> 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. If I drop the index account_move_date_index, the speed of the query improves to 0.035s. Adding the replacement index on (date, id) or (journal_id, date, id) increases the query to > 47s again. This gives me a bit of something to play with. Thanks for the insight. -- Jonathan Chen <jonc@xxxxxxxxxxx>
