Forums PostgreSQL.fr

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

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.

ok merci c'est gentil

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

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

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;

C'est la version 8.4.

Bonjour,

J'ai essayé d'écrire ANY comme vous avez dit, mais la fonction ne compile pas:
début de la fonction:
CREATE OR REPLACE FUNCTION test_any(curs refcursor, params text[])
  RETURNS refcursor AS
...
J'ai écrit:
open curs for execute 'select * from image where path=ANY('||$1||')' USING params ; 
-->
J'obtiens alors:
ERROR:  syntax error at or near "USING"
LINE 1: ... 'select * from image where path=ANY('|| $1 ||')' USING  $2
                                                             ^
QUERY:  SELECT  'select * from image where path=ANY('|| $1 ||')' USING  $2
CONTEXT:  SQL statement in PL/PgSQL function "test_any" near line 3

si je mes :
ANY('||$2||')' USING params, j'ai :

ERROR:  syntax error at or near "$1"
LINE 1: SELECT  'select * from image where path=ANY(' $1 ')' USING  ...
                                                      ^
QUERY:  SELECT  'select * from image where path=ANY(' $1 ')' USING  $1
CONTEXT:  SQL statement in PL/PgSQL function "test_any" near line 3

Si je ne mes pas les || autour de $1, il me dit qu'il y a une erreur de syntaxe.

Faut-t-il rajouter quelque chose?
----------------------------------------------------------------------------------------------------------------------------------------------------------

Je voulais aussi revenir sur les temps d'execution. Quand cursor_tuple_fraction était à 0.1 la fonction retournant un refcursor était bien plus longue qu'une requête SQL si le select récupérait des colonnes de type bytea, mais dans les autres cas les temps étaient équivalents.

Exemple: si la requête de la fonction était:

--> avec vector de type bytea. On a alors un temps d'execution de la fonction au moins 3 fois plus long que celui de la requête SQL.
open curs for 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'

--> sinon les temps sont quasi-similaires:
open curs for execute 'select IMAGE.id,id_sign, id_algo_gab from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign' --> temps similaires à la requête en SQL

Cela signifie-t-il que le plan execution "fast-start" etait suffisant quand la taille des valeurs de colonnes n'étaient pas trop gros (int, text...): la méthode de récupérer le plus rapidement possible 10% des enregistrements étaient la meilleure.

Mais quand une des colonnes était de type bytea (par exemple vector qui semble contenir des valeurs assez grosses ), ramener très vite 10% des enregistrements n'était pas la bonne méthode. Il était mieux d'attendre de générer tous les enregistrements avant de les récupérer.

De plus puisque les temps des fonctions postgres et des requêtes étaient très différents quand cursor_tuple_fraction était à 0.1, cela signifie-t-il que les plans d'execution de postgres peuvent être très différents de ceux en SQL?

Cette interprétation vous semble -t- elle correcte?

merci

Bonjour,

J'ai un problème avec postgresql.conf. Je l'ai ouvert avec pgadmin et je n'y ait pas trouvé cursor_tuple_fraction.
Je l'ai alors ouvert manuellement avec un editeur de texte (gedit) et j'y ai bien trouvé cursor_tuple_fraction. J'y ai donc mis la valeur de 1.0 au lieu de 0.1. Mais pgadmin n'en tient pas compte (je vérifie alors avec show all). J'ai quitter puis relancé pgadmin mais ça ne change rien. J'ai aussi essayé en enlevant le # devant cursor_tuple_fraction mais ça ne change rien
Savez-vous si je dois faire autre chose?

Bonjour,
merci de votre réponse. J'aimerais si possible obtenir quelques précisions sur certains points.
Vous avez dit: " part de l'hypothèse que vous n'allez récupérer que 10% des enregistrements générés  (il n'a aucun moyen de savoir si vous allez tout utiliser)."

Je n'ai pas bien saisi la différence entre générer et récupérer. Est-ce que générer= mettre  les résultats de la requête (un enregistrement=1 ligne) en mémoire, et utilisez= mettre ces résultats en sortie (en créant un ensemble curseur et en y insérant les lignes)? 

Si je modifie  le paramètre  globalement (postgresql.conf), cela peut -il avoir des conséquences sur d'autres fonctions? (J'ai testé sur d'autres fonctions refcursor où le select ne prend pas de colonnes de type bytea, et elle semblent un tout petit peu plus rapide--> 21 853 ms au lieu de 28 058 ms)

Enfin d'une manière générale, si par exemple je n'ai pas besoin que le résultat d'une fonction renvoyant un ensemble de ligne (par refcursor ou setof) puisse être récupérer par une autre fonction plpgsql (dans ce cas là on utilise refcursor je crois),
il y a -t-il d'autres intérêt à privilégiers le type de retour refcursor plutôt que setof?  Le premier type offrirait-t-il plus de facilités pour certaines opérations?

Encore merci pour vos réponses

Bonjour,
j'ai fait de multiples tests en comparant les temps d’exécution des fonctions renvoyant un refcursor, celles renvoyant un setof de type composite et ceux des requêtes SQL.
A chaque fois j'ai obtenu des temps d’exécution similaires sauf dans un cas.
Ce cas est quand je fais un select sur une ou plusieurs colonnes dont une contient des valeurs de type bytea.

Exemple:
select IMAGE.id,id_sign, vector from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign; -- vector est de type bytea.

-- Dans ce cas là seulement la fonction renvoyant un refcursor est 3 fois plus longue (desfois plus encore)que les deux autres. La fonction retournant un setof a par contre toujours un temps equivalent à la requête SQL.
Pour tout autre combinaison de colonnes sélectionnées, si aucune n'est de type  bytea alors les 3 ont des temps similaires.
--Voici un exemple de valeur de vector:
" 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"

--C'est utilisé pour stocker des résultats d'algorithmes, mais ce n'est pas moi qui gère le calcul.
-- on réfléchit à remplacer ce type de colonne

J'aimerais savoir si vous avez une idée de pourquoi le type bytea entraine une différence de temps quand on utilise refcursor ou setof.
Ci- dessous le code des 2 fonctions et de la requête.
Merci.

Exemple:
tables:  image (id, ... autres paramètres...)  signature(id_sign,id_image) algo_gab (id, vector, id_sign)
IMAGE.id ALGO_GAB.id et id_sign sont des entiers, id_image est une clé étrangère référençant IMAGE.id.
vector est de type bytea.

Requête SQL:
select IMAGE.id,id_sign, vector from image,signature,algo_gab where IMAGE.id=id_image and signature.id_sign=algo_gab.id_sign; --> 23 541 ms

Fonction refcursor:
CREATE OR REPLACE FUNCTION fun_ref(curs refcursor)  --> 116 169 ms
  RETURNS refcursor AS
begin
open curs for 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'
return curs;
end;

Fonction setof:

create type image_sign_gab as (i int, sign int, vector bytea);

CREATE OR REPLACE FUNCTION fun_setof () --> 18 951 ms
  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;

Bonjour,
merci de votre réponse. Donc en fait si j'ai bien compris plpgsql ne peut etre plus rapide que par rapport à plusieurs requêtes SQL exécutées à la suite. Mais considérant une seule requête on ne peut aller plus vite que sql.
Pour mes fonctions, le type de retour refcursor a été remplacé par setof (un type composite créé) et j'ai remarqué qu'alors que les temps d'éxécutions étaient similaires aux requêtes écrites en SQL. Si possible j'aimerais comprendre pourquoi. (Types d'algorithmes mis en jeu, ou gestion particulière de la mémoire ou autre mécanisme expliquant la différence de temps d'éxécution entre refcursor et setof).
2ème point:
je voulais faire une fonction booleenne is_included ayant comme paramètre un tableau d'id (clé de certaines tables) et qui renvoie true si l'entier passé en paramètre est inclus dans le tableau.
J'utilisais cette fonction comme condition (après le WHERE) dans des requête d'autres fonctions.
Exemple: select s_id where is_included(s_id, ARRAY [1,2,3]). Mais le temps d'exécution était très lent. J'ai finalement remplacé par select s_id in (1,2,3) et j'ai eu des temps bien meilleurs.
Dans la fonction utilisant le select in Je construis donc une chaine à partir du tableau en paramètre (de type text[] car les élément désignent des entiers, booléens ou autre. Je fais des CAST ensuite dans la requête).
Je voudrais là aussi savoir d'où vient la différence et si il existe un moyen de faire mieux encore.
En espérant ne pas étre trop confus.

Ci -dessous les  code de is_included puis de la fonction retournant un setof. Si vous trouvez des points où l'on peut faire mieux n'hésitez pas à le notifier.
Voici le code de is_included. On parcourt le tableau avec une boucle while, n'étant pa assez efficace je ne l'utilise plus:
CREATE OR REPLACE FUNCTION is_included(test integer, signatures integer[])
  RETURNS boolean AS
DECLARE
present boolean := false;
i integer;
BEGIN
i:=0;
while present=false AND i<= array_upper(signatures,1) loop
if test= signatures[i] then present:=true;
end if;
i:=i+1;
end loop;
return present;
end;
------------------------------------------------------------------------------------------------------------------------------------------------------

Ci-dessous un exemple de code retournant un setof:

--params contient une liste valeurs d'attributs qui sont propres à la table algo_table
--on n'a pas toujours les memes colonnes selon la table

create type request_sign_type as (signature int, vector bytea, id_image int , i_time double precision);

CREATE OR REPLACE FUNCTION request_id(algo_table text, params text[], user_paths text[] DEFAULT '{}'::text[])
  RETURNS setof request_sign_type AS
$BODY$
declare
selected_baz request_sign_type%rowtype;

s text;
s_paths text :='';

begin
-- on appelle une fonction qui fait que les element de user_paths  soient concaténés dans s_paths . Exemple: avec {path1, path2, path3} s_path contient 'AND path in (path1, path2, path)'
-- si user_path est vide s_path est vide (pas de conditions sur path). Je ne l'écris pas pour ne pas surcharger inutilement.
--path es une colonne de la table image

if(algo_table ='algo_gab') then
FOR selected_baz IN EXECUTE
' select s_id, s_vector, s_image, s_time from signature, image,algo_gab WHERE s_id=algo_gab.s_id AND signature.s_image=s_image
AND  norm_x ='||
  CAST (params[1] as int) ||'   --pas sur que c'est nécessaire
AND norm_y = '||CAST (params[2] as int) ||'
AND scale_numbers ='||CAST (params[3] as int) ||'
AND quadrant = '||CAST (params[4] as boolean)||' '  -- ici c'est un booléen
||s_paths
loop
RETURN NEXT selected_baz;
end loop;

if(algo_table ='algo_text') then ...
...
Return;