Optimierung der GROUP BY-Abfrage zum abrufen der aktuellen Zeile pro Benutzer

Für beste Leseleistung benötigen Sie eine mehrspaltigen index:

CREATE INDEX log_combo_idx
ON log (user_id, log_date DESC NULLS LAST)

Machen index-scans möglich, hinzufügen, die sonst nicht notwendig Spalte payload:

CREATE INDEX log_combo_covering_idx
ON log (user_id, log_date DESC NULLS LAST, payload)

Warum DESC NULLS LAST?

• Nicht verwendete index im Bereich der Daten-Abfrage

Für paar Zeilen pro user_id oder kleine Tische DISTINCT ON ist in der Regel am schnellsten und einfachsten:

• Wählen Sie die erste Zeile in jeder GRUPPE DURCH die Gruppe?

Für viele Zeilen pro user_id eine index skip scan (oder lose index-scan) ist (viel) mehr effizient. Das ist nicht implementiert, die bis zu Postgres-12 - die Arbeit ist im Gange für Postgres 13. Aber es gibt Möglichkeiten, ihn nachzuahmen effizient.

Common Table Expressions erfordern Postgres 8.4+.

LATERAL erfordert Postgres 9.3+.

Die folgenden Lösungen, die über das hinausgehen, was in der Postgres-Wiki.

1. Keine separate Tabelle mit eindeutigen Benutzer

Mit einem separaten users Tabelle, Lösungen in 2. unten sind in der Regel einfacher und schneller. Fahren Sie Fort.

1a. Rekursive CTE mit LATERAL join

WITH RECURSIVE cte AS (
   (                                -- parentheses required
   SELECT user_id, log_date, payload
   FROM   log
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT l.user_id, l.log_date, l.payload
      FROM   log l
      WHERE  l.user_id > c.user_id  -- lateral reference
      AND    log_date <= :mydate    -- repeat condition
      ORDER  BY l.user_id, l.log_date DESC NULLS LAST
      LIMIT  1
      ) l
   )
TABLE  cte
ORDER  BY user_id;

Dies ist ganz einfach abrufen beliebiger Spalten und wahrscheinlich am besten in der aktuellen Postgres. Mehr Erklärung in Kapitel 2a. unten.

1b. Rekursive CTE mit korrelierte Unterabfrage

WITH RECURSIVE cte AS (
   (                                           -- parentheses required
   SELECT l AS my_row                          -- whole row
   FROM   log l
   WHERE  log_date <= :mydate
   ORDER  BY user_id, log_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT l                            -- whole row
           FROM   log l
           WHERE  l.user_id > (c.my_row).user_id
           AND    l.log_date <= :mydate        -- repeat condition
           ORDER  BY l.user_id, l.log_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.my_row).user_id IS NOT NULL       -- note parentheses
   )
SELECT (my_row).*                              -- decompose row
FROM   cte
WHERE  (my_row).user_id IS NOT NULL
ORDER  BY (my_row).user_id;

Bequem abrufen einer einzelne Spalte oder die ganze Reihe. Im Beispiel wird der gesamte Zeilentyp der Tabelle. Andere Varianten sind möglich.

Geltend zu machen, eine Zeile wurde gefunden, die in der vorherigen iteration, testen einer einzelnen NICHT-NULL-Spalte (wie primary key).

Weitere Erklärung für diese Abfrage in Kapitel 2b. unten.

Verwandte:

• Abfrage der letzten N-verknüpften Zeilen pro Zeile
• GRUPPE DURCH eine Spalte, während die Sortierung durch eine andere in PostgreSQL

2. Mit separaten users Tabelle

Tabelle layout fast nicht wichtig, wie lange, wie genau eine Zeile pro einschlägigen user_id gewährleistet ist. Beispiel:

CREATE TABLE users (
   user_id  serial PRIMARY KEY
 , username text NOT NULL
);

Idealerweise wird die Tabelle physisch sortiert in sync mit der log Tabelle. Siehe:

• Optimieren Postgres timestamp-Abfrage-Spektrum

Oder es ist klein genug (niedrige Kardinalität), dass es kaum zählt. Sonst, Sortieren von Zeilen in der Abfrage weiter helfen zu können Optimierung der Leistung. Siehe Gang Liang hinaus. Wenn die körperliche Sortierreihenfolge der users Tabelle geschieht entsprechend der index auf log, kann dies unerheblich sein.

2a. LATERAL join

SELECT u.user_id, l.log_date, l.payload
FROM   users u
CROSS  JOIN LATERAL (
   SELECT l.log_date, l.payload
   FROM   log l
   WHERE  l.user_id = u.user_id         -- lateral reference
   AND    l.log_date <= :mydate
   ORDER  BY l.log_date DESC NULLS LAST
   LIMIT  1
   ) l;

JOIN SEITLICHEN erlaubt Referenz vorhergehenden FROM Elemente auf das gleiche query-Ebene. Siehe:

• Was ist der Unterschied zwischen LATERAL-und einer Unterabfrage in PostgreSQL?

Ergebnisse in einem index (-nur) look-up pro Benutzer.

Gibt keine Zeile für den Benutzer fehlt in der users Tabelle. In der Regel, eine foreign key Einschränkung, die referenzielle Integrität durchsetzen würde, ausschließen.

Auch keine Zeile für Benutzer ohne entsprechenden Eintrag in log - entsprechend der ursprünglichen Frage. Um diese Benutzer in das Ergebnis verwenden LEFT JOIN LATERAL ... ON true statt CROSS JOIN LATERAL:

• Aufruf eines set-returning Funktion mit einem array-argument mehrere Male

Verwenden LIMIT n statt LIMIT 1 abrufen mehr als einer Zeilen (aber nicht alle) pro Benutzer.

Effektiv, alle diese das gleiche tun:

JOIN LATERAL ... ON true
CROSS JOIN LATERAL ...
, LATERAL ...

Den letzten eine geringere Priorität haben, wenn. Explizite JOIN bindet vor dem Komma. Der feine Unterschied kann Fragen mit mehr Tabellen verbinden. Siehe:

• "Ungültiger Verweis auf FROM-Klausel-Eintrag für Tabelle" in Postgres query

2b. Korrelierte Unterabfrage

Gute Wahl zum abrufen einer einzelne Spalte von einem einzelne Zeile. Code-Beispiel:

• Optimieren groupwise maximum query

Das gleiche ist möglich für mehrere Spalten, aber Sie brauchen mehr smarts:

CREATE TEMP TABLE combo (log_date date, payload int);

SELECT user_id, (combo1).*              -- note parentheses
FROM (
   SELECT u.user_id
        , (SELECT (l.log_date, l.payload)::combo
           FROM   log l
           WHERE  l.user_id = u.user_id
           AND    l.log_date <= :mydate
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

• Wie LEFT JOIN LATERAL oben, diese Variante umfasst alle Benutzer, auch ohne Einträge in log. Sie erhalten NULL für combo1, die können Sie ganz einfach filter mit einem WHERE - Klausel in der äußeren Abfrage, wenn es sein muss.
  
  _{- Fehler: in der äußeren Abfrage, Sie können nicht unterscheiden, ob die Unterabfrage nicht eine Zeile oder Spalte alle Werte zufällig von NULL - gleiche Ergebnis. Sie brauchen eine NOT NULL Spalte in der Unterabfrage zu vermeiden, diese Zweideutigkeit.}

• Einer korrelierten Unterabfrage kann nur zurückgeben ein einzelnen Wert. Sie können wickeln Sie mehrere Spalten in einem zusammengesetzten Typ. Aber zerlegen Sie es später, Postgres-Forderungen eines bekannten zusammengesetzten Typ. Anonyme Einträge kann nur zerlegt werden, die Bereitstellung einer spaltendefinitionsliste.
  
  Verwenden Sie einen registrierten Typ wie der Zeilentyp einer vorhandenen Tabelle. Oder registrieren Sie einen composite-Typ explizit (und dauerhaft) mit CREATE TYPE. Oder erstellen Sie eine temporäre Tabelle (fiel automatisch am Ende der Sitzung) zu registrieren, deren Zeilen-Art vorübergehend. Cast-syntax: (log_date, payload)::combo

• Schließlich wollen wir nicht zerlegen combo1 auf das gleiche query-Ebene. Durch eine Schwachstelle in der query-Planer würde dies bewerten die Unterabfrage einmal für jede Spalte (wahr noch in Postgres 12). Stattdessen machen Sie es mit einer Unterabfrage und zersetzen sich in der äußeren Abfrage.

Verwandte:

• Werte aus der ersten und letzten Zeile pro Gruppe

Demonstrieren, alle 4 Abfragen mit 100k log-Einträge und 1k-Benutzer:

db<>fiddle hier - pg 11

Alte sqlfiddle - pg 9.6

Vielleicht einen anderen index für die Tabelle helfen würde. Try this one: log(user_id, log_date). Ich bin nicht positiv, dass Postgres wird optimal auf die Verwendung mit distinct on.

So, ich würde mit dem stick-index und versuchen Sie diese version:

select *
from log l
where not exists (select 1
                  from log l2
                  where l2.user_id = l.user_id and
                        l2.log_date <= :mydate and
                        l2.log_date > l.log_date
                 );

Diese ersetzen sollte die Sortierung/Gruppierung mit index look-ups. Es könnte schneller sein.