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 WITH TIESfrom the SQL: 2008 standard implemented in the upcoming version of PostgreSQL 13 :

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 by it (by ts DESC) it is most effective 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 look at the plan for 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;

[look at explain.tensor.ru]



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

Trying WITH TIES "to the teeth"

But this is exactly what the functionality WITH TIESis for - to select all records with the same value of the boundary key at once !

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

[look at explain.tensor.ru] The



query looks much simpler, almost 2 times faster, and in just one Index Scan- an excellent result!



Please note that even though we "ordered" only 26 records, I Index Scanextracted one more - just to make sure that the "next" one no longer suits us.







Well, we are waiting for the official release of PostgreSQL 13, which is scheduled for tomorrow.