Re: tuple compare involving NULL

Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> writes:
> Tobias Florek wrote:
>> maybe a little more context might be helpful. i am trying to have
>> reasonable efficient paging. the query i am building looks like
>>
>> select t.*
>> from table t,
>> (select a, b from table where id = ) q
>> where (q.a, t.b, t.id) > (t.a, q.b, q.id)
>> order by t.a desc, t.b asc, t.id asc
>> limit 10;
>>
>> where t is a table with column id (primary key, serial), a and b.
>>
>> that works fine and efficient (given an index (a,b) on t) without NULLs,
>> but (predictably) not in the presence of NULLs.
>>
>> i would certainly like to handle that better, but i don't have any ideas
>> besides manually expanding the tuple comparison.

> That would probably make it harder to use a multicolumn index correctly.

Yeah, if you change the WHERE condition at all, it will stop matching
the multicolumn index.

regards, tom lane