Meilleure façon d'obtenir le nombre de résultats avant l'application de la LIMITE

Question

Lorsque vous parcourez des données provenant d'une base de données, vous devez savoir combien de pages il y aura pour afficher les contrôles de saut de page.

Pour ce faire, j'exécute la requête deux fois, une fois enveloppée dans un fichier count() pour déterminer le nombre total de résultats, et une deuxième fois avec une limite appliquée pour obtenir uniquement les résultats dont j'ai besoin pour la page en cours.

Cela semble inefficace. Existe-t-il un meilleur moyen de déterminer le nombre de résultats qui auraient été renvoyés avant que LIMIT a été appliquée ?

J'utilise PHP et Postgres.

Answer 1

Pure SQL

Les choses ont changé depuis 2008. Vous pouvez utiliser un fonction de fenêtre pour obtenir le décompte complet y le résultat limité en une seule requête. Introduit avec PostgreSQL 8.4 en 2009 .

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

Il convient de noter que cette peut être considérablement plus coûteux que sans le décompte total. . Toutes les lignes doivent être comptées, et un éventuel raccourci consistant à ne prendre que les lignes supérieures d'un index correspondant peut ne plus être utile.
Cela n'a pas beaucoup d'importance pour les petites tables ou les tables d'appoint. full_count <= OFFSET + LIMIT . Des questions pour une plus grande full_count .

Boîtier d'angle : quand OFFSET est au moins aussi grand que le nombre de lignes de la requête de base, pas de rangée est renvoyée. Vous n'obtenez donc pas non plus de full_count . Alternative possible :

• Exécutez une requête avec un LIMIT/OFFSET et obtenez également le nombre total de lignes.

Séquence d'événements dans un SELECT interrogation

( 0. Les CTE sont évalués et matérialisés séparément. Dans Postgres 12 ou une version ultérieure, le planificateur peut intégrer les sous-requêtes avant de se mettre au travail). Pas ici.

1. WHERE (et JOIN mais aucune dans votre exemple) filtrent les lignes qualifiées de la (des) table(s) de base. Le reste est basé sur le sous-ensemble filtré.

( 2. GROUP BY et des fonctions agrégées). Pas ici.

( 3) Autres SELECT les expressions de liste sont évaluées, sur la base des colonnes groupées / agrégées). Ce n'est pas le cas ici.

4. Les fonctions de la fenêtre sont appliquées en fonction de la OVER et la spécification du cadre de la fonction. La clause count(*) OVER() est basé sur toutes les lignes qualifiées.

5. ORDER BY

( 6. DISTINCT o DISTINCT ON (voir ici). Pas ici.

7. LIMIT / OFFSET sont appliquées en fonction de l'ordre établi pour sélectionner les lignes à renvoyer.

LIMIT / OFFSET devient de plus en plus inefficace lorsque le nombre de lignes du tableau augmente. Envisagez d'autres approches si vous avez besoin de meilleures performances :

• Optimiser la requête avec OFFSET sur une grande table

Alternatives pour obtenir le décompte final

Il existe des approches complètement différentes pour obtenir le nombre de lignes affectées ( no le décompte complet avant OFFSET & LIMIT ont été appliquées). Postgres comptabilise en interne le nombre de lignes affectées par la dernière commande SQL. Certains clients peuvent accéder à cette information ou compter eux-mêmes les lignes (comme psql).

Par exemple, vous pouvez récupérer le nombre de lignes affectées dans la rubrique plpgsql immédiatement après l'exécution d'une commande SQL avec :

GET DIAGNOSTICS integer_var = ROW_COUNT;

Détails dans le manuel.

Vous pouvez également utiliser pg_num_rows en PHP . Ou des fonctions similaires chez d'autres clients.

En rapport :

• Calculer le nombre de lignes affectées par une requête batch dans PostgreSQL

Answer 2

Comme je l'ai décrit sur mon blog MySQL dispose d'une fonction appelée SQL_CALC_FOUND_ROWS . Il n'est donc plus nécessaire d'effectuer la requête deux fois, mais il faut toujours l'effectuer dans son intégralité, même si la clause de limitation lui aurait permis de s'arrêter plus tôt.

À ma connaissance, il n'existe pas de fonction similaire pour PostgreSQL. Une chose à laquelle il faut faire attention lorsque l'on fait de la pagination (la chose la plus courante pour laquelle LIMIT est utilisé IMHO) : faire un "OFFSET 1000 LIMIT 10" signifie que la base de données doit aller chercher au moins 1010 lignes, même si vous n'en obtenez que 10. Une méthode plus performante consiste à se souvenir de la valeur de la ligne par laquelle vous ordonnez pour la ligne précédente (la 1000e dans ce cas) et à réécrire la requête comme suit : "... WHERE order_row > value_of_1000_th LIMIT 10". L'avantage est que "order_row" est très probablement indexé (si ce n'est pas le cas, vous avez un problème). L'inconvénient est que si de nouveaux éléments sont ajoutés entre deux pages, la synchronisation risque d'être un peu perturbée (mais cela n'est pas forcément observable par les visiteurs et peut représenter un gain de performance important).

Answer 3

Vous pourriez atténuer la pénalité de performance en n'exécutant pas la requête COUNT() à chaque fois. Mettez en cache le nombre de pages pendant, disons, 5 minutes avant d'exécuter à nouveau la requête. À moins que vous ne voyiez un grand nombre d'insertions, cela devrait fonctionner parfaitement.

Answer 4

Étant donné que Postgres effectue déjà un certain nombre de mises en cache, ce type de méthode n'est pas aussi inefficace qu'il n'y paraît. Elle ne double certainement pas le temps d'exécution. Nous avons intégré des chronomètres dans notre couche de base de données, j'en ai donc eu la preuve.

Answer 5

Étant donné que vous avez besoin de savoir pour la pagination, je suggérerais d'exécuter la requête complète une fois, d'écrire les données sur le disque en tant que cache côté serveur, puis de les faire passer par votre mécanisme de pagination.

Si vous exécutez la requête COUNT dans le but de décider de fournir ou non les données à l'utilisateur (c'est-à-dire, s'il y a > X enregistrements, renvoyer une erreur), vous devez vous en tenir à l'approche COUNT.