Re: Postgresql 13 query engine regression

On Tue, 11 May 2021 at 12:49, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> 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.

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(at)chen(dot)org(dot)nz>