Optimiser le groupe par requête pour récupérer le dernier enregistrement par utilisateur

Pour de meilleures performances de lecture vous avez besoin d'un index multicolonne :

CREATE INDEX user_msg_log_combo_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST)

pour rendre index only scans possible, ajouter la colonne running_total :

CREATE INDEX user_msg_log_combo_covering_idx
ON user_msg_log (user_id, aggr_date DESC NULLS LAST, running_total)

pourquoi DESC NULLS LAST ?

• non utilisé index dans la plage de dates de requête

Pour quelques lignes par user_id ou petites tables un simple DISTINCT ON est parmi les solutions les plus rapides et les plus simples:

• sélectionnez première ligne dans chaque groupe par groupe?

Pour beaucoup lignes par user_id un lâche index scan serait être (beaucoup) plus efficace. Cela n'est pas mis en œuvre dans Postgres (au moins jusqu'à Postgres 10), mais il y a des façons de l'imiter:

1. Pas de tableau séparé avec des utilisateurs uniques

les solutions suivantes vont au-delà de ce qui est couvert dans le Postgres Wiki .

^{avec un tableau séparé users , solutions dans 2. ci-dessous sont généralement plus simple et plus rapide.}

1a. CTE récursive avec LATERAL join

Expressions de Table Communes exiger Postgres 8.4+ .

LATERAL nécessite Postgres 9.3+ .

WITH RECURSIVE cte AS (
   (  -- parentheses required
   SELECT user_id, aggr_date, running_total
   FROM   user_msg_log
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT u.user_id, u.aggr_date, u.running_total
   FROM   cte c
   ,      LATERAL (
      SELECT user_id, aggr_date, running_total
      FROM   user_msg_log
      WHERE  user_id > c.user_id   -- lateral reference
      AND    aggr_date <= :mydate  -- repeat condition
      ORDER  BY user_id, aggr_date DESC NULLS LAST
      LIMIT  1
      ) u
   )
SELECT user_id, aggr_date, running_total
FROM   cte
ORDER  BY user_id;

C'est préférable dans les versions actuelles de Postgres et c'est simple pour récupérer arbitraire colonnes. Plus d'explications dans le chapitre 2a. ci-dessous.

1b. CTE récursif avec sous-séries corrélées

pratique pour récupérer soit une colonne simple ou la rangée entière . L'exemple utilise le type de ligne entière de la table. D'autres variantes sont possibles.

WITH RECURSIVE cte AS (
   (
   SELECT u  -- whole row
   FROM   user_msg_log u
   WHERE  aggr_date <= :mydate
   ORDER  BY user_id, aggr_date DESC NULLS LAST
   LIMIT  1
   )
   UNION ALL
   SELECT (SELECT u1  -- again, whole row
           FROM   user_msg_log u1
           WHERE  user_id > (c.u).user_id  -- parentheses to access row type
           AND    aggr_date <= :mydate     -- repeat predicate
           ORDER  BY user_id, aggr_date DESC NULLS LAST
           LIMIT  1)
   FROM   cte c
   WHERE  (c.u).user_id IS NOT NULL        -- any NOT NULL column of the row
   )
SELECT (u).*                               -- finally decompose row
FROM   cte
WHERE  (u).user_id IS NOT NULL             -- any column defined NOT NULL
ORDER  BY (u).user_id;

Il pourrait être trompeur de tester la valeur de ligne avec c.u IS NOT NULL . Cela retourne true seulement si chaque colonne de la ligne testée est NOT NULL et échouerait si une seule valeur NULL est contenue. (J'ai eu ce bug dans ma réponse pour un certain temps.) Au lieu de cela, pour affirmer qu'une rangée a été trouvée dans l'itération précédente, tester une colonne simple de la rangée qui est définie NOT NULL (comme la clé primaire). Plus:

• contrainte non nulle sur un ensemble de colonnes
• N'est pas nul le test pour un enregistrement ne retourne pas TRUE lorsque la variable est définie

plus d'explications pour cette requête dans le chapitre 2b. ci-dessous.

Réponses associées:

• Requête N dernières lignes liées par ligne
• GROUPE PAR une colonne, tandis que le tri par un autre dans PostgreSQL

2. Avec users table "1519870920 séparée"

Tableau de mise en page à peine de questions tant que nous avons exactement une ligne selon les user_id . Exemple:

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

idéalement, le tableau est physiquement trié. Voir:

• Optimiser Postgres timestamp de la requête de la gamme

ou c'est assez petit (faible cardinalité) que cela n'a guère d'importance.

Autrement, le tri des lignes dans la requête peut aider à optimiser davantage les performances. voir L'ajout de Gang Liang.

2a. LATERAL rejoindre

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

JOIN LATERAL permet de faire référence à des éléments qui précèdent FROM sur le même niveau de requête. Vous obtenez un indice de (seulement) de recherche par l'utilisateur.

• Quelle est la différence entre un latéral et un sous-query en PostgreSQL?

envisager l'Amélioration possible en triant la users table suggérée par Gang Liang dans une autre réponse . Si l'ordre de tri physique de la table users correspond à l'index de user_msg_log , vous n'avez pas besoin de cela.

vous ne recevez pas de résultats pour les utilisateurs users table, même si vous avez des entrées dans user_msg_log . Typiquement, vous auriez une clé étrangère contrainte d'exécution de l'intégrité référentielle pour exclure cela.

vous n'obtenez pas non plus de ligne pour tout utilisateur qui n'a pas d'entrée correspondante dans user_msg_log . Cela correspond à votre question initiale. Si vous devez inclure ces lignes dans le résultat, utilisez LEFT JOIN LATERAL ... ON true au lieu de CROSS JOIN LATERAL :

• Appeler un retour de fonction avec un argument de tableau à plusieurs reprises

ce formulaire est également préférable pour récupérer plus d'une rangée (mais pas tous) par utilisateur. Il suffit d'utiliser LIMIT n au lieu de LIMIT 1 .

effectivement, tous ceux-ci feraient la même chose:

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

ce dernier a toutefois une priorité moindre. Explicite JOIN se lie avant la virgule.

2b. Sous-traitance corrélée

bon choix pour récupérer une colonne simple d'une ligne simple . Exemple de Code:

• Optimiser groupwise maximum de requête

la même chose est possible pour colonnes multiples , mais vous avez besoin de plus smarts:

CREATE TEMP TABLE combo (aggr_date date, running_total int);

SELECT user_id, (my_combo).*  -- note the parentheses
FROM (
   SELECT u.user_id
        , (SELECT (aggr_date, running_total)::combo
           FROM   user_msg_log
           WHERE  user_id = u.user_id
           AND    aggr_date <= :mydate
           ORDER  BY aggr_date DESC NULLS LAST
           LIMIT  1) AS my_combo
   FROM   users u
   ) sub;

• comme LEFT JOIN LATERAL ci-dessus, cette variante inclut tous utilisateurs, même sans les entrées dans user_msg_log . Vous obtenez NULL pour my_combo , que vous pouvez facilement filtrer avec une clause WHERE dans la requête externe si besoin est.
  
  _{Nitpick: dans la requête externe, vous ne pouvez pas distinguer si la sous-commande n'a pas trouvé une ligne ou toutes les valeurs retournées arriver à être NULL - même résultat. Vous devez inclure une colonne NOT NULL dans le sous-jeu pour être sûr.}

• Une sous-requête en corrélation ne peut renvoyer un valeur unique . Vous pouvez envelopper plusieurs colonnes dans un type composite. Mais pour le décomposer plus tard, Postgres exige un type composite bien connu. Les enregistrements anonymes ne peuvent être décomposés qu'en fournissant une liste de définition de colonne.

• utilisez un type enregistré comme le type de ligne d'une table existante, ou créez un type. Enregistrez un type composite explicitement (et de façon permanente) avec CREATE TYPE , ou créez une table temporaire (abandonnée automatiquement à la fin de la session) pour fournir un type de ligne Temporairement. Coulée sur ce type: (aggr_date, running_total)::combo

• enfin, nous ne voulons pas décomposer combo sur le même niveau de requête. En raison d'une faiblesse dans le planificateur de requête ce évaluerait le sous-questionnaire une fois pour chaque colonne (jusqu'à 9.6 - des améliorations sont prévues pour Postgres 10). Au lieu de cela, faites-en un sous-jeu et décomposez dans la requête externe.

Related:

• Obtenir les valeurs de la première et de la dernière ligne par groupe de

démontrant toutes les 4 requêtes avec 100k log entrées et 1K utilisateurs:

SQL Fiddle - pg 9.6

db < > fiddle here - pg 10