Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
quand je fais un AXPLAIN ANALYZE de ma requete, j'ai le plan suivant :
HashAggregate (cost=7474.42..7474.68 rows=3 width=211) (actual time=20.994..21.020 rows=15 loops=1)
-> Nested Loop (cost=0.00..7474.22 rows=3 width=211) (actual time=0.683..14.333 rows=1934 loops=1)
-> Append (cost=0.00..7449.31 rows=3 width=202) (actual time=0.655..8.342 rows=1934 loops=1)
-> Index Scan using TABLE_idx0 on TABLE aft (cost=0.00..8.30 rows=1 width=388) (actual time=0.013..0.013 rows=0 loops=1)
Index Cond: (id_a = 206)
Filter: ((ag_date1 >= '2011-10-01 00:00:00'::timestamp without time zone) AND (ag_date1 <= '2011-10-31 23:59:59'::timestamp without time zone) AND (id_b = ANY ('{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}'::bigint[])))
-> Bitmap Heap Scan on par_TABLE_2011_10 aft (cost=39.89..7441.02 rows=2 width=109) (actual time=0.641..8.092 rows=1934 loops=1)
Recheck Cond: (id_a = 206)
Filter: ((ag_date1 >= '2011-10-01 00:00:00'::timestamp without time zone) AND (ag_date1 <= '2011-10-31 23:59:59'::timestamp without time zone) AND (id_b = ANY ('{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}'::bigint[])))
-> Bitmap Index Scan on par_TABLE_2011_10_idx0 (cost=0.00..39.89 rows=2034 width=0) (actual time=0.353..0.353 rows=1934 loops=1)
Index Cond: (id_a = 206)
-> Index Scan using position_pkey on position p (cost=0.00..8.29 rows=1 width=13) (actual time=0.002..0.003 rows=1 loops=1934)
Index Cond: (p.id_pos = aft.id_pos)
Total runtime: 21.223 ms.Je ne comprends pas le comportement du Bitmap Heap Scan.
Je ne comprends pas non plus pourqoui il y a plusieurs check cond.
Je signale que ma table est partitionnée.
Voici la requête:
SELECT currency, id_a, NULL :: bigint, NULL :: character varying, position , NULL :: integer, (SUM(number_of_one) + SUM(number_of_two) + SUM(number_of_zero) + SUM(number_of_three) + SUM(number_of_for)) AS nbr, (SUM(amount_one) + SUM(amount_two) + SUM(amount_three) + SUM(amount_for) + SUM(amount_five)) AS amount, SUM(amount_six), SUM(number_of_five), SUM(amount_seven) , SUM(number_of_six) , SUM(amount_eight) , SUM(number_of_seven) , SUM(amount_nine) FROM Schem.TABLE aft , position p WHERE ag_date1 >= '2011-10-01 00:00:00' AND ag_date1 <= '2011-10-31 23:59:59' AND id_a = 206
AND p.id_b = aft.id_b
AND aft.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' )
AND p.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' )
GROUP BY currency,id_a, position;
.Hors ligne
Il a éliminé toutes les partitions qui n'ont pas ag_date1 >= '2011-10-01 00:00:00' AND ag_date1 <= '2011-10-31 23:59:59', et se contente donc de la partition par_TABLE_2011_10 et de la table parente.
Pour la table parente du partitionnement, il la lit par index sur id_a=206. Il a raison, c'est le plus rapide, vu que ça va ramener 0 enregistrements, donc ça ne coûte quasiment rien.
Pour la table par_TABLE_2011_10, il choisit un bitmap_index_scan. C'est à dire de parcourir l'index intégralement pour construire la liste des blocs disques de la table à aller visiter. Ensuite il passe au bitmap heap scan, c'est à dire qu'il va regarder tous ces blocs. Pour tous ces blocs, il revérifie id_a=206 (le recheck), et il applique les autres filtres de la clause where.
Il fait un append de l'index scan et du bitmap heap scan (c'est fait en même temps, bien sûr, il n'attend pas d'avoir reçu tous les enregistrements).
Au fur et à mesure de cet append, il jointure sur position, via son nested loop. Et évidemment il agrège l'ensemble, comme demandé par le group by.
Où est le problème ? (le plan me semble assez logique vu la requête).
Marc.
Hors ligne
Bitmap Heap Scan on par_TABLE_2011_10 aft (cost=39.89..7441.02 rows=2 width=109) (actual time=0.641..8.092 rows=1934 loops=1)
Recheck Cond: (id_a = 206)
Sur ce bout de code, il s'attend à avoir 2 lignes mais à la fin il a 1934, je me demande si c'est normal.
J'ai lu quelque part que le Heap Scan est pas loin du seq scan.
Le problème est que j'ai une procedure qui vas boucler n fois sur ce type de requetes, cette procedure a en paramètre un tableau d'id_a, et un tableau d'id_b ainsi que les autres filtres.
Pour executer la requete je faits RETURN QUERY EXECUTE ma_chane.
ma_chaine contient la requete (SQLD).
Mon temps d'execution dépasse les 4mn, j'ai donc analyser une étape de la boucle, por voir comment je peux gagner du temps.
Hors ligne
Non, ce n'est pas vraiment «normal» qu'il y ait une erreur d'estimation de ce genre. Il se trompe vraisemblablement sur l'estimation de la sélectivité du filtre du bitmap heap scan. Mais de toutes façons le bitmap index scan + bitmap heap scan est probablement la meilleure stratégie tout de même…
Vous pouvez essayer de jouer sur les critères du filtre pour trouver ce qui l'induit en erreur. Peut-être le ANY ? Par ailleurs, c'est quelle version de PG ?
Marc.
Hors ligne
C'est la 8.4
Hors ligne
Oups! j'ai enlevé un commentaire de la requete, je la corrige:
SELECT currency, id_a, NULL :: bigint, NULL :: character varying, position , NULL :: integer, (SUM(number_of_one) + SUM(number_of_two) + SUM(number_of_zero) + SUM(number_of_three) + SUM(number_of_for)) AS nbr, (SUM(amount_one) + SUM(amount_two) + SUM(amount_three) + SUM(amount_for) + SUM(amount_five)) AS amount, SUM(amount_six), SUM(number_of_five), SUM(amount_seven) , SUM(number_of_six) , SUM(amount_eight) , SUM(number_of_seven) , SUM(amount_nine) FROM Schem.TABLE aft , position p WHERE ag_date1 >= '2011-10-01 00:00:00' AND ag_date1 <= '2011-10-31 23:59:59' AND id_a = 206
AND p.id_b = aft.id_b
AND aft.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' )
--AND p.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' )
GROUP BY currency,id_a, position;.Dernière modification par Postgres.0 (09/03/2012 13:10:28)
Hors ligne
la table p est beaucoup plus petite que la table aft.
Quand je filtre sur
p.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' ).l'anomalie disparait, pensez vous que c'est à cause de ça.
Sinon avez une proposition pour que je puisse gagner en tèrme de temps d'exécution.
Hors ligne
C'est un des problèmes effectivement, il ne déduit pas de p.id_b=aft.id_b and aft.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' ) que p.id_b = ANY( '{1442,1436,1440,1441,1443,1452,1451,1435,1449,1450,1439,1444,1446,1438,1437,1445,1448,1447}' )
PostgreSQL ne sait le faire (en tout cas pour le moment, mais je ne suis pas au courant de plans pour améliorer ça) que dans le cas où la colonne est égale à une constante (on aurait aft.id_b = 1442, il saurait déduire p.id_b = 1442)
Par contre, je ne vois pas comment ça fait disparaitre l'anomalie. Être plus rapide, probablement, vu que vous lui donnez plus d'informations. Vous pouvez poster le nouveau plan ?
Marc.
Hors ligne
vous avez parafaitement raison, ça ne changera rien.
L'analyse que j'ai faites étais sur la requete déjà dans le cache.
J'aimerai quand même bien savoir comment je corrige cette anomalie car elle me permettera de gagner en temps d'exécutiion.
Hors ligne
Sinon avez vous un lien qui peut m'aider à comprendre techniquement ces mots:
HashAggregate,Hash Join,Hash Cond,Append,Index Cond,Bitmap Heap Scan,Recheck Cond,Filter, Hash.
J'ai vraiment en vie de comprendre comment l'optimiseur fonctionne.
Dernière modification par Postgres.0 (09/03/2012 15:07:08)
Hors ligne
Non, ce n'est pas une «anomalie», simplement une mauvaise estimation du nombre d'enregistrements retournées. Comme le plan généré n'est pas absurde, ça n'aura que peu d'impact. À la rigueur, il pourrait choisir de ne pas faire un nested loop sur position_pkey. Mais comme vous l'avez dit, la table est petite… elle sera donc très rapidement en cache, et ça sera donc le bon plan.
Le bitmap scan est le bon plan par rapport à ce que vous voulez faire. Le fait que le «filter» enlève beaucoup d'enregistrements ne change rien au fait que id_a = 206, en tout cas d'après l'optimiseur, est une valeur assez fréquente dans la table. Et qu'il est donc plus rentable pour lui de partir sur un bitmap index scan que sur un index scan classique (qui lui est bon quand très peu d'enregistrements sont ramenés).
Si vous voulez comprendre ces choses, dans le contexte PostgreSQL, vous avez quelques sources valables: https://www.pgcon.org/2010/schedule/events/208.en.html (vous avez la conf audio, et les slides qui vont avec doivent pouvoir se trouver), et sinon, vous avez le «PostgreSQL High Performance» de Greg Smith (traduit en français si vous le préférez) qui a tout un chapitre sur le sujet.
Marc.
Hors ligne
Merci, beaucoup!
Hors ligne
Pour infos, les slides se trouvent sur http://sites.google.com/site/robertmhaas/presentations (avec toutes les autres confs de Robert Haas).
Guillaume.
Hors ligne
Pages : 1