Méthode rapide pour découvrir le nombre de lignes d'une table dans PostgreSQL

Question

J'ai besoin de connaître le nombre de lignes d'un tableau pour calculer un pourcentage. Si le nombre total est supérieur à une constante prédéfinie, j'utiliserai la valeur constante. Sinon, j'utiliserai le nombre réel de lignes.

Je peux utiliser SELECT count(*) FROM table . Mais si ma valeur constante est 500,000 et j'ai 5,000,000,000 rangées dans ma table, compter toutes les rangées fera perdre beaucoup de temps.

Est-il possible d'arrêter le comptage dès que la valeur de ma constante est dépassée ?

J'ai besoin du nombre exact de lignes, à condition qu'il soit inférieur à la limite fixée. Sinon, si le nombre est supérieur à la limite, j'utilise la valeur limite à la place et je veux la réponse le plus rapidement possible.

Quelque chose comme ça :

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

Answer 1

Le comptage des lignes dans les grandes tables est connu pour être lent dans PostgreSQL. Le site MVCC nécessite un comptage complet des lignes actives pour obtenir un nombre précis. Il existe des solutions de contournement pour accélérer considérablement le processus si le compte ne no doivent être exacto comme cela semble être le cas dans votre affaire.

(Rappelez-vous que même un comptage "exact" est potentiellement mort à l'arrivée sous une charge d'écriture simultanée).

Compte exact

Lent pour les grandes tables.
Avec les opérations d'écriture simultanées, elle peut être périmée au moment où vous l'obtenez.

SELECT count(*) AS exact_count FROM myschema.mytable;

Estimation

Extrêmement rapide :

SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

En général, l'estimation est très proche. Cette proximité dépend du fait que ANALYZE o VACUUM sont exécutés suffisamment - où "suffisamment" est défini par le niveau d'activité d'écriture sur votre table.

Une estimation plus sûre

Ce qui précède ne tient pas compte de la possibilité d'avoir plusieurs tables portant le même nom dans une même base de données - dans des schémas différents. Pour en tenir compte :

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema';

Le casting pour bigint met en forme le real bien, surtout pour les grands nombres.

Meilleure estimation

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

Plus rapide, plus simple, plus sûr, plus élégant. Voir le manuel sur Types d'identificateurs d'objets .

Remplacer 'myschema.mytable'::regclass con to_regclass('myschema.mytable') dans Postgres 9.4+ pour ne rien obtenir au lieu d'une exception pour les noms de tables invalides. Voir :

• Comment vérifier si une table existe dans un schéma donné ?

Meilleure estimation encore (pour un coût supplémentaire très faible)

Nous pouvons faire ce que le planificateur Postgres fait. En citant le Exemples d'estimation de rangs dans le manuel :

Ces chiffres sont à jour au moment de la dernière VACUUM o ANALYZE sur le tableau. Le planificateur va ensuite chercher le nombre actuel de pages dans le tableau. la table (il s'agit d'une opération peu coûteuse, ne nécessitant pas de balayage de la table). Si est différent de relpages puis reltuples est mis à l'échelle en conséquence pour obtenir une estimation du nombre de rangs actuels.

Postgres utilise estimate_rel_size défini dans src/backend/utils/adt/plancat.c qui couvre également le cas de figure où il n'y a pas de données dans la base de données. pg_class parce que la relation n'a jamais été aspirée. Nous pouvons faire quelque chose de similaire en SQL :

Forme minimale

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

Sûr et explicite

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
     * (pg_catalog.pg_relation_size(c.oid)
      / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_catalog.pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

Ne se brise pas avec les tables vides et les tables qui n'ont jamais vu VACUUM o ANALYZE . Le manuel sur pg_class :

Si la table n'a encore jamais été aspirée ou analysée, reltuples contient -1 indiquant que le nombre de lignes est inconnu.

Si cette requête renvoie NULL , courir ANALYZE o VACUUM pour le tableau et répétez. (Vous pourriez aussi estimer la largeur des lignes en fonction des types de colonnes, comme le fait Postgres, mais c'est fastidieux et source d'erreurs).

Si cette requête renvoie 0 la table semble être vide. Mais je voudrais ANALYZE pour être sûr. (Et peut-être vérifier votre autovacuum paramètres.)

Typiquement, block_size est 8192. current_setting('block_size')::int couvre de rares exceptions.

Les qualifications de la table et du schéma l'immunisent contre toute search_path et la portée.

Dans tous les cas, la requête prend systématiquement moins de 0,1 ms pour moi.

Plus de ressources Web :

• El FAQ du wiki Postgres
• Les pages du wiki Postgres pour estimations du nombre de personnes y compte(*) performance

TABLESAMPLE SYSTEM (n) dans Postgres 9.5+.

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

Comme @a_horse a commenté la clause ajoutée pour les SELECT peut être utile si les statistiques dans pg_class ne sont pas assez courants pour une raison quelconque. Par exemple :

• Non autovacuum en cours d'exécution.
• Immédiatement après un grand INSERT / UPDATE / DELETE .
• TEMPORARY (qui ne sont pas couvertes par les autovacuum ).

Cela ne regarde qu'un échantillon aléatoire n % ( 1 dans l'exemple) sélection de blocs et compte des lignes dans celle-ci. Un échantillon plus grand augmente le coût et réduit l'erreur, à vous de choisir. La précision dépend de plus de facteurs :

• Distribution de la taille des rangs. Si un bloc donné contient des rangs plus larges que d'habitude, le nombre de rangs est plus faible que d'habitude, etc.
• Des tuples morts ou un FILLFACTOR occupent un espace par bloc. Si la répartition est inégale sur le tableau, l'estimation peut être erronée.
• Erreurs d'arrondi générales.

En général, l'estimation de pg_class sera plus rapide et plus précis.

Réponse à la question actuelle

Tout d'abord, j'ai besoin de connaître le nombre de lignes dans ce tableau, si le total est supérieur à une constante prédéfinie,

Et que ce soit...

... est possible qu'au moment où le comptage passe ma valeur constante, il va arrêter le comptage (et ne pas attendre la fin du comptage pour informer le que le nombre de lignes est supérieur).

Sí. Vous pouvez utiliser un sous-requête avec LIMIT :

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

Postgres arrête effectivement de compter au-delà de la limite donnée, vous obtenez un exact et actuel compter jusqu'à n rangs (500000 dans l'exemple), et n autrement. Pas aussi rapide que l'estimation dans pg_class mais

Answer 2

J'ai fait cela une fois dans une application postgres en exécutant :

EXPLAIN SELECT * FROM foo;

Puis examiner la sortie avec une regex, ou une logique similaire. Pour un simple SELECT *, la première ligne de la sortie devrait ressembler à quelque chose comme ceci :

Seq Scan on uids  (cost=0.00..1.21 rows=8 width=75)

Vous pouvez utiliser le rows=(\d+) comme une estimation approximative du nombre de lignes qui seraient renvoyées, puis n'effectuez que l'analyse réelle des données. SELECT COUNT(*) si l'estimation est, par exemple, inférieure à 1,5 fois votre seuil (ou tout autre chiffre que vous jugez utile pour votre application).

Selon la complexité de votre requête, ce nombre peut devenir de moins en moins précis. En fait, dans mon application, au fur et à mesure que nous avons ajouté des jointures et des conditions complexes, il est devenu si imprécis qu'il était complètement inutile, même pour savoir à une puissance de 100 près combien de lignes nous aurions retournées, et nous avons donc dû abandonner cette stratégie.

Mais si votre requête est suffisamment simple pour que Pg puisse prédire avec une marge d'erreur raisonnable le nombre de lignes qu'elle retournera, cela peut fonctionner pour vous.

Answer 3

Référence tirée de ce blog.

Vous pouvez utiliser la requête suivante pour trouver le nombre de lignes.

Utilisation de pg_class :

 SELECT reltuples::bigint AS EstimatedCount
    FROM   pg_class
    WHERE  oid = 'public.TableName'::regclass;

Utilisation de pg_stat_user_tables :

SELECT 
    schemaname
    ,relname
    ,n_live_tup AS EstimatedCount 
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Answer 4

Quelle est la largeur de la colonne de texte ?

Avec un GROUP BY, il n'y a pas grand-chose que vous puissiez faire pour éviter un balayage des données (au moins un balayage de l'index).

Je vous le recommande :

1. Si possible, modifier le schéma pour supprimer la duplication des données textuelles. De cette façon, le comptage se fera sur un champ de clé étrangère étroit dans la table 'many'.

2. Une autre solution consiste à créer une colonne générée avec un HASH du texte, puis à GROUPER PAR la colonne de hachage. Là encore, il s'agit de réduire la charge de travail (balayage d'un index de colonne étroit).

Edit :

Votre question originale ne correspondait pas tout à fait à votre édition. Je ne sais pas si vous êtes conscient que le COUNT, lorsqu'il est utilisé avec un GROUP BY, renvoie le nombre d'éléments par groupe et non le nombre d'éléments de la table entière.

Answer 5

Vous pouvez obtenir le compte par la requête ci-dessous (sans * ni aucun nom de colonne).

select from table_name;