Comparing Several Columns at Once With a Row Value Comparison
SQL lets you compare a group of columns as one value, writing (created_at, id) > (:last_time, :last_id), which means exactly what sorting by those columns in that order means. Written the long way with AND and OR it is easy to get wrong and hard for the engine to match to an index. The short form keeps the comparison in the same shape as a multi-column index, so the index can find the starting point directly.
Questions this Concept answers
Why is the row-value form preferred over `created_at > :t OR (created_at = :t AND id > :i)`?
J
jeremy
Text
Advanced PostgreSQL Pagination: A Guide to Conditional ORDER BY and Keyset Cursors
This page shows how to page through a large table without OFFSET, which gets slower the deeper you go. Instead you remember the last row you showed and ask for rows after it, comparing several column…