PostgreSQL 13: ترقيم صفحات سعيد مع روابط

تم نشر مقالتين في وقت واحد (من Hubert 'depesz' Lubaczewski ومؤلف التصحيح نفسه ، Alvaro Herrera ) ، تم تخصيصهما لدعم الخيار WITH TIESمن معيار SQL: 2008 المطبق في الإصدار القادم من PostgreSQL 13 :

OFFSET start { ROW | ROWS }

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

ما هو وكيف يلغي مشاكل تنفيذ الترحيل التي ناقشتها في PostgreSQL Antipatterns: Registry Navigation ؟







دعني أذكرك أنه في هذا المقال توقفنا عند النقطة التي إذا كانت لدينا علامة مثل هذه:

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);

... ثم لتنظيم الترحيل الزمني بواسطته (بواسطة ts DESC) يكون استخدام الفهرس التالي أكثر فاعلية:

CREATE INDEX ON events(ts DESC);

... ونموذج الاستعلام هذا:

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;

طلب فرعي قديم جيد

لنلقِ نظرة على خطة مثل هذا الاستعلام ، إذا أردنا الحصول على المقطع التالي من بداية هذا العام:

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;

[انظر إلىشرح.tensor.ru]



لماذا يوجد استعلام متداخل هنا؟ بالضبط حتى لا تحدث المشاكل الموضحة في تلك المقالة مع "القفز" بنفس قيم مفتاح الفرز بين الأجزاء المطلوبة:

محاولة ربطات "بالأسنان"

ولكن هذا هو بالضبط ما نحتاجه من وظائف WITH TIES- لتحديد كل السجلات بنفس قيمة مفتاح الحدود مرة واحدة !

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

[انظر إلىشرح.tensor.ru]



يبدو الاستعلام أبسط بكثير ، أسرع مرتين تقريبًا ، وفي واحد فقط Index Scan- نتيجة ممتازة!



يرجى ملاحظة أنه على الرغم من أننا "طلبنا" 26 سجلاً فقط ، فقد Index Scanاستخرجت واحدًا آخر - فقط للتأكد من أن السجل "التالي" لم يعد يناسبنا.







حسنًا ، نحن ننتظر الإصدار الرسمي من PostgreSQL 13 ، والمقرر له غدًا.