Forums PostgreSQL.fr

Abeidi Yacoub

Membre

Optimisation requête

Bonjour
J'ai une interface pour générer des requêtes automatiquement selon le choix de l'utilisateur.
ma bdd de données contient environ 500 tables et chaque table contient environ 1 million de ligne.
tout marche bien, mais je veux faire une optimisation pour éviter des éventuelles problèmes si les tables deviennent plus grosses.

voici deux exemples de requêtes que je génère, elles marchent les deux, mais je veux optimisé de plus.
La vrai différence entre les deux reqs est l'utilisation de INTERSECT au lieu de IN et EXCEPT au lieu de NOT IN.
ma grande surprise était que IN et NOT IN sont plus rapide que INTERSECT et EXCEPT dans la majorité des reqs que j'ai testé.

délai exécution : 10 s
Nombre retourné : 729693
SELECT count(*) as nbr FROM table_1 att
JOIN
(SELECT at.matricule FROM table_1 at
    WHERE matricule in
    (SELECT tc1.matricule FROM
        ( SELECT matricule FROM table_2 WHERE (m3+m4+m5) >= 500000) tc1
        JOIN
        ( SELECT matricule FROM table_3 WHERE (m3+m4+m5) >= 1073741824) tc2 ON tc2.matricule = tc1.matricule
    ) AND matricule not in
    ( SELECT matricule FROM table_2 WHERE   (m3+m4+m5) > 1500000  OR (m6+m7)!= 0 )
) res on res.matricule=att.matricule

délai exécution : 22 s
Nombre retourné : 729693
SELECT count(*) as nbr FROM table_1 att
JOIN
(SELECT at.matricule FROM table_1 at
    INTERSECT
    (SELECT tc1.matricule FROM
        ( SELECT matricule FROM table_2 WHERE (m3+m4+m5) >= 500000 ) tc1
        JOIN
        ( SELECT matricule FROM table_3 WHERE (m3+m4+m5) >= 1073741824 ) tc2 ON tc2.matricule = tc1.matricule
    )
    EXCEPT ( SELECT matricule FROM table_2 WHERE   (m3+m4+m5) > 1500000  OR (m6+m7) != 0 )
) res on res.matricule=att.matricule

Dernière modification par Abeidi Yacoub (24/08/2017 01:03:19)

dverite

Membre

Re : Optimisation requête

Il faut utiliser EXPLAIN ANALYZE pour comparer les plans d'exécution

Garder aussi à l'esprit que WHERE IN () et INTERSECT ne font pas la même chose s'il y a des NULL dans les valeurs.
Et si vous savez qu'il n'y a pas de NULL dans les valeurs, est-ce que le moteur d'exécution peut le savoir avec 100% de certitude dans le contexte de la requête?

---

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

Abeidi Yacoub

Membre

Re : Optimisation requête

Bonjour
Voici mon plan d'exécution sur une BDD locale qui ne contient pas des données.

*** En utilisant IN

Aggregate  (cost=62164.30..62164.31 rows=1 width=0) (actual time=83.577..83.578 rows=1 loops=1)
  ->  Nested Loop  (cost=3275.44..62162.54 rows=705 width=0) (actual time=83.569..83.569 rows=0 loops=1)
        Join Filter: ((table_3.matricule)::text = (att.matricule)::text)
        ->  Hash Semi Join  (cost=3275.01..61700.01 rows=705 width=36) (actual time=83.567..83.567 rows=0 loops=1)
              Hash Cond: ((at.matricule)::text = (table_3.matricule)::text)
              ->  Seq Scan on table_1 at  (cost=741.39..56954.11 rows=839788 width=12) (actual time=25.606..25.606 rows=1 loops=1)
                    Filter: (NOT (hashed SubPlan 1))
                    SubPlan 1
                      ->  Seq Scan on table_2 table_2_1  (cost=0.00..709.75 rows=12658 width=12) (actual time=25.389..25.389 rows=0 loops=1)
                            Filter: ((((m3 + m4) + m5) > 1500000) OR ((m6 + m7) <> 0))
                            Rows Removed by Filter: 12700
              ->  Hash  (cost=2515.98..2515.98 rows=1411 width=24) (actual time=57.906..57.906 rows=0 loops=1)
                    Buckets: 2048  Batches: 1  Memory Usage: 16kB
                    ->  Hash Join  (cost=699.16..2515.98 rows=1411 width=24) (actual time=57.902..57.902 rows=0 loops=1)
                          Hash Cond: ((table_3.matricule)::text = (table_2.matricule)::text)
                          ->  Seq Scan on table_3  (cost=0.00..1772.39 rows=8084 width=12) (actual time=7.344..38.504 rows=6 loops=1)
                                Filter: (((m3 + m4) + m5) >= 1073741824)
                                Rows Removed by Filter: 24245
                          ->  Hash  (cost=646.25..646.25 rows=4233 width=12) (actual time=19.301..19.301 rows=3 loops=1)
                                Buckets: 8192  Batches: 1  Memory Usage: 65kB
                                ->  Seq Scan on table_2  (cost=0.00..646.25 rows=4233 width=12) (actual time=6.429..19.271 rows=3 loops=1)
                                      Filter: (((m3 + m4) + m5) >= 500000)
                                      Rows Removed by Filter: 12697
        ->  Index Only Scan using table_1_pkey on table_1 att  (cost=0.43..0.64 rows=1 width=12) (never executed)
              Index Cond: (matricule = (at.matricule)::text)
              Heap Fetches: 0
Planning time: 4.170 ms
Execution time: 84.190 ms

*** En utilisant intersect

Aggregate  (cost=87701.24..87701.25 rows=1 width=0) (actual time=2673.479..2673.479 rows=1 loops=1)
  ->  Nested Loop  (cost=699.59..87697.72 rows=1411 width=0) (actual time=2673.471..2673.471 rows=0 loops=1)
        ->  Subquery Scan on res  (cost=699.16..76427.71 rows=1411 width=48) (actual time=2673.469..2673.469 rows=0 loops=1)
              ->  HashSetOp Except  (cost=699.16..76413.60 rows=1411 width=12) (actual time=2673.467..2673.467 rows=0 loops=1)
                    ->  Append  (cost=699.16..76378.43 rows=14069 width=12) (actual time=2673.456..2673.456 rows=0 loops=1)
                          ->  Result  (cost=699.16..75542.10 rows=1411 width=12) (actual time=2648.693..2648.693 rows=0 loops=1)
                                ->  HashSetOp Intersect  (cost=699.16..75542.10 rows=1411 width=12) (actual time=2648.690..2648.690 rows=0 loops=1)
                                      ->  Append  (cost=699.16..71339.63 rows=1680988 width=12) (actual time=59.301..2169.849 rows=1679577 loops=1)
                                            ->  Subquery Scan on *SELECT* 2  (cost=699.16..2530.09 rows=1411 width=12) (actual time=59.288..59.288 rows=0 loops=1)
                                                  ->  Hash Join  (cost=699.16..2515.98 rows=1411 width=12) (actual time=59.284..59.284 rows=0 loops=1)
                                                        Hash Cond: ((table_3.matricule)::text = (table_2.matricule)::text)
                                                        ->  Seq Scan on table_3  (cost=0.00..1772.39 rows=8084 width=12) (actual time=7.488..39.013 rows=6 loops=1)
                                                              Filter: (((m3 + m4) + m5) >= 1073741824)
                                                              Rows Removed by Filter: 24245
                                                        ->  Hash  (cost=646.25..646.25 rows=4233 width=12) (actual time=20.157..20.157 rows=3 loops=1)
                                                              Buckets: 8192  Batches: 1  Memory Usage: 65kB
                                                              ->  Seq Scan on table_2  (cost=0.00..646.25 rows=4233 width=12) (actual time=6.954..20.118 rows=3 loops=1)
                                                                    Filter: (((m3 + m4) + m5) >= 500000)
                                                                    Rows Removed by Filter: 12697
                                            ->  Subquery Scan on *SELECT* 1  (cost=0.00..68809.54 rows=1679577 width=12) (actual time=0.012..1849.942 rows=1679577 loops=1)
                                                  ->  Seq Scan on table_1 at  (cost=0.00..52013.77 rows=1679577 width=12) (actual time=0.008..1256.249 rows=1679577 loops=1)
                          ->  Subquery Scan on *SELECT* 3  (cost=0.00..836.33 rows=12658 width=12) (actual time=24.760..24.760 rows=0 loops=1)
                                ->  Seq Scan on table_2 table_2_1  (cost=0.00..709.75 rows=12658 width=12) (actual time=24.755..24.755 rows=0 loops=1)
                                      Filter: ((((m3 + m4) + m5) > 1500000) OR ((m6 + m7) <> 0))
                                      Rows Removed by Filter: 12700
        ->  Index Only Scan using table_1_pkey on table_1 att  (cost=0.43..7.98 rows=1 width=12) (never executed)
              Index Cond: (matricule = (res.matricule)::text)
              Heap Fetches: 0
Planning time: 1.837 ms
Execution time: 2673.919 ms

gleu

Administrateur

Re : Optimisation requête

Juste pour que je comprenne bien, vous cherchez à optimiser les deux requêtes ? ou vous cherchez à comprendre pourquoi l'une est plus rapide que l'autre ?

---

Guillaume.

dverite

Membre

Re : Optimisation requête

Voici mon plan d'exécution sur une BDD locale qui ne contient pas des données.

On voit bien en quoi les plans d'exécution sont différents, mais là le résultat du count(*) semble être zéro.

Comme les plans d'exécution dépendent entre autres des statistiques sur les tables, ça n'a pas trop d'intérêt d'analyser des plans sur des données dont les volumes ne réflètent pas l'environnement de production.

---

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

Abeidi Yacoub

Membre

Re : Optimisation requête

Juste pour que je comprenne bien, vous cherchez à optimiser les deux requêtes ? ou vous cherchez à comprendre pourquoi l'une est plus rapide que l'autre ?

Bonjour,
Merci a vous tous pour l'interet que vous avez accordé a ma demande.
Mon objectif est d'avoir une requete plus rapide et donne le même résultat.
Ce que j'ai pu faire moi, c'est seulement d'éviter l'utilisation de IN & NOT IN qui peuvent posé un pb si le volume de données est important.
J'ai remplacé IN par INTERSECT, et NOT IN par EXCEPT mais d'après les tests, ce n'a pas apporté une amélioration, même dans la majorité des tests sont plus lentes.