Forums PostgreSQL.fr

R.Seif

Membre

optimisation de l'insertion et la selection

Bonjour,

Lors du programme si dessous, je veux insérer une @IP, tester son existance, me rendre un msg d'erreur (qui ne soit pas systématique) s'il existe, sinon on insère cette nouvelle @.

-- MAPE_ADD_IP
-- Takes a CIDR representation of the IP in input and adds a record in the ips table
-- returns the index of the entry
-- if the entry exists, returns simply the ID
--
CREATE OR REPLACE FUNCTION mape_add_ip(inet) RETURNS inet
AS $$
	DECLARE
        addr ALIAS FOR $1;
	md5ip varchar;
        ipid INTEGER;
BEGIN
	SELECT INTO ipid mape_get_ip(addr);

	IF ipid >= 1
		THEN
			return (addr);
		ELSE
			INSERT INTO ips(ip,md5) VALUES (addr,'-');
			--SELECT INTO ipid currval('ips_id_seq');
	END IF;
	
       	 return (addr);
END;$$
	LANGUAGE plpgsql;


-----------------------------------------------------------------------------------------
-- MAPE_GET_IP
-- Returns the ID of the given addr
--
CREATE OR REPLACE FUNCTION mape_get_ip(inet) RETURNS integer
AS $$
	DECLARE
	addr ALIAS FOR $1;
	ipid  INTEGER;
BEGIN
	SELECT INTO ipid ip FROM ips WHERE host(ips.ip) = host(addr);
IF ipid >=1
	THEN
		return ipid;

		ELSE
			return 0;
	END IF;
	
END;$$
	LANGUAGE plpgsql;



--

cette opération va se faire plusieurs fois, donc j'ai pensé utiliser INSERT ... RUNNING ... mais j'ai pas su comment l'utiliser?

Marc Cousin

Membre

Re : optimisation de l'insertion et la selection

INSERT RETURNING ne sert pas dans votre cas, puisque vous retourneriez quelque chose que vous connaissez déjà.

Premier problème:
SELECT INTO ipid ip FROM ips WHERE host(ips.ip) = host(addr) n'utilisera pas d'index… et sera donc peu performant : vous appliquez une fonction à une colonne de votre table.

Ensuite, je vous conseille d'opter pour l'algorithme réussissant le plus souvent : quand mape_add_ip est appelé, quel sera le cas le plus fréquent : l'ip déjà en place, ou l'ip à insérer ? si c'est le second cas, commencez votre algo par une tentative d'insertion, puis interceptez l'erreur si nécessaire, plutôt que de d'abord tester la présence de l'enregistrement puis tenter l'insertion.

Dernier point qui me chiffonne : que se passe t'il si vous avez deux sessions voulant ajouter la même IP en même temps, que les deux voient par mape_get_ip que l'IP n'existe pas, et que les deux essayent d'insérer en même temps ? Vous avez une violation de contrainte unique dans une des deux sessions. Alors que vous avez justement écrit votre fonction dans l'espoir de l'éviter à votre utilisateur. Ce dernier point est un argument supplémentaire pour tenter d'insérer, puis intercepter l'erreur du violation de contrainte unique : votre code fonctionnera toujours, sans cas particulier à gérer.

Dernière modification par Marc Cousin (19/04/2010 14:03:22)

---

Marc.

R.Seif

Membre

Re : optimisation de l'insertion et la selection

et pour l'index?
en fait le test que je vais essayer est énorme, pour des observations de 1 journée, il me faut une journée pour récupérer les résultats.
donc j'ai opté à éliminer les index puisqu'ils ne sont efficace qu'en lecture, et non pas en écriture.

Marc Cousin

Membre

Re : optimisation de l'insertion et la selection

Les index sont efficaces en écriture justement quand la requête d'écriture a besoin de l'information , ce qui est le cas du select indiqué plus haut.

Pouvez vous poster la définition de la fonction host ?

---

Marc.

R.Seif

Membre

Re : optimisation de l'insertion et la selection

et bah ça ne marche pas quand je fais l'insert avant le select.

Marc Cousin

Membre

Re : optimisation de l'insertion et la selection

J'imagine, je ne suggérais pas de juste en inverser l'ordre d'exécution. Si vous faites l'insert alors que la donnée existe déjà, vous allez générer une erreur, qu'il faudra alors intercepter.

Je vous conseille de regarder http://docs.postgresql.fr/8.4/plpgsql-c … tures.html (paragraphe 8.6.5 et suivant), avant de continuer la discussion, le paragraphe devrait vous éclairer sur le sujet. La condition d'erreur à tester est "unique_violation".

---

Marc.

R.Seif

Membre

Re : optimisation de l'insertion et la selection

c fait.
en plus j'ai gagné trop au niveau comparaison puisque mon prog utilise 5 fois la fonction mape_get_ip.
Je vous remercie.

Dernière modification par R.Seif (19/04/2010 15:52:26)

Marc Cousin

Membre

Re : optimisation de l'insertion et la selection

N'oubliez pas malgré tout cette problématique d'index sur mape_get_ip. C'est là qu'on va pouvoir obtenir le plus gros gain. Que fait la fonction host ?

---

Marc.

R.Seif

Membre

Re : optimisation de l'insertion et la selection

wé je l'ai fixé.
host fait l'extraction de l'@ IP en texte.
càd    host('192.168.1.1') --> 192.168.1.1

pardon j'ai pa fé attention, mé vous voulez dire que l'index sur mape_add_ip n'est ce pa?
parce que j'ai pas fait d'index en mape_get_ip

Dernière modification par R.Seif (19/04/2010 16:19:09)

Marc Cousin

Membre

Re : optimisation de l'insertion et la selection

Le problème est sur cet ordre :
SELECT INTO ipid ip FROM ips WHERE host(ips.ip) = host(addr)
Ce que vous demandez au moteur c'est de trouver tous les enregistrements de la table ipid qui vérifient  host(ips.ip) = host(addr)
Cela veut dire lire toute la table, et pour tous les enregistrements de la table, calculer host(ips.ip). Cela a deux conséquences :
- Beaucoup de calculs inutiles
- Impossible de trouver directement le bon enregistrement : même si vous avez un index sur la colonne ip, il est inutilisable, du fait de cette fonction.

---

Marc.