Forums PostgreSQL.fr

Mika313

Membre

Optimisation d'une requête

Bonjour à tous,

Suite à une migration d'Oracle vers PostgreSQL, une requête sous postgre dure 10 fois plus longtemps (50 sec).
J'aimerais savoir quels sont les moyens d'améliorer le temps d'exécution.
Le vacuum est activé, la commande analyze n'est pas oubliée non plus.

Merci.

Dernière modification par Mika313 (21/02/2018 00:20:13)

Mika313

Membre

Re : Optimisation d'une requête

La requête en question :

select t1.c1, t1.c2 from MaTable t1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MaTable t2 where t2.c4=t1.c4);

Dernière modification par Mika313 (21/02/2018 00:13:47)

dverite

Membre

Re : Optimisation d'une requête

C'est une auto-jointure qui est exprimée via une sous-requête. Apparemment il s'agit de filtrer sur la date la plus récente de la même série de mesure ou quelque chose de ce style. Avec Postgres, il est souvent plus efficace de faire ça avec un DISTINCT ON (id en question)+ tri par date descendante.

A part ça, il y a un index sur rawCurveId? S'il y en a un, apparemment le moteur préfère celui sur la date.

---

@DanielVerite
http://blog-postgresql.verite.pro/

gleu

Administrateur

Re : Optimisation d'une requête

Ajouter l'option BUFFERS permet d'avoir plus d'infos, ainsi qu'activer le paramètre track_io_timing.

---

Guillaume.

Mika313

Membre

Re : Optimisation d'une requête

Merci pour vos réponses.

@dverite
Vous préconisez d'utiliser DISTINCT ON(id) + tri pour la sous requête ?
Tout à fait il y a un index sur la colonne c1 que je viens de supprimer car l'index sur c2 s'avère plus efficace.

@gleu
Je viens de paramétrer comme vous m'avez indiqué.

Voici le nouveau plan d'exécution avec les modifications citées :
https://explain.depesz.com/

Dernière modification par Mika313 (21/02/2018 00:23:18)

dverite

Membre

Re : Optimisation d'une requête

Pour le DISTINCT ON, non ma remarque n'est pas pour la sous-requête mais pour la requête dans son intégralité.
C'est-à-dire que vu génériquement au lieu de faire:

SELECT colonnes FROM table t1 where date=(select max(date) from table t2 where t1.id = t2.id) ;

DISTINCT ON permet d'écrire

SELECT distinct on (id) colonnes FROM table ORDER BY id, date DESC;

qui traduit la même idée de manière plus directe, c'est-à-dire pour chaque id, ne garder que la date la plus récente.

cf https://docs.postgresql.fr/10/sql-select.html section "DISTINCT Clause"

---

@DanielVerite
http://blog-postgresql.verite.pro/

Mika313

Membre

Re : Optimisation d'une requête

Pour être précis, voici la requête complète :

select t1.c1, t1.c2 from MaTable t1
left outer join Table t3
on t1.c1=t3.c1
where t1.c3='string'
and t1.c2=(select max(t2.c2) from MaTable t2 where t2.c4=t1.c4);

La modification de la première partie de la requête me sors moins de lignes qu'avec le select max.

Dernière modification par Mika313 (21/02/2018 00:17:13)

dverite

Membre

Re : Optimisation d'une requête

Intéressant cette différence 54 vs 124 parce que le plan https://explain.depesz.com/s/tZFG  sort pourtant 54 lignes au niveau le plus haut:

Seq Scan on curve abscurve0_  (cost=0.00..344652122.45 rows=299 width=201) (actual time=46354.070..46991.799 rows=54 loops=1)

Si le résultat a 124 lignes, je ne vois pas pourquoi ça ne se voit pas dans le explain analyze, mais quelque chose doit m'échapper.

Indépendamment de ça s'il y a des doublons de calculationDate pour un même rawCurveId le DISTINCT ON devrait les écarter (en garder un seul) alors que la sous-requête va garder les deux. Donc ce n'est pas rigoureusement équivalent.

Ce qui est ennuyeux dans le plan d'exécution montré, c'est qu'il lance la sous-requête 60742 fois.

Si le DISTINCT ON ne fait pas l'affaire, un vrai JOIN marcherait peut-être mieux, avec une sous-requête qui sortirait max(date) par id, du style:

 (SELECT FROM table ) AS t1 JOIN 
     (select rawcurveid, max(calculationDate) as maxdate FROM table GROUP BY rawcurveid) sub
        ON (sub.rawcurveid=t1.rawcurvid AND sub.maxdate=t1.calculationDate)

---

@DanielVerite
http://blog-postgresql.verite.pro/

Mika313

Membre

Re : Optimisation d'une requête

Le plan tZFG sort bien 54 lignes. C'est celui de la requête avec l'option buffers comme demandé par gleu.
C'est le plan de la requête complète avec la jointure (que je n'ai pas posté) qui sort 124 lignes.

J'ai modifié l'indexation en positionnant des indexes multi-colonnes (c3, c1, c2) puis (c4, c2)
Le résultat est surprenant ~500ms.

Je vous remercie pour vos éclaircissements.

Dernière modification par Mika313 (21/02/2018 00:22:00)