Re: Query optimiser is not using 'not null' constraint when 'order bynulls last' clause is used

Поиск
Список
Период
Сортировка
От Nandakumar M
Тема Re: Query optimiser is not using 'not null' constraint when 'order bynulls last' clause is used
Дата
Msg-id CANcFUu4YyAFYUYw2P=UGTJNo4TKFqSMOjnx5pi1apxiuTqeR+g@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Query optimiser is not using 'not null' constraint when 'orderby nulls last' clause is used  (Laurenz Albe <laurenz.albe@cybertec.at>)
Ответы Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Дерево обсуждения
Hi,

On 2 Feb 2018 15:06, "Laurenz Albe" <laurenz.albe@cybertec.at> wrote:

>In the above case, the optimizer does >not know that it will get the rows
>in the correct order: indexes are >sorted ASC NULLS LAST by default,
>so a backwards index scan will >produce the results NULLS FIRST,
>which is the default for ORDER BY ... >DESC.

The order by column has a not null constraint on it and so nulls last or first shouldn't make any difference.


>If you want the nulls last, PostgreSQL >has to retrieve *all* the rows and sort
>them rather than using the first 25 >results it gets by scanning then >indexes.

>To have the above query perform >fast, add additional indexes with either
>ASC NULLS FIRST or DESC NULLS >LAST for all used keys.

For now this is exactly what I have done. But it is in effect a duplicate index on a PK column and I would be happy not to create it in the first place.

Regards
Nanda

В списке pgsql-performance по дате отправления:

Предыдущее
От: Vitaliy Garnashevich
Дата:
Сообщение: Re: effective_io_concurrency on EBS/gp2
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Query optimiser is not using 'not null' constraint when 'order by nulls last' clause is used