Re: Postgresql 13 query engine regression

On Tue, 11 May 2021 at 11:34, Jonathan Chen <jonc(at)chen(dot)org(dot)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