Re: Indexes on NULL's and order by ... limit N queries

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Indexes on NULL's and order by ... limit N queries
Дата
Msg-id 1390.1228166934@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: Indexes on NULL's and order by ... limit N queries  (Maxim Boguk <mboguk@masterhost.ru>)
Список pgsql-general
Maxim Boguk <mboguk@masterhost.ru> writes:
> But why? NULL's have some special representation in index which don't work same as normal values?

In general, NULLs don't work the same as normal values, no.

The reason this particular query isn't working as you are expecting is
that "foo IS NULL" isn't seen as an ordering constraint by the planner's
pathkey machinery, and so the query doesn't appear to match the index
order.  You could work around it by explicitly specifying a matching
ordering:

    SELECT * from cluster_weight where cluster_weight.rubric_id IS NULL
    ORDER BY rubric_id, pos LIMIT 5;
                 ^^^^^^^^^^


            regards, tom lane

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

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Nested Loop Left Join always shows rows=1
Следующее
От: Greg Smith
Дата:
Сообщение: Re: Monty on MySQL 5.1: "Oops, we did it again"