Efficace de la dernière requête d'enregistrement avec Postgresql

J'ai besoin de faire une grande requête, mais je ne veux les derniers dossiers.

Pour une seule entrée, je voudrais probablement faire quelque chose comme

SELECT * FROM table WHERE id = ? ORDER BY date DESC LIMIT 1;

Mais j'ai besoin de tirer les derniers dossiers pour un grand (en milliers d'entrées) nombre de dossiers, mais seulement la dernière entrée.

Voici ce que j'ai. Ce n'est pas très efficace. Je me demandais si il ya une meilleure façon.

SELECT * FROM table a WHERE ID IN $LIST AND date = (SELECT max(date) FROM table b WHERE b.id = a.id);

Oui, mes SELECT DISTINCT requête de vous aider de quelque façon? Il doit être plus rapide qu'en corrélation les sous-sélections, mais je ne suis pas sûr de combien.
Utiliser stackoverflow.com/a/2111420/454769

InformationsquelleAutor Sheldon Ross | 2009-11-05

43

Si vous ne voulez pas changer votre modèle de données, vous pouvez utiliser DISTINCT ON pour aller chercher le nouveau record du tableau "b" pour chaque entrée dans "une":
```
SELECT DISTINCT ON (a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY a.id, b.date DESC
```
Si vous voulez éviter un "tri" dans la requête, l'ajout d'un index comme ce pourrait vous aider, mais je ne suis pas sûr:
```
CREATE INDEX b_id_date ON b (id, date DESC)

SELECT DISTINCT ON (b.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY b.id, b.date DESC
```
Alternativement, si vous voulez trier les enregistrements de la table "a" d'une certaine façon:
```
SELECT DISTINCT ON (sort_column, a.id) *
FROM a
INNER JOIN b ON a.id=b.id
ORDER BY sort_column, a.id, b.date DESC
```
Approches alternatives

Cependant, toutes ces requêtes ont encore besoin de lire toutes les lignes référencées partir de la table "b", donc si vous avez beaucoup de données, il pourrait tout simplement être trop lent.

Vous pouvez créer une nouvelle table, qui seul détient la plus récente "b" enregistrement pour chaque a.id -- ou même le déplacement de ces colonnes à la "une" de la table elle-même.
- Si vous êtes à la recherche d'une solution plus efficace d'essayer Manji la réponse ci-dessous. Il comparées environ 3x plus rapide que l'utilisation de l'DISTINCTES SUR la solution présentée ici.
- ce mec est allé tous sur ceci: stackoverflow.com/a/7630564/1699320
InformationsquelleAutor intgr
34

cela pourrait être plus eficient. Différence: la requête de la table b est exécuté seulement 1 heure, votre sous-requête corrélée est exécuté pour chaque ligne:
```
SELECT * 
FROM table a 
JOIN (SELECT ID, max(date) maxDate
        FROM table
      GROUP BY ID) b
ON a.ID = b.ID AND a.date = b.maxDate
WHERE ID IN $LIST 
```
- La pensée qui semblait prometteur, mais la jointure est vraiment inefficace.
- Pourquoi pensez-vous que la jointure est inefficace, d'autant plus qu'elle rejoint avec juste une ligne?
- Ooops, pas un de sûr, désolé
- Après avoir tester les deux méthodes, max(date) était d'environ 3x plus vite pour moi que l'DISTINCTES.
- C'est vraiment une excellente solution! Merci beaucoup! Ma requête réduction du temps de 470ms à 95ms. J'ai utilisé max(id) comme une dernière ligne de l'identificateur. Donc, il est peut être plus efficace que datetime comparaison.
- Dans mon cas, un user table contient tous les utilisateurs du système, un invitation table contient toutes les invitations envoyées à devenir des utilisateurs. Plusieurs invitations peuvent être envoyées par email à l'adresse, à chaque invitation a un valid_until date, un invitation_accepted drapeau, et un created_date. Nous ne se soucient que de la plus récente invitation. Si vous acceptez l'utilisation des fonctions d'agrégation sur toutes les colonnes, les performances, c'est la meilleure réponse.
- FROM user u LEFT JOIN (SELECT lower(i2.email) AS email, bool_or(i2.used) AS used, max(i2.created_date) AS max_created_date, max(i2.valid_until) AS max_valid_until FROM invitation i2 GROUP BY lower(i2.email)) i ON i.email = lower(u.email)
- IIUC cela ne permettrait pas de tirer un autre champ de b? E. g. J'en veux pour preuve le dernier commentaire sur une tâche.
InformationsquelleAutor manji
4

Sur la méthode de créer un petit dérivé de la table contenant la plus récente mise à jour /insertion fois sur la table d'un appel ce tableau a_latest. Tableau a_latest aurez besoin de suffisamment de granularité pour répondre à votre requête spécifique d'exigences. Dans votre cas, il devrait être suffisant d'utiliser
```
CREATE TABLE 
a_latest 
( id INTEGER NOT NULL, 
  date TSTAMP NOT NULL, 
  PRIMARY KEY (id, max_time) );
```
Puis utiliser une requête similaire à celui qui est proposé par najmeddine :
```
SELECT a.* 
FROM TABLE a, TABLE a_latest 
USING ( id, date );
```
L'astuce est alors de garder a_latest jusqu'à ce jour. Le faire à l'aide d'un trigger sur les insertions et mises à jour. Un déclencheur écrit dans plppgsql est assez facile à écrire. Je suis heureux de vous fournir un exemple si vous le souhaitez.

Le point ici est que le calcul de la dernière mise à jour de temps est pris en charge lors de la mises à jour eux-mêmes. Celle-ci se déplace plus de la charge à l'écart de la requête.

InformationsquelleAutor youngthing

que pensez-vous de cela?

select * from (
   SELECT a.*, row_number() over (partition by a.id order by date desc) r 
   FROM table a where ID IN $LIST 
)
WHERE r=1

je l'ai utilisé beaucoup sur le passé

InformationsquelleAutor unknown

1

Si vous avez beaucoup de lignes par id vous voulez certainement une sous-requête corrélée.
Il fera 1 de l'index de recherche par id, mais c'est plus rapide que le tri de l'ensemble de la table.

Quelque chose comme :
```
SELECT a.id,
(SELECT max(t.date) FROM table t WHERE t.id = a.id) AS lastdate
FROM table2;
```
L' 'table2' vous allez utiliser n'est pas le tableau que vous mentionnez dans votre question ci-dessus, car ici, vous avez besoin d'une liste de distinctes id est pour une bonne performance. Depuis votre id sont probablement FKs dans une autre table, utilisez-la.

InformationsquelleAutor peufeu

Vous devez vous connecter pour publier un commentaire.

Approches alternatives