PostgreSQL Antipatterns: تنقل التسجيل

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

CREATE INDEX ON events(ts DESC);

# 0 "أنا مجرم في أمي"

cur.execute("SELECT * FROM events;")
rows = cur.fetchall();
rows.sort(key=lambda row: row.ts, reverse=True);
limit = 26
print(rows[offset:offset+limit]);

رقم 1. عوض

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 -   , $1 -  

26? . , 25 , 1, , - .

, «» , . PostgreSQL , , — .

توسيع المؤشر

CREATE UNIQUE INDEX ON events(ts DESC, id DESC);

SELECT
  ...
ORDER BY
  ts DESC, id DESC
LIMIT 26 OFFSET $1;

# 2 الانتقال إلى "المؤشرات"

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) --      
ORDER BY
  ts DESC, id DESC
LIMIT 26;

# 3 تنظيف الفهرس

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;

1. ننزل 25 سجلاً ونحصل على قيمة "الحدود" ts.
2. إذا لم يكن هناك شيء بالفعل ، فاستبدل القيمة NULL بـ -infinity.
3. اطرح الجزء الكامل من القيم بين القيمة المستلمة tsوالمعلمة $ 1 التي تم تمريرها من الواجهة (القيمة المرسومة "الأخيرة" السابقة).
4. إذا تم إرجاع كتلة تحتوي على أقل من 26 إدخالاً ، فهذا هو الأخير.

تعليق

1. , , « ». Index Only Scan.
2. , , ts , . — « 00:00:00.000», . , . , .