Optimisation d'une requête sans désactiver nested loop

ced · 16/02/2018 18:28:38

Bonjour,

J'exécute la requête suivante :

SELECT f.npp, p1.nph
    , f.numboite 
    , CASE WHEN p1.incref < 10 THEN 2010 + p1.incref ELSE 2005 + p1.incref END AS campagne
    , p1.dep
    , ep2.pro_nm, mpro.libelle AS lib_pro_nm
    , p1.pro, mpr.libelle AS lib_pro
    , p1.zp
    , p1.ser_86, mser.libelle AS lib_ser_86
    , LEFT(p1.ser_86, 1) AS greco
    , ec.pent2
    , ec.topo, mtop.libelle AS lib_topo
    , ec.humus, mhum.libelle AS lib_humus
--    , humg1.mode AS humusg1, humg1.libelle AS lib_humusg1
    , ec.affroc
    , ec.roche, mroc.libelle AS lib_roche
    , eec.rayo
    , eec.reserutile 
--    , 
FROM inv_prod.e1point p1
INNER JOIN inv_prod.g3foret f ON p1.npp = f.npp
INNER JOIN inv_prod.e2point p2 ON p1.npp = p2.npp
LEFT JOIN inv_exp_nm.e2point ep2 ON p1.npp = ep2.npp
INNER JOIN inv_prod.g3ecologie ec ON p1.npp = ec.npp
LEFT JOIN inv_exp_nm.g3ecologie eec ON p1.npp = eec.npp
LEFT JOIN metaifn.abmode mpro ON mpro.unite = 'PRO_2015' AND ep2.pro_nm = mpro.mode
LEFT JOIN metaifn.abmode mpr ON mpr.unite = 'PF' AND p1.pro = mpr.mode
INNER JOIN metaifn.abmode mser ON mser.unite = 'SER_86' AND p1.ser_86 = mser.mode
INNER JOIN metaifn.abmode mtop ON mtop.unite = 'TOPO' AND ec.topo = mtop.mode
INNER JOIN metaifn.abmode mhum ON mhum.unite = 'HUMUS' AND ec.humus = mhum.mode
INNER JOIN metaifn.abgroupe ghum ON ghum.unite = mhum.unite AND ghum.gunite = 'HUMUSD1' AND ghum.mode = mhum.mode 
--INNER JOIN metaifn.abmode humg1 ON ghum.gunite = humg1.unite AND humg1.mode = ghum.gmode
LEFT JOIN metaifn.abmode mroc ON mroc.unite = 'ROCHE' AND ec.roche = mroc.mode
WHERE f.numboite IS NOT NULL
ORDER BY campagne, npp;

Son exécution prend environ 40 secondes, et voici le résultat d'un EXPLAIN (ANALYZE) :

QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=110397.51..110397.51 rows=1 width=216) (actual time=60972.179..60972.645 rows=5431 loops=1)
Sort Key: (CASE WHEN (p1.incref < 10) THEN (2010 + p1.incref) ELSE (2005 + p1.incref) END), f.npp
Sort Method: quicksort Memory: 1676kB
-> Nested Loop Left Join (cost=32184.76..110397.50 rows=1 width=216) (actual time=2667.623..60939.240 rows=5431 loops=1)
Join Filter: (ec.roche = (mroc.mode)::bpchar)
Rows Removed by Join Filter: 896126
-> Nested Loop (cost=32084.76..102396.01 rows=1 width=163) (actual time=2667.019..55562.972 rows=5431 loops=1)
Join Filter: (ec.topo = (mtop.mode)::bpchar)
Rows Removed by Join Filter: 48879
-> Nested Loop (cost=31984.76..94399.45 rows=1 width=144) (actual time=2666.581..55548.509 rows=5431 loops=1)
Join Filter: (p1.ser_86 = (mser.mode)::bpchar)
Rows Removed by Join Filter: 461635
-> Nested Loop (cost=31884.76..86402.89 rows=1 width=125) (actual time=2665.859..55458.842 rows=5431 loops=1)
Join Filter: ((mhum.mode)::bpchar = ec.humus)
Rows Removed by Join Filter: 97758
-> Foreign Scan (cost=100.00..20313.31 rows=1 width=24) (actual time=1.261..1.266 rows=19 loops=1)
Relations: (metaifn.abmode mhum) INNER JOIN (metaifn.abgroupe ghum)
-> Hash Left Join (cost=31784.76..66021.69 rows=5431 width=106) (actual time=742.608..2917.762 rows=5431 loops=19)
Hash Cond: (p1.pro = (mpr.mode)::bpchar)
-> Hash Left Join (cost=23788.20..57942.16 rows=5431 width=87) (actual time=742.582..2916.633 rows=5431 loops=19)
Hash Cond: (ep2.pro_nm = (mpro.mode)::bpchar)
-> Hash Right Join (cost=15791.63..49864.12 rows=5431 width=68) (actual time=742.551..2914.761 rows=5431 loops=19)
Hash Cond: (ep2.npp = p1.npp)
-> Foreign Scan on e2point ep2 (cost=100.00..31860.92 rows=608964 width=20) (actual time=0.903..2792.405 rows=608964 loops=19)
-> Hash (cost=15623.75..15623.75 rows=5431 width=82) (actual time=494.773..494.773 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 740kB
-> Hash Right Join (cost=11104.09..15623.75 rows=5431 width=82) (actual time=91.340..492.405 rows=5431 loops=1)
Hash Cond: (eec.npp = p1.npp)
-> Foreign Scan on g3ecologie eec (cost=100.00..4324.86 rows=77662 width=27) (actual time=2.033..388.705 rows=77662 loops=1)
-> Hash (cost=10936.21..10936.21 rows=5431 width=72) (actual time=89.255..89.255 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 652kB
-> Gather (cost=2908.64..10936.21 rows=5431 width=72) (actual time=26.653..87.045 rows=5431 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (cost=1908.64..9393.11 rows=3195 width=72) (actual time=20.355..79.790 rows=2716 loops=2)
Join Filter: (f.npp = p1.npp)
-> Nested Loop (cost=1908.21..7289.70 rows=3195 width=66) (actual time=20.332..50.710 rows=2716 loops=2)
Join Filter: (f.npp = p2.npp)
-> Hash Join (cost=1907.79..3850.26 rows=3195 width=49) (actual time=20.293..23.829 rows=2716 loops=2)
Hash Cond: (ec.npp = f.npp)
-> Parallel Seq Scan on g3ecologie ec (cost=0.00..1740.47 rows=45347 width=30) (actual time=0.005..4.617 rows=38545 loops=2)
-> Hash (cost=1839.90..1839.90 rows=5431 width=19) (actual time=12.507..12.507 rows=5431 loops=2)
Buckets: 8192 Batches: 1 Memory Usage: 340kB
-> Seq Scan on g3foret f (cost=0.00..1839.90 rows=5431 width=19) (actual time=0.695..10.714 rows=5431 loops=2)
Filter: (numboite IS NOT NULL)
Rows Removed by Filter: 71659
-> Index Only Scan using pke2point on e2point p2 (cost=0.42..1.06 rows=1 width=17) (actual time=0.009..0.009 rows=1 loops=5431)
Index Cond: (npp = ec.npp)
Heap Fetches: 2680
-> Index Scan using pke1point on e1point p1 (cost=0.43..0.65 rows=1 width=40) (actual time=0.010..0.010 rows=1 loops=5431)
Index Cond: (npp = p2.npp)
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.510..0.510 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on abmode mpro (cost=100.00..7996.44 rows=10 width=24) (actual time=0.501..0.503 rows=7 loops=1)
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.434..0.434 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on abmode mpr (cost=100.00..7996.44 rows=10 width=24) (actual time=0.420..0.420 rows=5 loops=1)
-> Foreign Scan on abmode mser (cost=100.00..7996.44 rows=10 width=24) (actual time=0.000..0.005 rows=86 loops=5431)
-> Foreign Scan on abmode mtop (cost=100.00..7996.44 rows=10 width=24) (actual time=0.000..0.001 rows=10 loops=5431)
-> Foreign Scan on abmode mroc (cost=100.00..7999.46 rows=161 width=24) (actual time=0.447..0.778 rows=166 loops=5431)
Planning time: 14.210 ms
Execution time: 61004.853 ms
(62 lignes)

Devant la durée, j'ai désactivé les boucles (nested loop).

La requête prend alors 3 secondes pour s'exécuter et voici le plan d'exécution :

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=155498.14..155498.14 rows=1 width=216) (actual time=3089.724..3090.152 rows=5431 loops=1)
Sort Key: (CASE WHEN (p1.incref < 10) THEN (2010 + p1.incref) ELSE (2005 + p1.incref) END), f.npp
Sort Method: quicksort Memory: 1676kB
-> Hash Left Join (cost=121240.52..155498.13 rows=1 width=216) (actual time=1499.085..3061.587 rows=5431 loops=1)
Hash Cond: (ec.roche = (mroc.mode)::bpchar)
-> Hash Join (cost=113239.05..147496.43 rows=1 width=163) (actual time=1498.167..3056.377 rows=5431 loops=1)
Hash Cond: (ec.topo = (mtop.mode)::bpchar)
-> Hash Join (cost=105242.49..139499.84 rows=1 width=144) (actual time=1497.530..3053.863 rows=5431 loops=1)
Hash Cond: (p1.ser_86 = (mser.mode)::bpchar)
-> Hash Join (cost=97245.92..131503.26 rows=1 width=125) (actual time=1496.821..3051.040 rows=5431 loops=1)
Hash Cond: (ec.humus = (mhum.mode)::bpchar)
-> Hash Left Join (cost=76932.60..111169.54 rows=5431 width=106) (actual time=1495.633..3047.981 rows=5431 loops=1)
Hash Cond: (p1.pro = (mpr.mode)::bpchar)
-> Hash Left Join (cost=68936.04..103090.00 rows=5431 width=87) (actual time=1495.220..3046.425 rows=5431 loops=1)
Hash Cond: (ep2.pro_nm = (mpro.mode)::bpchar)
-> Hash Right Join (cost=60939.48..95011.96 rows=5431 width=68) (actual time=1494.643..3043.939 rows=5431 loops=1)
Hash Cond: (ep2.npp = p1.npp)
-> Foreign Scan on e2point ep2 (cost=100.00..31860.92 rows=608964 width=20) (actual time=0.808..1960.520 rows=608964 loops=1)
-> Hash (cost=60771.59..60771.59 rows=5431 width=82) (actual time=984.847..984.847 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 740kB
-> Hash Join (cost=42823.62..60771.59 rows=5431 width=82) (actual time=819.981..982.527 rows=5431 loops=1)
Hash Cond: (p2.npp = p1.npp)
-> Seq Scan on e2point p2 (cost=0.00..15382.51 rows=675851 width=17) (actual time=0.051..71.541 rows=675851 loops=1)
-> Hash (cost=42755.73..42755.73 rows=5431 width=99) (actual time=817.746..817.746 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 825kB
-> Hash Right Join (cost=38236.08..42755.73 rows=5431 width=99) (actual time=424.647..815.196 rows=5431 loops=1)
Hash Cond: (eec.npp = p1.npp)
-> Foreign Scan on g3ecologie eec (cost=100.00..4324.86 rows=77662 width=27) (actual time=2.018..376.510 rows=77662 loops=1)
-> Hash (cost=38068.19..38068.19 rows=5431 width=89) (actual time=422.560..422.560 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 742kB
-> Gather (cost=5376.97..38068.19 rows=5431 width=89) (actual time=173.080..419.779 rows=5431 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=4376.97..36525.09 rows=1358 width=89) (actual time=175.150..379.921 rows=1086 loops=5)
Hash Cond: (p1.npp = f.npp)
-> Parallel Seq Scan on e1point p1 (cost=0.00..31025.21 rows=295821 width=40) (actual time=3.167..119.309 rows=236657 loops=5)
-> Hash (cost=4309.09..4309.09 rows=5431 width=49) (actual time=156.633..156.633 rows=5431 loops=5)
Buckets: 8192 Batches: 1 Memory Usage: 499kB
-> Hash Join (cost=1907.79..4309.09 rows=5431 width=49) (actual time=115.947..148.795 rows=5431 loops=5)
Hash Cond: (ec.npp = f.npp)
-> Seq Scan on g3ecologie ec (cost=0.00..2057.90 rows=77090 width=30) (actual time=3.085..51.858 rows=77090 loops=5)
-> Hash (cost=1839.90..1839.90 rows=5431 width=19) (actual time=44.989..44.989 rows=5431 loops=5)
Buckets: 8192 Batches: 1 Memory Usage: 340kB
-> Seq Scan on g3foret f (cost=0.00..1839.90 rows=5431 width=19) (actual time=2.588..39.368 rows=5431 loops=5)
Filter: (numboite IS NOT NULL)
Rows Removed by Filter: 71659
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.547..0.547 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on abmode mpro (cost=100.00..7996.44 rows=10 width=24) (actual time=0.540..0.541 rows=7 loops=1)
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.401..0.401 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on abmode mpr (cost=100.00..7996.44 rows=10 width=24) (actual time=0.396..0.396 rows=5 loops=1)
-> Hash (cost=20313.31..20313.31 rows=1 width=24) (actual time=1.178..1.178 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan (cost=100.00..20313.31 rows=1 width=24) (actual time=1.168..1.170 rows=19 loops=1)
Relations: (metaifn.abmode mhum) INNER JOIN (metaifn.abgroupe ghum)
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.698..0.698 rows=86 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Foreign Scan on abmode mser (cost=100.00..7996.44 rows=10 width=24) (actual time=0.668..0.681 rows=86 loops=1)
-> Hash (cost=7996.44..7996.44 rows=10 width=24) (actual time=0.627..0.627 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Foreign Scan on abmode mtop (cost=100.00..7996.44 rows=10 width=24) (actual time=0.618..0.618 rows=10 loops=1)
-> Hash (cost=7999.46..7999.46 rows=161 width=24) (actual time=0.896..0.896 rows=166 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 17kB
-> Foreign Scan on abmode mroc (cost=100.00..7999.46 rows=161 width=24) (actual time=0.570..0.852 rows=166 loops=1)
Planning time: 14.370 ms
Execution time: 3093.657 ms
(67 lignes)

Dans le plan initial, il me semble que c'est au niveau de ce nœud que ça coince :

-> Foreign Scan (cost=100.00..20313.31 rows=1 width=24) (actual time=1.261..1.266 rows=19 loops=1)
Relations: (metaifn.abmode mhum) INNER JOIN (metaifn.abgroupe ghum)

En effet, le planificateur voit une seule ligne là où il y en a en réalité 19, et du coup il privilégie à tort le nested loop.
J'ai pourtant fait un ANALYZE de toutes mes tables (y compris étrangères) pour avoir des stats le plus à jour possible...

Voyez-vous une explication et un moyen d'améliorer le plan d'exécution, sans désactiver le paramètre enable_nestloop ?
Je précise que cette requête sera utilisée pour créer une vue. Il faut donc que son temps d'exécution reste acceptable (surtout vu le nombre de lignes en jeu)...

Merci d'avance pour votre aide et pardon pour la longueur du message,

Cédric

Dernière modification par ced (19/02/2018 11:34:42)

ced · 16/02/2018 19:26:24

J'arrive à contourner le problème en réécrivant la requête comme ça :

WITH meta AS (
    SELECT unite, mode, libelle
    FROM metaifn.abmode 
    WHERE unite IN ('PRO_2015', 'PF', 'SER_86', 'TOPO', 'HUMUS', 'HUMUSD1', 'ROCHE')
)
SELECT f.npp, p1.nph
    , f.numboite 
    , CASE WHEN p1.incref < 10 THEN 2010 + p1.incref ELSE 2005 + p1.incref END AS campagne
    , p1.dep
    , ep2.pro_nm, mpro.libelle AS lib_pro_nm
    , p1.pro, mpr.libelle AS lib_pro
    , p1.zp
    , p1.ser_86, mser.libelle AS lib_ser_86
    , LEFT(p1.ser_86, 1) AS greco
    , ec.pent2
    , ec.topo, mtop.libelle AS lib_topo
    , ec.humus, mhum.libelle AS lib_humus
--    , humg1.mode AS humusg1, humg1.libelle AS lib_humusg1
    , ec.affroc
    , ec.roche, mroc.libelle AS lib_roche
    , eec.rayo
    , eec.reserutile 
--    , 
FROM inv_prod.e1point p1
INNER JOIN inv_prod.g3foret f ON p1.npp = f.npp
INNER JOIN inv_prod.e2point p2 ON p1.npp = p2.npp
LEFT JOIN inv_exp_nm.e2point ep2 ON p1.npp = ep2.npp
INNER JOIN inv_prod.g3ecologie ec ON p1.npp = ec.npp
LEFT JOIN inv_exp_nm.g3ecologie eec ON p1.npp = eec.npp
LEFT JOIN meta mpro ON mpro.unite = 'PRO_2015' AND ep2.pro_nm = mpro.mode
LEFT JOIN meta mpr ON mpr.unite = 'PF' AND p1.pro = mpr.mode
INNER JOIN meta mser ON mser.unite = 'SER_86' AND p1.ser_86 = mser.mode
INNER JOIN meta mtop ON mtop.unite = 'TOPO' AND ec.topo = mtop.mode
INNER JOIN meta mhum ON mhum.unite = 'HUMUS' AND ec.humus = mhum.mode
INNER JOIN metaifn.abgroupe ghum ON ghum.unite = mhum.unite AND ghum.gunite = 'HUMUSD1' AND ghum.mode = mhum.mode 
--INNER JOIN metaifn.abmode humg1 ON ghum.gunite = humg1.unite AND humg1.mode = ghum.gmode
LEFT JOIN meta mroc ON mroc.unite = 'ROCHE' AND ec.roche = mroc.mode
WHERE f.numboite IS NOT NULL
ORDER BY campagne, npp;

Le plan d'exécution associé devient alors le suivant :

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=117298.61..117298.62 rows=1 width=930) (actual time=2990.373..2990.829 rows=5431 loops=1)
Sort Key: (CASE WHEN (p1.incref < 10) THEN (2010 + p1.incref) ELSE (2005 + p1.incref) END), f.npp
Sort Method: quicksort Memory: 1676kB
CTE meta
-> Foreign Scan on abmode (cost=100.00..9778.22 rows=218 width=30) (actual time=20.849..21.951 rows=300 loops=1)
-> Hash Left Join (cost=73385.98..107520.39 rows=1 width=930) (actual time=1394.691..2962.191 rows=5431 loops=1)
Hash Cond: (ec.roche = (mroc.mode)::bpchar)
-> Hash Join (cost=73381.06..107515.44 rows=1 width=758) (actual time=1394.569..2957.905 rows=5431 loops=1)
Hash Cond: (ec.topo = (mtop.mode)::bpchar)
-> Hash Join (cost=73376.15..107510.51 rows=1 width=620) (actual time=1372.489..2934.007 rows=5431 loops=1)
Hash Cond: (p1.ser_86 = (mser.mode)::bpchar)
-> Hash Join (cost=73371.23..107505.58 rows=1 width=482) (actual time=1372.407..2931.824 rows=5431 loops=1)
Hash Cond: (ec.humus = (mhum.mode)::bpchar)
-> Hash Left Join (cost=60949.31..95063.03 rows=5431 width=344) (actual time=1371.819..2929.411 rows=5431 loops=1)
Hash Cond: (p1.pro = (mpr.mode)::bpchar)
-> Hash Left Join (cost=60944.40..95037.52 rows=5431 width=206) (actual time=1371.770..2928.333 rows=5431 loops=1)
Hash Cond: (ep2.pro_nm = (mpro.mode)::bpchar)
-> Hash Right Join (cost=60939.48..95011.96 rows=5431 width=68) (actual time=1371.694..2926.142 rows=5431 loops=1)
Hash Cond: (ep2.npp = p1.npp)
-> Foreign Scan on e2point ep2 (cost=100.00..31860.92 rows=608964 width=20) (actual time=0.835..1966.443 rows=608964 loops=1)
-> Hash (cost=60771.59..60771.59 rows=5431 width=82) (actual time=864.189..864.189 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 740kB
-> Hash Join (cost=42823.62..60771.59 rows=5431 width=82) (actual time=697.044..861.788 rows=5431 loops=1)
Hash Cond: (p2.npp = p1.npp)
-> Seq Scan on e2point p2 (cost=0.00..15382.51 rows=675851 width=17) (actual time=0.043..73.762 rows=675851 loops=1)
-> Hash (cost=42755.73..42755.73 rows=5431 width=99) (actual time=694.655..694.655 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 825kB
-> Hash Right Join (cost=38236.08..42755.73 rows=5431 width=99) (actual time=300.793..691.962 rows=5431 loops=1)
Hash Cond: (eec.npp = p1.npp)
-> Foreign Scan on g3ecologie eec (cost=100.00..4324.86 rows=77662 width=27) (actual time=2.411..378.582 rows=77662 loops=1)
-> Hash (cost=38068.19..38068.19 rows=5431 width=89) (actual time=298.327..298.327 rows=5431 loops=1)
Buckets: 8192 Batches: 1 Memory Usage: 742kB
-> Gather (cost=5376.97..38068.19 rows=5431 width=89) (actual time=81.160..295.911 rows=5431 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=4376.97..36525.09 rows=1358 width=89) (actual time=112.967..263.113 rows=1086 loops=5)
Hash Cond: (p1.npp = f.npp)
-> Parallel Seq Scan on e1point p1 (cost=0.00..31025.21 rows=295821 width=40) (actual time=1.780..105.096 rows=236657 loops=5)
-> Hash (cost=4309.09..4309.09 rows=5431 width=49) (actual time=84.918..84.918 rows=5431 loops=5)
Buckets: 8192 Batches: 1 Memory Usage: 499kB
-> Hash Join (cost=1907.79..4309.09 rows=5431 width=49) (actual time=63.472..77.886 rows=5431 loops=5)
Hash Cond: (ec.npp = f.npp)
-> Seq Scan on g3ecologie ec (cost=0.00..2057.90 rows=77090 width=30) (actual time=1.060..14.484 rows=77090 loops=5)
-> Hash (cost=1839.90..1839.90 rows=5431 width=19) (actual time=29.048..29.048 rows=5431 loops=5)
Buckets: 8192 Batches: 1 Memory Usage: 340kB
-> Seq Scan on g3foret f (cost=0.00..1839.90 rows=5431 width=19) (actual time=1.681..27.894 rows=5431 loops=5)
Filter: (numboite IS NOT NULL)
Rows Removed by Filter: 71659
-> Hash (cost=4.91..4.91 rows=1 width=180) (actual time=0.049..0.049 rows=7 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on meta mpro (cost=0.00..4.91 rows=1 width=180) (actual time=0.010..0.043 rows=7 loops=1)
Filter: ((unite)::text = 'PRO_2015'::text)
Rows Removed by Filter: 293
-> Hash (cost=4.91..4.91 rows=1 width=180) (actual time=0.039..0.039 rows=5 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on meta mpr (cost=0.00..4.91 rows=1 width=180) (actual time=0.005..0.035 rows=5 loops=1)
Filter: ((unite)::text = 'PF'::text)
Rows Removed by Filter: 295
-> Hash (cost=12421.90..12421.90 rows=1 width=180) (actual time=0.579..0.579 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Hash Join (cost=104.92..12421.90 rows=1 width=180) (actual time=0.564..0.572 rows=19 loops=1)
Hash Cond: ((ghum.mode)::text = (mhum.mode)::text)
-> Foreign Scan on abgroupe ghum (cost=100.00..12416.97 rows=1 width=10) (actual time=0.491..0.491 rows=19 loops=1)
-> Hash (cost=4.91..4.91 rows=1 width=222) (actual time=0.044..0.044 rows=19 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on meta mhum (cost=0.00..4.91 rows=1 width=222) (actual time=0.001..0.039 rows=19 loops=1)
Filter: ((unite)::text = 'HUMUS'::text)
Rows Removed by Filter: 281
-> Hash (cost=4.91..4.91 rows=1 width=180) (actual time=0.066..0.066 rows=86 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> CTE Scan on meta mser (cost=0.00..4.91 rows=1 width=180) (actual time=0.024..0.044 rows=86 loops=1)
Filter: ((unite)::text = 'SER_86'::text)
Rows Removed by Filter: 214
-> Hash (cost=4.91..4.91 rows=1 width=180) (actual time=22.067..22.067 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> CTE Scan on meta mtop (cost=0.00..4.91 rows=1 width=180) (actual time=21.894..22.061 rows=10 loops=1)
Filter: ((unite)::text = 'TOPO'::text)
Rows Removed by Filter: 290
-> Hash (cost=4.91..4.91 rows=1 width=180) (actual time=0.095..0.095 rows=166 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 17kB
-> CTE Scan on meta mroc (cost=0.00..4.91 rows=1 width=180) (actual time=0.006..0.053 rows=166 loops=1)
Filter: ((unite)::text = 'ROCHE'::text)
Rows Removed by Filter: 134
Planning time: 7.815 ms
Execution time: 2993.754 ms
(85 lignes)

Cette fois-ci, dans la CTE, le nombre de lignes pour la jointure qui coinçait auparavant est juste : 19 lignes. Et donc, pas de nested loop...

Dernière modification par ced (19/02/2018 11:44:36)

gleu · 16/02/2018 23:02:11

Comme vous désactivez l'affichage des informations de l'optimiseur (COSTS OFF), ça va être difficile d'interpréter pourquoi il choisit un Nested Loop.

De plus, l'affichage du résultat du EXPLAIN est optimisé certainement par un psqlrc sympathique, mais ça empêche de fournir le plan à explain.depesz.com, ce qui est assez ballot si on veut rechercher le problème de cette requête.

Bref, activez les coûts, utilisez un affichage normal, et on pourra peut être vous aider

ced · 19/02/2018 11:39:02

Bonjour Guillaume,

Merci de bien vouloir m'aider et pardon pour la mauvaise mise en forme.
J'ai réédité mon premier message pour mettre la sortie d'un EXPLAIN avec les coûts.
J'en ai profité pour indiquer la ligne sur laquelle, je pense, une mauvaise estimation de nombre de ligne est faite, ce qui explique pourquoi le nested loop est privilégié à tort.

Comme ce sont des tables étrangères, le problème peut-il venir de là ? J'ai pourtant fait un ANALYZE de ces tables. Mais apparemment, les statistiques ne sont pas tout à fait justes, puisque le nombre de lignes est sous-estimé (1 vs 19 en réalité). Ça expliquerait aussi pourquoi la requête avec la CTE est plus efficace : le nombre de lignes estimées à l'issue de la CTE pour la même jointure est correct (19 lignes) et c'est alors un hash join qui est fait, plus efficace dans le cas présent.
Comment est-ce que je peux faire pour améliorer ces stats sur la table étrangère (si toutefois le problème vient bien de là) ?

Encore merci pour l'aide apportée.

Cédric

Dernière modification par ced (19/02/2018 11:41:03)

gleu · 19/02/2018 12:47:32

La différence dans le nombre de lignes n'est pas énorme mais elle aurait certainement influencé entre un Nested Loop et autre chose. En l'occurence, vous ne pouvez pas faire grand chose pour améliorer cette statistique. En effet, il ne s'agit pas d'une statistique sur le contenu d'une table étrangère, mais sur le résultat d'une jointure entre deux tables étrangères.

La CTE est plus rapide car elle agit comme une barrière à cette optimisation de faire la jointure à distance. Du coup, il n'y a qu'une lecture de la table à distance, les stats ont toujours la même erreur (1 ligne contre 19), mais le Nested Loop n'est plus considéré, ce qui permet d'avoir exactement la même réaction que avec enable_nestloop à off. En gros, pour moi, le mieux est la CTE, en documentant bien pourquoi une CTE est réalisée.

ced · 19/02/2018 13:44:26

Merci pour l'explication.
Effectivement, les stats sont bien identiques. Je vais rester sur la CTE faute de mieux.

Bonne journée,

Cédric

ced · 20/02/2018 11:36:37

J'ai creusé ce problème d'estimation du nombre de lignes. C'est un problème de sélectivité de l'optimiseur qui se résout très bien avec les statistiques multi-colonnes de la v10. En créant une statistique sur les colonnes GUNITE, UNITE, la sélectivité de la requête en question remonte à 19 et le nested loop n'est plus choisi par défaut par l'optimiseur.
Ça me fait une bonne raison supplémentaire pour argumenter une montée de version.

Encore merci pour l'aide apportée.

Dernière modification par ced (20/02/2018 11:36:53)

gleu · 20/02/2018 11:39:11

Merci pour le retour, c'est intéressant.

ced · 20/02/2018 12:09:22

En attendant le passage à la v10, j'ai contourné le problème en créant la vue à partir d'une fonction SQL qui exécute la requête et désactive le nested loop localement à la fonction (surcharge de enable_nestloop).
Ça fonctionne très bien : le temps d'exécution de la requête sur la vue redescend à 3 secondes, ce qui est parfaitement acceptable. Et aucun impact sur le serveur puisque je ne modifie aucun paramètre global.

#1 16/02/2018 18:28:38

Optimisation d'une requête sans désactiver nested loop

#2 16/02/2018 19:26:24

Re : Optimisation d'une requête sans désactiver nested loop

#3 16/02/2018 23:02:11

Re : Optimisation d'une requête sans désactiver nested loop

#4 19/02/2018 11:39:02

Re : Optimisation d'une requête sans désactiver nested loop

#5 19/02/2018 12:47:32

Re : Optimisation d'une requête sans désactiver nested loop

#6 19/02/2018 13:44:26

Re : Optimisation d'une requête sans désactiver nested loop

#7 20/02/2018 11:36:37

Re : Optimisation d'une requête sans désactiver nested loop

#8 20/02/2018 11:39:11

Re : Optimisation d'une requête sans désactiver nested loop

#9 20/02/2018 12:09:22

Re : Optimisation d'une requête sans désactiver nested loop

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux