Vous n'êtes pas identifié(e).
Pages : 1
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)
Hors ligne
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/
Hors ligne
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
Hors ligne
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.
Hors ligne
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/
Hors ligne
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.
Hors ligne
Pages : 1
