Re: tuple compare involving NULL

On Wed, Aug 13, 2014 at 10:00 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Albe Laurenz <laurenz.albe@wien.gv.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.

Right -- the current behavior (row-wise compairson) was implemented
precisely because it matched multi column index behaviors so that easy
and cheap paging was possible without using cursors.  This is a very
common way of accessing data for systems converted from the older ISAM
style of record navigation.  So OP's gripe is invalid on it's face,
the server does exactly what he wants it to do.  IMO, it's very
underutilized technique.

If you *had* (say, if using a database without row wise comparison
support) to to expand to cascading boolean logic, you'd want to write
it as:

SELECT
  a1 >= b1
  AND (a1 > b1 OR a2 >= b2)
  AND (a1 >  b1 OR a2 > b2 OR a3 > b3)
ORDER BY a1,a2,a3  LIMIT k;

By having the AND logic on the outside, there is at least a chance of
getting use of an index on 'a1'.

This by the way has nothing to do with 'keys'.  It's just a mechanic
for fast paging through data using an index.

merlin