🥇PostgreSQL Antipatterns: subcriptio navigationis

Hodie nullae causae implicatae erunt et algorithmi urbani in SQL. Omnia simplicissima erunt, in gradu Obvioi - faciamus viewing eventu subcriptio digestus tempore.

Id est, signum datorum eventset habet agrum ts - Tempus exacte quo volumus haec monumenta ordine monstrare;

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

CREATE INDEX ON events(ts DESC);

Liquet nos ibi duodecim tabulas non habere, ut forma aliqua indigebimus pagina navigationis.

#0. "Pogromista matris meae sum"

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

Fere non iocus est - rara est, sed in bestiis invenitur. Aliquando, postquam cum ORM laborat, difficile est ad opus "directum" flectendum cum SQL.

Sed transeamus ad problemata communiora et minus manifesta.

#1. INCREMENTUM

SELECT
  ...
FROM
  events
ORDER BY
  ts DESC
LIMIT 26 OFFSET $1; -- 26 - записей на странице, $1 - начало страницы

Ubi numerus XXVI venire? Hic est numerus approximatorum viscus ut unum velum impleat. Accuratius, 26 monumenta ostendi, plus 25, significans saltem aliquid aliud esse in sample et facit sensum movere in.

Utique, haec utilitas non potest in corpus petitionis "consi", sed per modulum transiri. Sed in hoc casu, schedula PostgreSQL in scientia niti non poterit paucas relationes esse relationes - et consilium inefficax facile eliget.

Et dum in schedula interfaciei inspicitur, subcriptio effectum est ut commutatio inter " paginas visuales", nemo quicquam suspectum diu animadvertit. Prorsus usque ad momentum quo, in conflictu pro opportunitate, UI/UX decernit interfaciem reformare ad "librum infinitum" - hoc est, omnes registri viscus in uno indice ducuntur, quos usor sursum ac deorsum librum facere potest.

Itaque in altera probatione captus es duplicatione monumentorum in Registro. Cur, quia mensa index normalem habet (ts)quem interrogatio tua innititur?

Prorsus quod non consideras ts non est a unique key in hac tabula. Nam et valores eius non sunt uniquesicut quodlibet "tempus" in realibus conditionibus - ergo idem recordum in duabus quaestionibus adjacentibus "prosilit" a pagina ad paginam propter alium ordinem finalem intra ambitum idem valorem clavis voluptua.

Re quidem vera etiam secunda quaestio hic occultatur, quae multo difficilius est notatu. quidam entries non ostensum est omnino! Post omnes monumenta "duplicata" alium locum occupaverunt. Explicatio cum pulcherrima imaginibus inveniri potest hic legitur.

Dilatantur indicem

Elit callidus intellegit index clavem singularem fieri oportere, et viam facillime amplificare cum campo manifesto unico, qui PK perfectus est;

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

Mutat et rogatu;

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

#2. Switch ad "cursors"

Post aliquod tempus DBA ad te venit et "placuit" petitiones tuas Servo sicut inferni onerant cum suis INCREMENTUM praeceptaet generatim tempus est mutandae navigationem de ultimo valore ostensum est. Quaestione tua iterum mutat:

SELECT
  ...
WHERE
  (ts, id) < ($1, $2) -- последние полученные на предыдущем шаге значения
ORDER BY
  ts DESC, id DESC
LIMIT 26;

Susspirasti suspirium subsidio donec veniret...

#3. Purgatio index

Quia unus dies tuus DBA legitur articulus de inveniendo inefficax indexes et intellexit "Non tardus" indicatione non est bonum. Veni autem ad vos - nunc cum cogitatione index ille adhuc in tergum convertatur (ts DESC).

Sed quid agat cum problema initiali "saliendi" monumentorum inter paginas?... Et omnia simplicia sunt - necesse est stipites eligere cum incerto numero monumentorum!

In genere, quis vetat non omnino 26 legere, sed non 26 minus? Exempli gratia, ut in proximo clausura sint records cum manifesto significationes ts - tunc quaestio non erit cum monumentis "saliendi" inter caudices!

Ecce quomodo hoc assequendum est:

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;

Quid hic agitur?

Gradus XXV records "descendere" et "terminus" valorem ts.
Si iam nihil est, tunc restitue nullum valorem cum -infinity.
Totam portionem valorum inter valorem receptum subtrahemus ts et $1 modulus ab interface (prior "ultimus" valorem redditum transiit.
Si clausus minus quam 26 litteris reddatur, novissima est.

Vel eadem pictura;

Quia nunc habemus sample specifica non habet aliquam "principium"nihil obstat quominus hanc petitionem in contrariam partem "expandi" et dynamicam onerationem notitiarum stipitiorum exsequantur a "puncto" in utramque partem - tum deorsum et sursum.

illud

Ita in hoc casu bis ad indicem accedimus, omnia autem "indicem solum". Ergo subquisitio nonnisi in Index additional ad unum tantum Scan.
Perspicuum est hanc artem tantum adhiberi posse, cum valores habeas ts potest transire nisi forte et non multi. Si casus typicus tuus est "decies centena milia monumentorum 00:00:00.000", hoc non debes. Dico, non debes talem casum accidere. Quod si hoc incidit, optione utere cum indice extenso.

Source: www.habr.com

PostgreSQL Antipatterns: Navigating Subcriptio