Re: Indexes on NULL's and order by ... limit N queries - Mailing list 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