PostgreSQL 13: happy pagination WITH TIES

Last week, two articles were published at once (from Hubert ‘depesz’ Lubaczewski and the author of the patch itself Alvaro herrera), dedicated to the support of the option implemented in the upcoming version of PostgreSQL 13 WITH TIES from SQL standard: 2008:

OFFSET start { ROW | ROWS } FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } { ONLY | WITH TIES }

What is it, and how does it eliminate the paging implementation problems I discussed in PostgreSQL Antipatterns: Registry Navigation?

Let me remind you that in that article we stopped at the point that if we have a sign like this:

CREATE TABLE events(
  id
    serial
      PRIMARY KEY
, ts
    timestamp
, data
    json
);

INSERT INTO events(ts)
SELECT
  now() - ((random() * 1e8) || ' sec')::interval
FROM
  generate_series(1, 1e6);

… then to organize chronological paging on it (by ts DESC) it is most efficient to use the following index:

CREATE INDEX ON events(ts DESC);

… and this query model:

SELECT
  ...
WHERE
  ts < $1 AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < $1
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

Good old subquery

Let’s take a look at the plan of such a query if we want to get the next segment from the beginning of this year:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  *
FROM
  events
WHERE
  ts < '2020-01-01'::timestamp AND
  ts >= coalesce((
    SELECT
      ts
    FROM
      events
    WHERE
      ts < '2020-01-01'::timestamp
    ORDER BY
      ts DESC
    LIMIT 1 OFFSET 25
  ), '-infinity')
ORDER BY
  ts DESC;

[посмотреть на explain.tensor.ru]

Why is there a nested query here? Exactly so as not to have the problems with “jumping” described in that article the same sort key values between the requested segments:

Trying WITH TIES “to the teeth”

But this is exactly what functionality is needed for WITH TIES – to select all records with the same value at once boundary key!

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  *
FROM
  events
WHERE
  ts < '2020-01-01'::timestamp
ORDER BY
  ts DESC
FETCH FIRST 26 ROWS WITH TIES;

[посмотреть на explain.tensor.ru]

The request looks much simpler, almost 2 times faster, and in just one Index Scan – excellent result!

Please note that although we “ordered” only 26 records, Index Scan extracted one more – just to make sure that the “next” is no longer suitable for us.