optimisation des stats de la page d'accueil

[dba-sig-sfepm]

Ref : #1320 et #1086

Malgré le cache du navigateur, l'affichage des stats de la page d'accueil peut être très long lors d'un premier affichage. La requête est une route du fichier /backend/geonature/core/gn_synthese/routes.py et est défini comme cela :

     nb_allowed_datasets = db.session.scalar(
        select(func.count("*"))
        .select_from(TDatasets)
        .where(TDatasets.filter_by_readable().whereclause)
    )
    query = select(
        func.count(Synthese.id_synthese),
        func.count(func.distinct(Synthese.cd_nom)),
        func.count(func.distinct(Synthese.observers)),
    )
    synthese_query_obj = SyntheseQuery(Synthese, query, {})
    synthese_query_obj.filter_query_with_cruved(g.current_user, permissions)
    result = DB.session.execute(synthese_query_obj.query)
    synthese_counts = result.fetchone()
    data = {
        "nb_data": synthese_counts[0],
        "nb_species": synthese_counts[1],
        "nb_observers": synthese_counts[2],
        "nb_dataset": nb_allowed_datasets,
    }

Un filtre est en effet appliqué en fonction des permissions du rôle. Il en résulte pour les observations, les observateurs et les taxons l'exécution de la requête suivante :

SELECT 
	COUNT(s.id_synthese) AS count_1, 
	COUNT(DISTINCT s.cd_nom) AS count_2, 
	COUNT(DISTINCT s.observers) AS count_3 
FROM gn_synthese.synthese AS s ;

or celle-ci, du fait des "distinct" peut-être très longue avec beaucoup d'enregistrements. (près de 2mn avec 8 million de lignes).
La première chose à faire est d'ajouter un index sur synthese.observers mais ceci ne réduit pas le temps de la requête précédente mais par contre celle ci-dessous donne le même résultat en 4s :

WITH r1 AS (SELECT COUNT(s.id_synthese) AS count_1 FROM gn_synthese.synthese AS s), 
r2 AS (select COUNT(sr2.cd_nom) AS count_2 FROM (SELECT DISTINCT cd_nom FROM gn_synthese.synthese) AS sr2),
r3 AS (select COUNT(sr3.observers) AS count_3 FROM (SELECT DISTINCT observers FROM gn_synthese.synthese) AS sr3)
SELECT count_1, count_2, count_3 FROM r1,r2,r3

Est-il possible de modifier la première requête en la deuxième tout en appliquant le filtre des permissions ? (je ne suis pas expert en python...) Ou éventuellement exécuter les 3 requêtes de manière distincte en appliquant le filtre sur chacune ? Dans les 2 cas je pense que l'affichage des stats serait beaucoup plus rapide.

[dba-sig-sfepm]

Pour tester rapidement j'ai remplacer le code cité plus haut par :

    nb_allowed_datasets = db.session.scalar(
        select(func.count("*"))
        .select_from(TDatasets)
        .where(TDatasets.filter_by_readable().whereclause)
    )

    query1 = select(
        func.count(Synthese.id_synthese),
    )
    synthese_query1_obj = SyntheseQuery(Synthese, query1, {})
    synthese_query1_obj.filter_query_with_cruved(g.current_user, permissions)
    result1 = DB.session.execute(synthese_query1_obj.query)
    count1 = result1.fetchone()

    query2 = select(
        func.count(func.distinct(Synthese.cd_nom)),
    )
    synthese_query2_obj = SyntheseQuery(Synthese, query2, {})
    synthese_query2_obj.filter_query_with_cruved(g.current_user, permissions)
    result2 = DB.session.execute(synthese_query2_obj.query)
    count2 = result2.fetchone()

    query3 = select(
        func.count(func.distinct(Synthese.observers)),
    )
    synthese_query3_obj = SyntheseQuery(Synthese, query3, {})
    synthese_query3_obj.filter_query_with_cruved(g.current_user, permissions)
    result3 = DB.session.execute(synthese_query3_obj.query)
    count3 = result3.fetchone()

    data = {
        "nb_data": count1[0],
        "nb_species": count2[0],
        "nb_observers": count3[0],
        "nb_dataset": nb_allowed_datasets,
    }

et en effet mes stats mettent à présent - de 5s à s'afficher lors d'un premier affichage (contre 2 mn auparavant) mais je suis conscient que code peut être encore optimisé puisqu'il n'y a plus qu'une valeur par requête.

[dba-sig-sfepm]

et bien sur il ne faut pas oublié d'ajouter l'index sur synthese.observers !

[jacquesfize]

Merci @dba-sig-sfepm, j'ai repris tes propositions dans #3308

L'opération sur mon instance (1.7 millions) est calculée en 279 ms en moyenne avec la nouvelle version.

[camillemonchicourt]

Intégré dans la 2.15.2 et le calcul des stats est maintenant très rapide.
On pourrait même retirer le système de cache il me semble vu la rapidité, mais c'est un autre sujet.