On se plaint souvent que le nombre (*) dans PostgreSQL est très lent.Dans cet article, je souhaite explorer les options afin d'obtenir le résultat le plus rapidement possible.Pourquoi le décompte (*) est-il si lent?
La plupart des gens comprennent sans problème que la requête suivante sera exécutée lentement:SELECT count(*)
FROM ;
Il s'agit, après tout, d'une requête complexe, et PostgreSQL doit calculer le résultat avant de savoir combien de lignes il contiendra.Mais beaucoup de gens sont choqués lorsqu'ils découvrent que la requête suivante est lente:SELECT count(*) FROM large_table;
Cependant, si vous pensez à nouveau, tout ce qui précède est vrai: PostgreSQL doit calculer le jeu de résultats avant de pouvoir le compter. Comme le «compteur de lignes magiques» n'est pas stocké dans la table (comme dans MyISAM MySQL), la seule façon de compter les lignes est de les regarder.Par conséquent, count (*) effectue généralement des analyses de table séquentielles, ce qui peut être assez coûteux.Le "*" dans le décompte (*) est-il un problème?
Le "*" dans SELECT * FROM ... s'applique à toutes les colonnes. Par conséquent, de nombreuses personnes trouvent que l'utilisation de count (*) est inefficace et utilisent plutôt count (id) ou count (1) à la place.Mais le "*" dans count (*) est complètement différent, cela signifie simplement "string" et ne se développe pas du tout (en fait, c'est "un agrégat avec zéro argument"). La notation count (1) ou count (id) est en fait plus lente que count (*), car il faut vérifier si l'argument est NULL ou non (count, comme la plupart des agrégats, ignore les arguments NULL).Vous n'obtiendrez donc rien en évitant le "*".Utilisation de l' analyse d'index uniquement
Il est tentant d'analyser un petit index, pas la table entière, pour compter le nombre de lignes. Cependant, ce n'est pas si simple dans PostgreSQL en raison de sa stratégie de gestion des accès simultanés multi-versions. Chaque version de la ligne («tuple») contient des informations sur l'instantané de base de données auquel elle est visible . Mais ces informations (redondantes) ne sont pas stockées dans des index. Par conséquent, il n'est généralement pas suffisant de compter les entrées dans l'index, car PostgreSQL doit regarder l'entrée de table («tuple de tas») pour s'assurer que l'entrée d'index est visible.Pour atténuer ce problème, PostgreSQL a mis en œuvre une carte de visibilité , une structure de données qui stocke des informations indiquant si tous les tuples d'un bloc de table sont visibles par tout le monde ou non.Si la plupart des blocs du tableau sont entièrement visibles, les analyses d'index ne nécessitent pas de visites fréquentes à un groupe de tuples pour déterminer la visibilité. Une telle analyse d'index est appelée «analyse d'index uniquement» et il est souvent plus rapide d'analyser un index pour compter les lignes.Maintenant, c'est VACUUM qui prend en charge la carte de visibilité, alors assurez-vous que l'auto-vide est effectué assez souvent si vous souhaitez utiliser un petit index pour accélérer le comptage (*).Utilisation du tableau croisé dynamique
J'ai écrit ci-dessus que PostgreSQL ne stocke pas le nombre de lignes dans une table.Le maintien d'un tel nombre de lignes est une surcharge importante, car cet événement se produit à chaque modification de données et ne rapporte pas. Ce serait une mauvaise affaire. De plus, étant donné que différentes requêtes peuvent voir différentes versions de chaînes, le compteur doit également être versionné.Mais rien ne vous empêche d'implémenter vous-même un tel compteur de lignes.Supposons que vous souhaitiez suivre le nombre de lignes dans une mytable. Vous pouvez le faire comme suit:START TRANSACTION;
CREATE TABLE mytable_count(c bigint);
CREATE FUNCTION mytable_count() RETURNS trigger
LANGUAGE plpgsql AS
$$BEGIN
IF TG_OP = 'INSERT' THEN
UPDATE mytable_count SET c = c + 1;
RETURN NEW;
ELSIF TG_OP = 'DELETE' THEN
UPDATE mytable_count SET c = c - 1;
RETURN OLD;
ELSE
UPDATE mytable_count SET c = 0;
RETURN NULL;
END IF;
END;$$;
CREATE CONSTRAINT TRIGGER mytable_count_mod
AFTER INSERT OR DELETE ON mytable
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE PROCEDURE mytable_count();
-- TRUNCATE triggers must be FOR EACH STATEMENT
CREATE TRIGGER mytable_count_trunc AFTER TRUNCATE ON mytable
FOR EACH STATEMENT EXECUTE PROCEDURE mytable_count();
-- initialize the counter table
INSERT INTO mytable_count
SELECT count(*) FROM mytable;
COMMIT;
Nous faisons tout en une seule transaction afin qu'aucune modification des données sur les transactions simultanées ne puisse être «perdue» en raison d'une condition de sonnerie.Ceci est garanti par la commande CREATE TRIGGER qui verrouille la table en mode SHARE ROW EXCLUSIVE, ce qui empêche toutes les modifications simultanées.L'inconvénient est que toutes les modifications de données parallèles doivent attendre jusqu'à ce que le compte SELECT (*) soit exécuté.Cela nous donne une alternative vraiment rapide pour compter (*), mais au prix de ralentir toutes les modifications de données dans le tableau. L'utilisation d'un déclencheur de contrainte différée garantit que le verrou de ligne dans mytable_count est aussi court que possible pour améliorer la concurrence.Malgré le fait que cette table de comptage peut recevoir de nombreuses mises à jour, il n'y a aucun dangerIl n'y a pas de "ballonnement de la table" , car toutes ces mises à jour seront "à chaud" (mises à jour CHAUDES).Vous avez vraiment besoin de compter (*)
Parfois, la meilleure solution est de chercher une alternative.Souvent, l'approximation est assez bonne et vous n'avez pas besoin du montant exact. Dans ce cas, vous pouvez utiliser le score utilisé par PostgreSQL pour planifier des requêtes:SELECT reltuples::bigint
FROM pg_catalog.pg_class
WHERE relname = 'mytable';
Cette valeur est mise à jour à la fois par le vide automatique et l'analyse automatique, elle ne doit donc jamais dépasser 10%. Vous pouvez réduire autovacuum_analyze_scale_factor pour cette table afin que l'analyse automatique s'exécute plus souvent.Estimation du nombre de résultats de requête
Jusqu'à présent, nous avons exploré comment accélérer le comptage des lignes du tableau.Mais parfois, vous devez savoir combien de lignes l'instruction SELECT retournera sans réellement exécuter la requête.De toute évidence, la seule façon d'obtenir une réponse précise à cette question est de répondre à la demande. Mais si la note est assez bonne, vous pouvez utiliser l'optimiseur PostgreSQL pour l'obtenir.La fonction simple suivante utilise SQL dynamique et EXPLAIN pour obtenir le plan d'exécution de requête passé en argument et retourne une estimation du nombre de lignes:CREATE FUNCTION row_estimator(query text) RETURNS bigint
LANGUAGE plpgsql AS
$$DECLARE
plan jsonb;
BEGIN
EXECUTE 'EXPLAIN (FORMAT JSON) ' || query INTO plan;
RETURN (plan->0->'Plan'->>'Plan Rows')::bigint;
END;$$;
N'utilisez pas cette fonction pour traiter des instructions SQL non fiables, car elle est intrinsèquement vulnérable à l'injection SQL.