Postgres 844 : Explain ncorrect

F.Chanson · 23/12/2010 11:41:34

Bonjour,
je souhaite exploiter, via un programme java, le résultat d'un explain pour des raisons de performances (sans la clause analyse) , plutôt que celui d'un "count" lors d'un select et le pb est que l'explain ne donne pas les résultats attendus (92 résultats via un count et 866 avec l'explain), en général je constate une facteur 10

Y-t-il un moyen améliorer ..le résultat de l'explain

la BD 844 postgres comporte 1Million de rows

Mes statistiques sont à 500 sur toutes les tables (default_statistics_target = 500)

Merci

Voici le select
----------------
select
dgproductf0_.id as col_0_0_
from
DG_PRODUCT dgproductf0_
inner join FEATURE dgproductf0_1_ on dgproductf0_.id=dgproductf0_1_.id
inner join MD_METADATA metadatas1_ on dgproductf0_.id=metadatas1_.product
inner join FEATURE metadatas1_1_ on metadatas1_.id=metadatas1_1_.id
inner join METADATA_ID metadataid5_ on metadatas1_1_.fk_metadataID=metadataid5_.id
inner join MD_METADATA metadatas2_ on dgproductf0_.id=metadatas2_.product
inner join FEATURE metadatas2_1_ on metadatas2_.id=metadatas2_1_.id
inner join CATALOG_ID catalogide3_ on metadatas2_.fk_catalogID=catalogide3_.ID
inner join IMAGE_EXTENSION imagefeatu4_ on metadatas2_.fk_image_extension=imagefeatu4_.id
where
catalogide3_.profile='LEVEL0'
and metadatas2_.attribute_description='DATASTRIP' and
dgproductf0_.archive_searchByAllUsers='true' and
(imagefeatu4_.orbit between '12000' and '12100') and
(metadataid5_.EXTERNAL_ID like 'DS%')
group by dgproductf0_.id

et le résultat de l'explain
------------------------------------------
"HashAggregate (cost=13859.60..13868.26 rows=866 width=37) (actual time=51.707..51.766 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13857.44 rows=866 width=37) (actual time=0.441..51.586 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13833.87 rows=58 width=109) (actual time=0.426..50.352 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13752.29 rows=58 width=145) (actual time=0.414..49.090 rows=92 loops=1)"
" Join Filter: ((metadatas2_.fk_catalogid)::text = (catalogide3_.id)::text)"
" -> Index Scan using ib_t_catalogid_f_profile on catalog_id catalogide3_ (cost=0.00..6.27 rows=1 width=118) (actual time=0.009..0.010 rows=1 loops=1)"
" Index Cond: ((profile)::text = 'LEVEL0'::text)"
" -> Nested Loop (cost=22.08..13745.29 rows=58 width=181) (actual time=0.401..48.891 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..13379.61 rows=892 width=218) (actual time=0.219..35.159 rows=1046 loops=1)"
" -> Nested Loop (cost=22.08..13016.89 rows=892 width=217) (actual time=0.205..20.162 rows=1046 loops=1)"
" -> Nested Loop (cost=22.08..8715.53 rows=957 width=145) (actual time=0.179..15.676 rows=92 loops=1)"
" -> Nested Loop (cost=22.08..7369.40 rows=957 width=108) (actual time=0.154..14.209 rows=92 loops=1)"
" -> Bitmap Heap Scan on image_extension imagefeatu4_ (cost=22.08..2089.86 rows=957 width=37) (actual time=0.131..0.944 rows=1046 loops=1)"
" Recheck Cond: ((orbit >= 12000) AND (orbit <= 12100))"
" -> Bitmap Index Scan on ib_t_image_extension_f_orbit (cost=0.00..21.84 rows=957 width=0) (actual time=0.116..0.116 rows=1046 loops=1)"
" Index Cond: ((orbit >= 12000) AND (orbit <= 12100))"
" -> Index Scan using ib_t_md_metadata_fk_image_extension on md_metadata metadatas2_ (cost=0.00..5.50 rows=1 width=144) (actual time=0.011..0.011 rows=0 loops=1046)"
" Index Cond: ((metadatas2_.fk_image_extension)::text = (imagefeatu4_.id)::text)"
" Filter: ((metadatas2_.attribute_description)::text = 'DATASTRIP'::text)"
" -> Index Scan using feature_pkey on feature dgproductf0_1_ (cost=0.00..1.39 rows=1 width=37) (actual time=0.011..0.012 rows=1 loops=92)"
" Index Cond: ((dgproductf0_1_.id)::text = (metadatas2_.product)::text)"
" -> Index Scan using ib_t_md_metadata_fk_product on md_metadata metadatas1_ (cost=0.00..4.31 rows=15 width=72) (actual time=0.018..0.027 rows=11 loops=92)"
" Index Cond: ((metadatas1_.product)::text = (dgproductf0_1_.id)::text)"
" -> Index Scan using feature_pkey on feature metadatas1_1_ (cost=0.00..0.39 rows=1 width=74) (actual time=0.011..0.011 rows=1 loops=1046)"
" Index Cond: ((metadatas1_1_.id)::text = (metadatas1_.id)::text)"
" -> Index Scan using metadata_id_pkey on metadata_id metadataid5_ (cost=0.00..0.40 rows=1 width=37) (actual time=0.012..0.012 rows=0 loops=1046)"
" Index Cond: ((metadataid5_.id)::text = (metadatas1_1_.fk_metadataid)::text)"
" Filter: ((metadataid5_.external_id)::text ~~ 'DS%'::text)"
" -> Index Scan using feature_pkey on feature metadatas2_1_ (cost=0.00..1.39 rows=1 width=37) (actual time=0.010..0.010 rows=1 loops=92)"
" Index Cond: ((metadatas2_1_.id)::text = (metadatas2_.id)::text)"
" -> Index Scan using ib_t_dg_product_f_id on dg_product dgproductf0_ (cost=0.00..0.39 rows=1 width=37) (actual time=0.010..0.011 rows=1 loops=92)"
" Index Cond: ((dgproductf0_.id)::text = (dgproductf0_1_.id)::text)"
" Filter: dgproductf0_.archive_searchbyallusers"
"Total runtime: 51.962 ms"

-------------------------------------------

Marc Cousin · 23/12/2010 11:53:11

L'erreur d'estimation se trouve sur cette ligne là:

" -> Nested Loop (cost=22.08..7369.40 rows=957 width=108) (actual time=0.154..14.209 rows=92 loops=1)"

Tout le reste en découle. Malgré tout, même un facteur 10 n'est pas énorme, comme erreur, surtout dans le sens de la surestimation. Il ne s'agit que d'une optimisation statistique…

Bref, il s'agit d'estimer le nombre d'enregistrements ramenés par la jointure entre imagefeatu4_ et md_metadatas2_, en filtrant sur l'extension DATASTRIP. Il n'a aucun moyen de savoir que cette jointure va ramener 1 enregistrement sur 10…il se contente d'une estimation pessimiste du nombre d'enregistrement pouvant être ramenés.

Avez vous réellement un problème de performance sur la requête, ou c'est juste une question par simple curiosité ?

Ce genre d'erreur d'estimation est très fréquente, et normale.

F.Chanson · 23/12/2010 12:55:04

Merci ,pour la réponse rapide, je n'ai pas de pb de performances pour le moment , mais cette base de données va rapidement faire 10 Millions de Rows, Nous avons une première version du logiciel qui exploite cette bd qui effectue d'abord un 'select count " puis un select de rapatriement des données si le valeur du count est < à N données (N positionnée par l'utilisateur)

Nous voulons supprimer le count par un explain en exploitant le champ ("rows") afin d'éviter de faire de 2 requêtes. Mais on s'aperçoit que la valeur du champ "rows" de l'explain est souvent erronée (facteur 10 , 100) ce qui rend inexploitable l'utilisation de l'explain dans notre cas.

Je recherche donc un moyen de sécuriser/fiabiliser l'exploitation de ce champ ("rows") , est-ce possible , en augmentant le nb d'échantillon des stats ? en prenant un ratio , ...

Marc Cousin · 23/12/2010 13:04:42

Non, le nombre de lignes est très approximatif (vous remarquerez que même google, quand ils vous retourne une estimation du nombre d'enregistrements d'une requêtes, se trompe régulièrement d'un facteur 2 ou 3, alors qu'il n'y a pas de jointure sur 5 tables )

Habituellement, ce qui se fait, c'est plutôt de limiter le nombre d'enregistrements retourné par la requête à une valeur raisonnable… je présume que c'est un formulaire de recherche ?

L'utilisateur ira t'il lire plus de 500 résultats par exemple ? Sinon, vous pouvez utiliser LIMIT 500 dans votre ordre pour ne lui en ramener que 500. Si à la fin de ces 500, il en veut davantage, vous pourrez toujours réexécuter la requête avec LIMIT 500 OFFSET 500 pour aller chercher les 500 suivants. Et vous lui affichez 'voici les résultats 1 à 500 d'au moins 500'. Cela lui permet de constater que sa requête n'est pas assez sélective.

F.Chanson · 23/12/2010 13:19:29

Oui c'est bien un formulaire de recherche dans cette Bd qui permet d'accéder aux données.

Si je comprends bien, je ne peux pas utiliser l'explain et je n'arriverais pas par configuration postgres à établir un ratio me permettant de fiabiliser le résultat retourné par l'explain et dans mon cas indiquer à l'utilisateur que sa requête remonte trop de données.

Ce qui m'intéresse serait d'être proche de google et donc d'avoir un facteur 2 ou 3 mais pas 10 ou 100 !!!

Est-il préférable de faire un "count" (comme dans la première version du soft) ou d'utiliser le camp rows de l"explain analyse" qui exécute réellement la requête mais ne remonte pas d'infos

L'autre solution étant naturellement de jouer avec des select "LIMIT 500 OFFSET 500"

gleu · 23/12/2010 15:03:44

Mieux vaut un count, plus facile à récupérer et même performance que le EXPLAIN ANALYZE.

Cela veut aussi dire que vous allez exécuter deux fois la requête, ce qui n'est pas une bonne idée question performance.

Marc Cousin · 23/12/2010 15:05:41

Le problème du count, c'est qu'il va faire la requête complète. Donc si la requête ramène 100 000 enregistrements, le count ira chercher les 100 000 enregistrements. Il aura quasiment le même coût que la requête réelle.

F.Chanson · 23/12/2010 15:24:12

Pas tout à fait , mon schema de BB contient 30 tables.

Mon traitement est construit ainsi

1) construction du select de "count " par rapport aux critères contenues dans la clause "where", a ce titre il va au max s'appuyer sur 15 tables( qui contiennent les champs requetables). ce count remonte le nb de résultats de requête, affichage du nb de rows sur le formulaire. soit l'utilisateur affine sa requete soit il demande le rapatriement des données

si le rapatriement est demandé
2) construction d'un select "id" "qui remonte uniquement l'id des données" identique au count, donc il va s'appuyer au max sur 15 tables (qui contiennent les champs requetables)
3) construction d'un select "avec tous les champs demandées par l'utilisateur" et dans ce cas le traitement peut parcourir l'ensemble des tables du modèle afin de récupérer les champs requis, la clause where dans ce cas comporte uniquement un "where ID in (les ids remontés lors de la requete decrié en 2)"

Donc le cout des requêtes 1 et 2 sont identiques , la requête 2 sera plus rapide car en cache

LA requête 3 dépendra des tables qu'il faudra traverser (via des inner join) pour récupérer les infos demandées par l'utilisateur.

F.Chanson · 03/01/2011 12:18:10

Bonjour et Bonne Année... et pour finir au sujet de cet explain, 2 questions au sujet de l'erreur d'estimation (facteur 10)

L'erreur d'estimation se trouve sur cette ligne l
" -> Nested Loop (cost=22.08..7369.40 rows=957 width=108) (actual time=0.154..14.209 rows=92 loops=1)"

1) y-a-t-il un intérêt à positionner des statistiques au niveau des "colonnes jointes pu requetables" via la commande ALTER [ COLUMN ] column SET STATISTICS integer ? , car généralement ces colonnes sont indexées), ceci afin d'aider l'optimiseur ?

2) Quant une colonne est indexée, l'optimiseur utilise-t-il les informations relatives aux indexes ou s'appuie-il uniquement sur les statistiques collectés sur les colonnes, les tables ?

Marc Cousin · 03/01/2011 12:25:14

Cela ne vous aidera pas pour ce problème. Une statistique plus fine est utile pour estimer le nombre d'enregistrements d'une clause where, pas pour une jointure (il va avoir principalement besoin d'estimer le nombre moyen d'enregistrements pour une valeur, c'est quelque chose qu'on a correctement même avec des statistiques assez grossières).

Vous pouvez toujours augmenter la finesse des statistiques et voir si ça aide, mais j'en doute.

À ma connaissance, il utilise la taille d'un index, pour estimer le nombre d'entrées sorties nécessaires pour aller y chercher des informations. Je ne pense pas qu'il collecte davantage d'informations (mais là, je n'en suis pas sûr )

F.Chanson · 03/01/2011 18:34:59

J'ai fais quelques tests en spécifiant des statistiques au niveau des "colonnes" et il n'y a pas de résultats significatifs , donc pas d'amélioration de mon explain ... par contre, le plan d'exécution construit par l'optimiseur est-il toujours le même entre un explain et un explain analyse ? , lors de l'execution de la requête , l'optimiseur change-il de plan s'il s'aperçoit que son plan d'exécution n'est pas le bon.

Exemple: si le plan issu d'un explain remonte une estimation 100.000 rows , mais quant réalité le nb de rows retournés est < 100 , l' optimiseur changera -il son plan ?

Marc Cousin · 03/01/2011 18:36:09

Non, le plan est calculé avant l'exécution de la requête. Il n'y a pas de réévaluation en cours d'exécution.

F.Chanson · 03/01/2011 18:53:05

Ce plan est-il calculé via un explain , un explain analyse ? et y a t-il un moyen de récupérer le plan réellement exécute en positionnant un paramètre dans le fichier de configuration de Postgres ?

Marc Cousin · 03/01/2011 21:37:20

Le plan est calculé à l'exécution de la requête. Ou à l'appel d'explain.

On peut récupérer les vrais plans, via le module contrib autoexplain (cela permet de tracer les plans de toutes les requêtes dépassant une certaine durée). Attention, ça coûte un peu cher, vu le volume de données à logguer.

F.Chanson · 04/01/2011 09:57:09

Merci pour toutes ces réponses, cela clos le sujet sur cet "Explain incorrect"
Cordialement

#1 23/12/2010 11:41:34

Postgres 844 : Explain ncorrect

#2 23/12/2010 11:53:11

Re : Postgres 844 : Explain ncorrect

#3 23/12/2010 12:55:04

Re : Postgres 844 : Explain ncorrect

#4 23/12/2010 13:04:42

Re : Postgres 844 : Explain ncorrect

#5 23/12/2010 13:19:29

Re : Postgres 844 : Explain ncorrect

#6 23/12/2010 15:03:44

Re : Postgres 844 : Explain ncorrect

#7 23/12/2010 15:05:41

Re : Postgres 844 : Explain ncorrect

#8 23/12/2010 15:24:12

Re : Postgres 844 : Explain ncorrect

#9 03/01/2011 12:18:10

Re : Postgres 844 : Explain ncorrect

#10 03/01/2011 12:25:14

Re : Postgres 844 : Explain ncorrect

#11 03/01/2011 18:34:59

Re : Postgres 844 : Explain ncorrect

#12 03/01/2011 18:36:09

Re : Postgres 844 : Explain ncorrect

#13 03/01/2011 18:53:05

Re : Postgres 844 : Explain ncorrect

#14 03/01/2011 21:37:20

Re : Postgres 844 : Explain ncorrect

#15 04/01/2011 09:57:09

Re : Postgres 844 : Explain ncorrect

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux