Vous n'êtes pas identifié(e).
Bon ben merci quand même pour toutes vos réponses rapides et précises.
en utilisant les caractères d'échappement j'arrive à faire passer des tableaux dans la requête et ça marche plutôt bien
Hors ligne
Ok. Désolé, il n'y a pas de mécanisme élégant pour ce cas particulier en 8.4.
Marc.
Hors ligne
Bonjour,
j'ai de nouveau un problème de vitesse, cette fois en comparant une requête SQL et une fonction retournant un setof.
Le problème survient quand j'utilise la fonction setof dans: select ... in (appel de la fonction setof). Ceci est beaucoup plus long que l'équivalent écrit en requête SQL simple.
Comme précédemment les problèmes deviennent visibles lorsque un élément de type bytea fait partie des colonnes demandées.
version 8.4 de pgadmin 3.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Je détaille ci dessous:
situation des tables:
image (id_image,...autres attributs...) id_image de type int
signature(id_signature,...autres attributs...,id_image) id_signature de type int, id_image référence clé de image
algo_gab (..., vector,...autres attributs..., id_signature) vector de type bytea, id_signature référence clé de signature
exemple de valeur de vector: (c'est assez gros):
1.11498E+02, 1.35706E+02, 2.88843E+02, 1.30191E+02, 1.89009E+02, 1.88747E+02, 1.16372E+03, 1.71711E+02, 1.84266E+02, 2.83373E+02, 2.02455E+03, 2.50216E+02, 2.94586E+02, 4.01803E+02, 2.74225E+03, 3.59621E+02, 2.37780E+02, 4.26430E+02, 4.90676E+03, 3.64694E+02
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
j'ai une fonction ,retournant un setof image, nommée test_vsetof() (je mettrais le corps de la fonction à la fin du message) et la requête SQL équivalente:
select * from image
Quand je les appelle directement ils ont des temps d'execution équivalents:
Exemple:
select id_image from test_vsetof() --> 1050 ms
select id_image from image -> 1241 ms (ce n'est pas une moyenne mais une valeur particulière)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Je veux maintenant imbriquer le résultat de ce select dans un autre select:
Exemple:
en utilisant requête SQL: --> (temps moyen obtenu: 17 000 ms)
select vector FROM image,signature,algo_gab WHERE Signature.id_signature=Algo_gab.id_signature AND Image.id_image=Signature.id_image and
id_image in
(select id_image from
( select * from image ) as curs
)
en utilisant la fonction setof, j'ai 121 439 ms:
select sig_vector FROM image,signature,algo_gab WHERE id_signature=signature_id_signature AND image_id_image=id_image and
id_image in
( select id_image from test_vsetof() )
Remarque: si à la place de sig_vector je mes une colonne d'un autre type on a alors des temps équivalents dans les 2 cas.
Faut-il là aussi changer un paramètre de configuration du serveur?
merci à vous
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Ci-dessous le code de test_vsetof():
CREATE OR REPLACE FUNCTION test_vsetof()
RETURNS setof image AS
$BODY$declare
r record;
i image;
begin
for r in execute 'select * from image' loop
return next r;
end loop;
return ;
end;$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION test_vsetof() OWNER TO postgres;
Dernière modification par kris_le_parisien (30/01/2011 22:02:59)
Hors ligne
Pas de configuration à faire. Dans le premier cas (pas de fonction), le planificateur peut optimiser le travail (comme par exemple faire appel à un index). Dans le second cas, il ne peut faire aucune optimisation et est obligé d'exécuter une procédure stockée en PL/pgsql ce qui est assurément plus lent qu'un bête parcours séquentiel.
Guillaume.
Hors ligne
Bonjour,
Cela signifie -t-il qu'à chaque fois que l'on utilise une procédure stockée, ça bloque ou limite l'optimisation? Ou est-ce particulier à ce cas là?
Quelles sont les solutions utilisent les développeurs en général pour contourner ce problème?
Nous envisageons de créer des vues, pour limiter l'appels de procédures stockées, et utiliser au maximum des requêtes SQL simples? Pensez-vous que c'est une bonne manière de faire?
merci de vos réponses
Hors ligne
on utilisait avant une fonction setof:
Quand on faisait un appel à la fonction sans select imbriqués, on obtenait des temps d'execution similaires avec l'équivalent en SQL. Cela signifie-t-il que dans ce cas une optimisation était effectuée pour l'appel de la procédure stockée?
Le fait de ne pas pouvoir optimiser est-il du aux select imbriqués ou à la procédure stockée ou aux deux?
Requête SQL corespondante:
select IMAGE.id,id_sign, vector from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign; --> on obtenait 23 541 ms
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Défnition ci-dessous de la fonction:
create type image_sign_gab as (i int, sign int, vector bytea);
CREATE OR REPLACE FUNCTION fun_setof () --> on obtenait 18 951 ms avec un select * from fun_setof ().
RETURNS setof image_sign_gab AS
$BODY$
declare
s image_sign_gab %rowtype;
begin
for s in execute 'select IMAGE.id,id_sign, vector from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign ' loop
return next s;
end loop;
return;
end;
merci
Hors ligne
Le fait de ne pas pouvoir optimiser est-il du aux select imbriqués ou à la procédure stockée ou aux deux?
À la procédure stockée. Ce n'est pas comme une vue dont la requête sera intégrée dans la requête globale. Une procédure stockée est une boite noire pour PostgreSQL.
Guillaume.
Hors ligne
D'accord,
dans ce cas, quand j'obtiens des temps équivalents pour select vector from fun_setof () et
select vector from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign
cela signifie-t-il que la requête "select vector from fun_setof ()" est par sa défault optimale, malgré l'appel d'une procédure stockée?
Et que ce n'est plus le cas pour:
"
select vector FROM image,signature,algo_gab WHERE id_signature=signature_id_signature AND image_id_image=id_image and
id_image in
( select id_image from fun_setof() )"
merci
Dernière modification par kris_le_parisien (31/01/2011 16:37:54)
Hors ligne
Non, ça veut dire qu'il n'y a pas grand chose à optimiser avec la première requête alors que la seconde peut avoir une bonne optimisation quand on n'utilise pas une procédure stockée.
Guillaume.
Hors ligne
D'accord,
du coup est ce que les développeurs en général font en sorte de minimiser le nombre de procédures stockées, et d'utiliser d'autres astuces à la place?
Si oui, pourriez vous me dire quels sont les mécanismes les plus populaires?
Pour faciliter nos requêtes, on pense à créer des vues correspondantes? Est ce une démarche souvent utilisée?
merci à vous
Hors ligne
La création de vues est bien plus intéressantes du côté des performances. Mais elles ne permettent pas tout ce que permet une procédure stockée. Donc, oui, minimiser les procédures stockées, utilisez plutôt des vues lorsque c'est possible. Et quand il n'y a pas moyen de faire autrement, coder des procédures stockées. Mais coder les bien. On voit trop souvent des procédures stockées écrites n'importe comment, ce qui est un gouffre au niveau des performances.
Guillaume.
Hors ligne
Sachant que les vues sont dangereuses aussi, parce qu'elles masquent l'accès à plusieurs tables. On voit souvent des gens jointurant des vues avec des tables, effectuant des jointures sur 10 tables sans en avoir conscience, alors que 2 suffiraient.
=> Les vues sont intéressantes, mais attention à ne pas en abuser (surtout en cas de jointure, sous requête, etc… s'appuyant sur la vue).
Marc.
Hors ligne
Oui, elles ne sont pas forcément la panacée. La version 9 améliore les choses avec le "join removal" même si là-aussi ce n'est pas utilisable dans tous les cas.
Guillaume.
Hors ligne
ok merci pour vos conseils,
A propos des procédures stockées, si on est forcé d'en utiliser, connaissez-vous des sites web particuliers qui donneraient les principaux points à respecter pour bien coder des procédures?
La règle pour bien coder une procédure est-elle simplement de chercher la plus faible complexité, et de regarder les structures de pgsql (array, vues, select... in...,...) les plus efficaces (en effectuant des tests de performance selon la structure choisie)?
merci beaucoup.
Dernière modification par kris_le_parisien (01/02/2011 20:18:26)
Hors ligne
Je pense que la principale règle dans toutes les bases de données, que ça soit du PL ou du simple SQL, c'est d'essayer d'en faire le maximum avec chaque ordre.
Si vous parlez anglais et que vous avez 20 minutes, je vous recommande ces 3 excellentes vidéos.
http://www.youtube.com/watch?v=40Lnoyv-sXg
http://www.youtube.com/watch?v=GbZgnAINjUw
http://www.youtube.com/watch?v=y70FmugnhPU
Marc.
Hors ligne
ok merci c'est gentil
Hors ligne
Bonjour,
j'ai une requête sql et je souhaiterais si possible pouvoir la rendre plus rapide. J'ai utilisé des index, mais ils ne sont pas utilisés par l'optimiseur.
Voici les tables en question:
image(id_image,...) id_image= entier et primary key , 137 058 lignes
signature(id_signature,image_id_image) image_id_image= foreign key sur image[id_image] NOT NULL, 1 645 065 lignes
sig_gab (id_sig_gab, signature_id_signature) signature_id_signature= foreign key sur signature[id_signature] NOT NULL, 137 058 lignes
Tous les ID de image sont dans signature[image_id_image]
voici ma requête :
explain analyse SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant
FROM sig_gab,signature, image
where signature.image_id_image = image.id_image
and signature.id_signature = sig_gab.signature_id_signature order by time
dans la suite temps d'execution désignera la tamps pris pour executer une requete sans les mots explain analyse;
Exemple:
SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant
FROM sig_gab,signature, image
where signature.image_id_image = image.id_image
and signature.id_signature = sig_gab.signature_id_signature order by time
Pour parler du temps indiqué en dessous de l'arbre par le explain analyse je préciserais avec les mots : "Total runtime: "
voici ce que j'obtiens:
"Sort (cost=42201.77..42541.19 rows=135769 width=29) (actual time=2903.738..3040.078 rows=137058 loops=1)"
" Sort Key: image."time""
" Sort Method: external merge Disk: 5624kB"
" -> Hash Join (cost=7606.65..27375.42 rows=135769 width=29) (actual time=298.104..2348.859 rows=137058 loops=1)"
" Hash Cond: (signature.image_id_image = image.id_image)"
" -> Merge Join (cost=8.85..15113.99 rows=137058 width=21) (actual time=0.091..1450.229 rows=137058 loops=1)"
" Merge Cond: (sig_gab.signature_id_signature = signature.id_signature)"
" -> Index Scan using fk_sig_gab_signature on sig_gab (cost=0.00..9058.56 rows=137058 width=17) (actual time=0.045..573.206 rows=137058 loops=1)"
" -> Index Scan using signature_pkey on signature (cost=0.00..50013.17 rows=1645065 width=8) (actual time=0.039..379.898 rows=137059 loops=1)"
" -> Hash (cost=5214.58..5214.58 rows=137058 width=12) (actual time=297.859..297.859 rows=137058 loops=1)"
" -> Seq Scan on image (cost=0.00..5214.58 rows=137058 width=12) (actual time=0.014..153.951 rows=137058 loops=1)"
"Total runtime: 3100.730 ms"
En ne mettant pas le explain analyse j'obtiens un temps moyen de 57 308 ms pour 137 058 lignes.
J'ai essayé des optimisations mais qui n'ont rien améliorer:
- j'ai supprimé le order by time et ça enlève le noued Sort mais toute ce qui est en dessous reste identique (couts, noeuds,...). Pourtant j'ai toujours un temps d'execution identique (en ne mettant pas explain analyse j'ai toujours 57 308 ms) pourtant Total runtime a baissé 1444.693 ms
- J'ai essayé de réécrire la requête avec des left Join mais aucun gain de temps. Hash join est transformé en Hash left Join, et Merge join en Merge left join, mais les couts sont les mêmes et les temps d'execution aussi.
requête écrite:
explain analyse SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant
FROM ( sig_gab LEFT JOIN signature ON signature.id_signature = sig_gab.signature_id_signature)
left join image ON signature.image_id_image = image.id_image;
- J'ai essayé de créer des index, mais ils ne sont pas utilisés, j'ai toujours le même arbre.
Les index créés sont:
CREATE INDEX index_signature_image ON signature (image_id_image);
CREATE INDEX index_signature_image ON signature (image_id_image);
Sauriez-vous s'il est possible d'optimiser la requête et si oui quels seraient les meilleurs solutions?
merci
Dernière modification par kris_le_parisien (02/03/2011 16:50:50)
Hors ligne
Pour commencer, non, il ne va pas être possible d'optimiser la requête en rajoutant des index.
Le choix du moteur est probablement le meilleur… Boucler sur l'index id_image serait bien plus coûteux que le hash-join qu'il choisit. Par contre, vu le volume de données, je pense que votre paramètre work_mem est très insuffisant pour une requête de ce type (On voit d'ailleurs qu'on a un tri disque, pour 5Mo de données). Réessayez avec un work_mem beaucoup plus élevé, le temps d'exécution devrait être plus faible.
Par contre, êtes-vous sûr que vous avez besoin de 3 tables ? Vu que les trois étapes du plan ramènent exactement le même nombre d'enregistrements avant jointure, on a l'impression que non. Mais évidemment, avec aussi peu d'informations, je peux me tromper. Il est quand même très louche que image et sig_gab aient le même nombre d'enregistrements, et que le scan sur signature_pkey en ramène juste un de plus.
Marc.
Hors ligne
Bonjour, entre temps j'ai rajouté: SET enable_mergejoin=off; et le temps d'execution est passé de 57 308 à 13 199 ms.
Le noued merge_join est remplacé par hash join.
Actuellement image et sig_gab ont le même nombre de lignes car on a créé une ligne de sig_gab pour chaque ligne de image (une ligne de sig_gab contient le résultat d'un algorithme appliqué à une image). La table signature nous sert à identifier de manière unique le résultat de cet algo sur cette image.
je vais essayer pour le work_mem.
Dernière modification par kris_le_parisien (02/03/2011 16:59:10)
Hors ligne
Les temps sont totalement incohérents entre les différents posts. On voit dans le explain analyze 3100ms. Pouvez-vous clarifier ?
Pour ce qui est du merge_join, il est probable qu'il devienne un hash join de lui même si vous avez suffisamment de work mem. En tout cas sur le plan affiché, le merge join coûte 1.5s. Il peut être très largement plus lent si les données sont en cache. Ce qui ne sera pas le cas d'un hash join, qui fera des parcours séquentiels des tables, et aura donc un temps acceptable même si les données ne sont pas dans le cache.
Et réfléchissez tout de même au schéma, il y a à mon avis quelque chose de louche, comme une table éclatée en 2 sans bonne raison.
Marc.
Hors ligne
En fait j'ai constaté que les temps marqués dans le explain analyse après "Total runtime " est toujours largement inferieur à celui obtenu lors de l'execution de la requête en enlevant le explain analyse.
exemple:
SELECT id_image, id_signature, norm_x, norm_y, scale_numbers, quadrant
FROM sig_gab,signature, image
where signature.image_id_image = image.id_image
and signature.id_signature = sig_gab.signature_id_signature order by time --->
57 308 ms au départ,
13 199 ms. avec SET enable_mergejoin=off.,
11 279 ms avec work_mem à 5MB et enable_mergejoin=on ou off. Dans explain analyse on a des temps très différents "Total runtime: 4109.467 ms"
On dirait que explain analyse n'execute pas completement la requête, doù peut-être la différence de temps. Avez-vous une idée de la raison?
J'ai augmenté le work_mem qui était à 1MB et que j'ai passé à 5MB. J'obtiens maintenant un temps d'execution de 11 279 ms avec enable_mergejoin=off.
avec la requête explain analyse j'obtiens "Total runtime: 1846.291 ms" et le noeud merge_join n'est pas remplacé. On a la même structure d'arbre.
Puis-je encore augmenter work_mem, sans qu'il y ait de conséquances trop néfastes pour d'autres requêtes? Pensez-vous que c'est la meilleure optimisation possible?
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Concernant le schéma, on a actuellement une ligne dans sig_gab pour chaque ligne de image car on a appliqué une fonction qui pour chaque image (1 ligne de image) stocke le résultat dans sig_gab. On a donc le même nombre de lignes.
Les tables signatures et sig_gab ont 137 058 lignes en communs (les lignes de sig_gab). Une signature sert à identifier de manère unique les lignes de sig_gab, étant donné une image,
(l'identifiant de sig_gab ne suffit pas car on a aussi d'autres tables sig_tex,... qui fonctionnent sur le même principe. On ne peut pas prendre leurs clés primaires pour identifier les résultats de manière unique.). Le merge_ join renvoie 137 058 lignes, le hash join aussi car , pour le moment , à 1 résultat (ligne) de sig_gab correspond 1 image (1 ligne).
La table signature nous sert juste pour identifier dans toute la base de manière unique le résultat. Je n'ai pas trouvé d'erreur pour l'instant, mais je vais revérifier.
merci.
Hors ligne
- La seule différence, c'est que le explain analyze ne ramène pas les données, contrairement au vrai ordre SQL. Le reste ça ressemble à du temps réseau. Vous avez peut-être un lien lent entre le serveur et vous ? Ou bien un grand volume de données retourné ? En temps normal le explain analyze est plus lent que la requête normale, puisqu'il rajoute de l'instrumentation pour mesurer les différentes phases du plan d'exécution
- Le work_mem peut être augmenté juste pour votre session, pas la peine de modifier le fichier de configuration, si vous voulez juste effectuer des tests :
SET work_mem TO '64MB'
par exemple, ça ne le modifiera que pour la session courante. Ça vous permettra de faire des tests. Par contre effectivement, c'est dangereux de le garder à une valeur élevée si vous avez plusieurs sessions, puisque chacune peut allouer ce work_mem (voire même l'allouer plusieurs fois, comme dans votre requête, puisqu'elle l'allouera une fois pour le hash, et une fois pour le sort)
Pour ce qui est du schéma, je ne comprends pas pourquoi ne pas stocker la signature d'une image dans le même enregistrement que l'image. Vous n'avez certainement pas plus d'une signature par image. Ou alors je ne comprends pas l'intérêt de la signature 
Marc.
Hors ligne
D'accord,
La base de données que je conçois actuellement à pour but d'être accessibles par des programmes externes (en ADA, python,...). Dans ce cas si l'on a besoin de changer des paramètres du serveur juste pour une requête, est-ce que l'on peut inclure le changement du paramètre dans la requête ou est-il obligatoire de changer le fichier de configuration du serveur?
exemple:
set work_mem TO 64MB; ma_requete_sql; --> envoyé depuis un autre programme. (faut- il ensuite réinitialisé la valeur de work_mem?)
----------
Pour le cas du schéma.
En fait on a les tables image, signature, sig_gab mais aussi sig_tex,....
Les tables sig_gab, sig_tex représentent les résultat d'algorithme (un algo distinct par table) appliqués aux images. A une image (une ligne dans Image) peut correspondre une ligne dans sig_gab et/ou aussi dans sig_tex.
La signature est donc l'identifiant de ce résultat (on crée une table signature en plus, pour que cet identifiant soit unique par rapport à toute la base. Si on avait pris comme identifiant les clés de sig_gab et sig_tex, on pourrait avoir un ID 1 dans sig_gab et 1 dans sig_tex. On ne peut pas identifier les résultats de manière unique.)
Pensez-vous qu'une meilleure solution est possible?
merci beaucoup
Hors ligne
Bien sûr, on peut changer le paramètre juste pour la requête. Ne pas oublier de faire un RESET work_mem juste après, pour la ramener à la valeur qu'il avait avant le SET.
Ce que je dis en dessous dépend de ce que je comprends du schéma bien sûr… je pourrais être totalement à côté de la plaque:
Pour la partie schéma, je ne comprends toujours pas le besoin des tables sig_gab et sig_tex. Elles ont un lien 1 pour 1 avec la table image. Pourquoi ne pas stocker directement les signatures dans des colonnes de la table image ? Cela serait bien plus performant (scanner une table par rapport à 3). Surtout que le principe d'une signature, c'est d'être unique ou presque, je présume… donc que remplacer la signature dans une table par une clé technique ne va rien vous faire gagner en termes d'espace disque, et uniquement compliquer le schéma (toujours si je comprends).
Si je n'ai toujours rien compris, le mieux ça serait un exemple je crois
Marc.
Hors ligne
Bonjour et merci,
En fait c'est qu'il y doit y avoir par la suite une autre table qui n'est pas utilisé pour le moment. Cette table s'appelle Detecteur.
Mode de fonctionnement:
Une image est analysé par différents détecteurs, ce qui donne plusieurs résultats. Ces résultats sont passés à divers algorithmes dont les résultats sont stockés dans les tables sig_gab, sig_tex,... (une table par type d'algo).
Donc à une image , pour un algo donné (exemple celui de la table sig_gab), on obtient plusieurs résultats différents (pour chaque type de détecteurs).
On a un lien: à 1 image correspond n lignes de sig_gab.
Pour le moment, on n'utilise pas les detecteurs (comme si on utilisait le detecteur vide). Dans le cas présent votre solution est donc parfaite.
La table signature contient en fait les attributs suivants: (id_signature, fk_id_image, fk_id_detecteur) --> fk= foreign key, avec fk_id_detecteur=NULL pour l'instant.
Je n'avais pas parlé de la table Detecteur pour ne pas rendre les choses plus confuses.
L'id de signature permet de rendre chaque résultat unique (pour pouvoir être reconnu uniquement par une autre classe appelé cluster)
On a donc en réalité:
image(id_image,...) -->> 1 image -> n signatures ( n= n1 résultats dans sig_gab (pour n1 detecteurs)+ n2 resultats dans sig_tex (pour n2 detecteurs,...)
Detecteur(id_detecteur,...) -->> pas encore utilisé, par la suite on aura 1 detecteur-> n signatures ( n= n1 résultats dans sig_gab (pour n1 images)+ n2 resultats dans sig_tex (pour n2
images,...)
Signature(id_signature, fk_id_image, fk_id_detecteur) --> 1 signature ->1 image, 1 detecteur, 1 algo
Sig_gab(id_sig_gab,...), Sig_tex(id_sig_tex,...), ... --> 1 ligne pour 1 image et 1 detecteur
Cluster(id_cluster,...) --> 1 cluster -> n signature, 1 signature -> m cluster
ClustersSignature(fk_id_cluster,fk_id_signature ) --> pour stocker les signatures (=résultats d'un algo sur une image, étant donné un détecteur) utilisés par un cluster
Voici notre base à l'état actuel, sous réserve d'ajouts par la suite. D’après ce que vous avez dit, il est mieux d'avoir une table avec beaucoup d'attributs (même si certains sont Null) que plusieurs tables (pour ainsi éviter les jointures).
Peut-être que je devrais appliquer certains principe de normalisation pour améliorer tout ça.
Dernière modification par kris_le_parisien (03/03/2011 14:32:16)
Hors ligne
