PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 25/03/2021 19:00:08

andarius
Membre

Optimisation requête utilisant lateral

Bonjour à tous,

Je me permets de vous poser une question concernant une requête qui me prend beaucoup plus de temps que raisonnable (env 3 secondes).
Je dois avouer que je ne comprends pas pourquoi elle prend si longtemps. J'ai l'impression que c'est le "lateral" qui pose problème sans vraiment être sûr à 100% ni savoir pourquoi.
Si vous avez des pistes d'améliorations je suis preneur !

Voici la requête en question:

with _test as (
    SELECT ex.id,
        ex.user_id,
       COALESCE(tags.tags, '{}') as tags,
       COALESCE(muscles.muscles, '{}') as muscles
    from exercises ex, 
    lateral (
        SELECT array_agg(t.id) as tags
        from tags t
        inner join exercise_tags e on t.id = e.tag_id
        where e.exercise_id = ex.id
    ) tags,
    lateral (
        SELECT array_agg(t.id) as muscles
        FROM muscles t
        inner join exercise_muscles e on t.id = e.muscle_id
        where e.exercise_id = ex.id
    ) muscles
)
SELECT * from _test ex
where user_id = $1

Les différentes tables (simplifiées) sont:

Exercises (~500 éléments)

CREATE TABLE IF NOT EXISTS exercises
(
    id              UUID PRIMARY KEY NOT NULL,
    user_id     UUID             NOT NULL,
    name        VARCHAR(255)     NOT NULL,
    deleted     BOOLEAN          NOT NULL DEFAULT FALSE
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_exercises_name_lower_unique
    ON exercises (user_id, lower(name)) where deleted is false;

Muscles (~18 éléments)

CREATE TABLE IF NOT EXISTS muscles
(
    id                    SERIAL PRIMARY KEY NOT NULL,
    body_part        VARCHAR(30)        NOT NULL,
    muscle_group     VARCHAR(30)        NOT NULL,

    UNIQUE (body_part, muscle_group)
);

Tags (~100 éléments)

CREATE TABLE IF NOT EXISTS tags
(
    id         UUID PRIMARY KEY NOT NULL,
    label      VARCHAR(255)     NOT NULL,
    user_id    UUID             NOT NULL,
    created_at TIMESTAMP        NOT NULL DEFAULT now(),
    data_type  varchar(50)      NOT NULL,
    UNIQUE (label, data_type, user_id)
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_tags_label_lower_unique
    ON general.tags (lower(label), data_type, user_id);

ainsi que les différentes tables de jointure:

 CREATE TABLE IF NOT EXISTS exercise_tags (
      tag_id UUID NOT NULL REFERENCES tags,
      exercise_id UUID NOT NULL REFERENCES exercises,
      PRIMARY KEY(tag_id, exercise_id)
};

 CREATE TABLE IF NOT EXISTS exercise_muscles (
      muscle_id INTEGER NOT NULL REFERENCES muscles,
      exercise_id UUID NOT NULL REFERENCES exercises,
      PRIMARY KEY(muscle_id, exercise_id)
};

Voici à quoi ressemble graphiquement le EXPLAIN

request.png

| Nested Loop  (cost=10.53..1312.70 rows=123 width=96) (actual time=0.128..8.321 rows=123 loops=1)                                         |
|   ->  Nested Loop  (cost=4.29..541.54 rows=123 width=64) (actual time=0.057..3.378 rows=123 loops=1)                                     |
|         ->  Seq Scan on exercises ex  (cost=0.00..10.09 rows=123 width=32) (actual time=0.011..0.068 rows=123 loops=1)                   |
|               Filter: (user_id = 'd0792a59-686c-461c-bf6f-6f886e86af0a'::uuid)                                                           |
|               Rows Removed by Filter: 124                                                                                                |
|         ->  Aggregate  (cost=4.29..4.30 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=123)                                     |
|               ->  Hash Join  (cost=2.30..4.28 rows=2 width=16) (actual time=0.021..0.024 rows=1 loops=123)                               |
|                     Hash Cond: (t.id = e.tag_id)                                                                                         |
|                     ->  Seq Scan on tags t  (cost=0.00..1.77 rows=77 width=16) (actual time=0.002..0.009 rows=76 loops=40)               |
|                     ->  Hash  (cost=2.28..2.28 rows=2 width=16) (actual time=0.016..0.016 rows=1 loops=123)                              |
|                           Buckets: 1024  Batches: 1  Memory Usage: 8kB                                                                   |
|                           ->  Seq Scan on exercise_tags e  (cost=0.00..2.28 rows=2 width=16) (actual time=0.013..0.014 rows=1 loops=123) |
|                                 Filter: (exercise_id = ex.id)                                                                            |
|                                 Rows Removed by Filter: 101                                                                              |
|   ->  Aggregate  (cost=6.24..6.25 rows=1 width=32) (actual time=0.039..0.039 rows=1 loops=123)                                           |
|         ->  Hash Join  (cost=4.99..6.23 rows=4 width=4) (actual time=0.037..0.037 rows=0 loops=123)                                      |
|               Hash Cond: (t_1.id = e_1.muscle_id)                                                                                        |
|               ->  Seq Scan on muscles t_1  (cost=0.00..1.18 rows=18 width=4) (actual time=0.001..0.003 rows=18 loops=10)                 |
|               ->  Hash  (cost=4.94..4.94 rows=4 width=4) (actual time=0.035..0.035 rows=0 loops=123)                                     |
|                     Buckets: 1024  Batches: 1  Memory Usage: 8kB                                                                         |
|                     ->  Seq Scan on exercise_muscles e_1  (cost=0.00..4.94 rows=4 width=4) (actual time=0.033..0.034 rows=0 loops=123)   |
|                           Filter: (exercise_id = ex.id)                                                                                  |
|                           Rows Removed by Filter: 235                                                                                    |
| Planning Time: 1.316 ms                                                                                                                  |
| Execution Time: 8.388 ms                                                                                                                 |

Hors ligne

#2 25/03/2021 19:11:17

gleu
Administrateur

Re : Optimisation requête utilisant lateral

Vous parlez d'une requête qui dure 3 secondes, et le plan indique 8 ms. Il y a un soucis qq part smile


Guillaume.

Hors ligne

#3 25/03/2021 19:18:42

andarius
Membre

Re : Optimisation requête utilisant lateral

En effet je suis confus...
En fait ce n'est pas la requête en elle même qui prend du temps mais mon client (une lib python) à priori pour récupérer les différents éléments...
Désolé du dérangement du coup !

Hors ligne

#4 25/03/2021 22:23:45

gleu
Administrateur

Re : Optimisation requête utilisant lateral

Pas de soucis smile


Guillaume.

Hors ligne

#5 26/03/2021 10:45:21

andarius
Membre

Re : Optimisation requête utilisant lateral

Après enquête, c'est le

jit=on

par défaut de PG 12 qui me ralentissait. Mes requêtes sont toutes beaucoup plus rapides maintenant.

Hors ligne

#6 26/03/2021 11:33:11

gleu
Administrateur

Re : Optimisation requête utilisant lateral

On a vu ça chez quelques clients en effet. Généralement, on ne le désactive pas mais on augmente son coût d'utilisation.


Guillaume.

Hors ligne

#7 26/03/2021 11:38:45

andarius
Membre

Re : Optimisation requête utilisant lateral

Ah d'accord, je dois avouer que je suis novice là dessus. Je dois jouer avec jit_optimize_above_cost et trouver le point qui correspond le mieux ?
Quelles sont les meilleurs pratiques ?

Dernière modification par andarius (26/03/2021 11:39:16)

Hors ligne

#8 26/03/2021 12:10:41

Re : Optimisation requête utilisant lateral

gleu a écrit :

On a vu ça chez quelques clients en effet. Généralement, on ne le désactive pas mais on augmente son coût d'utilisation.

Intéressant. Ça se diagnostique comment ? Purement empirique ?

Hors ligne

#9 26/03/2021 12:16:35

rjuju
Administrateur

Re : Optimisation requête utilisant lateral

C'est assez empirique.  Il est compliqué d'estimer à partir de quand faire du JIT va améliorer les performances d'une requête.  Enfin, il est surtout compliqué de l'estimer sans passer plus de temps à faire cette estimation que d'exécuter la requête.


Mais de manière générale, si vous applicatif est de type OLTP, clairement activer le JIT est inutile voire contreproductif.  Activez-le au pire sur quelques grosses requête si besoin, après benchmark.

Hors ligne

Pied de page des forums