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

Maxim Boguk <mboguk(at)masterhost(dot)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