Explain sur une procédure pl/pgsql [Résolu]

[ymoreau]

Bonjour,
Je teste une procédure écrite en pl/pgsql, et c'est vraiment très lent alors qu'à priori il y a des index qui devraient permettre des requêtes assez rapides. J'ai exécuté quelques SELECT, présents dans la procédure, directement sous psql et ils sont très rapides.

Est-ce qu'il y a un moyen d'avoir des détails sur la manière dont les requêtes sont exécutées dans la procédure. J'ai lu qu'on ne pouvait pas utiliser explain sur une fonction. Et je ne vois pas de moyen d'aller "voir sous le capot". J'utilise des FOR .. IN SELECT .. et des SELECT INTO .. n'ayant que très peu de résultats, par contre le nombre total de lignes de mes tables est énorme.

Merci d'avance, je ne vois pas du tout où chercher.

[estofilo]

En plpgsql, les requêtes sont préparées, donc pour voir leur plan d'exécution de l'extérieur, il faut procéder en deux temps, par exemple:

Code :

1
2
3
 
PREPARE a(int) AS SELECT 1 FROM TABLE WHERE id=$1;
EXPLAIN ANALYZE EXECUTE a(100);

[ymoreau]

A vrai dire je débute et je n'ai pas lu PREPARE dans la doc ou les exemples, j'appelle directement les requêtes comme ceci (c'est un extrait seulement):

Code sql :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE FUNCTION delete_document(doc_id integer) RETURNS void AS
$$
DECLARE
    doc_id_token integer;
    token_index_reference integer;
BEGIN
    -- Clean the no-longer used tokens
    FOR doc_id_token IN SELECT id_token FROM inverted_index WHERE id_doc = doc_id LOOP
        SELECT INTO token_index_reference id_token FROM inverted_index WHERE id_doc != doc_id AND id_token = doc_id_token;
        IF token_index_reference IS NULL THEN
            DELETE FROM tokens WHERE id_token = doc_id_token; -- Delete the token
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

Et en cherchant comment utiliser le PREPARE je suis tombé sur ce message :

Envoyé par Tom Lane

You do not need PREPARE at all in plpgsql. plpgsql's automatic
caching of plans gives you the effect of PREPARE on every statement
without your having to ask for it.

[estofilo]

Je me suis peut-être mal exprimé.
Je ne suggère pas d'ajouter des PREPARE à l'intérieur de plpgsql, car justement l'interpréteur du langage le fait tout seul, donc ça ne servirait à rien.
Ce que je voulais dire, c'est que pour voir de l'extérieur de plgpsql le plan d'exécution tel que va l'exécuter l'interpréteur plpgsql, il faut utiliser PREPARE.

[ymoreau]

Autant pour moi j'avais compris de travers ! Merci pour cette indication, j'ai pu voir ce que donne mes SELECT, par contre à propos des boucles FOR .. IN SELECT, Est-ce que le SELECT utilisé peut être analysé de la même manière qu'un SELECT normal ? (en fait, est-ce que le plan prévu pour la requête sera le même qu'avec la boucle utilisant cette requête)

[vmolines]

Essayez de travailler en ensembliste au maximum. Faire X boucles sur X requêtes dans une procédure stockée est contre performant, en plus d'être moins lisible.

Normalement vous pouvez faire un simple :

Code :

1
2
3
4
DELETE FROM tokens 
WHERE NOT EXISTS (	SELECT * FROM inverted_index 
			WHERE inverted_index.id_token = tokens.id_token 
			AND inverted_index.id_doc != doc_id)

[ymoreau]

En effet je n'avais pas pensé comme ça, je n'ai pas la logique ensembliste, merci pour la remarque

[vmolines]

Vérifiez quand même que la nouvelle forme que je propose donne le bon résultat, j'ai fait ça vite .

Pour info, si vous ne connaissez pas déjà, cette requête utilise une sous requête corrélée qui remplace avantageusement des boucles imbriquees.