Fonction de géocodage : optimisation nécessaire

Bonjour à tous,

Je possède une table possédant des adresses, définit par les champs suivants :
- country
- city
- postcode
- street
- gstreet
- housenumber

Le champ "gstreet" est pré calculé par rapport au champ "street" dont on lui a enlevé des mots tel que "de", "la", "du" etc. via la fonction fct_gstreet_from_street(country, street) qui suit :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 CREATE OR REPLACE FUNCTION fct_gstreet_from_street(argCountry character varying(255), argEntry character varying(255)) RETURNS character varying(255) AS ' DECLARE ret character varying(255); r osm_new_addresses_replace_entries%rowtype; BEGIN SELECT lower(argEntry) INTO ret; FOR r IN (SELECT * FROM osm_new_addresses_replace_entries WHERE lower(country) = lower(argCountry) ORDER BY seq ASC) LOOP SELECT replace(ret, r.strFrom, r.strTo) INTO ret; END LOOP; RETURN ret; END; 'LANGUAGE'plpgsql';
L'objectif est de faire une fonction qui prend en paramètre une adresse définie de la même manière, et qui renvoie les 30 adresses les plus crédibles, triées par un rank calculé selon quelques règles :
- On part d'un rank de 100, auquel on retire le niveau d'erreur (via la fonction levenshtein) multiplié par un poids dépendant du type de donnée erronée.
- Se tromper de numéro (housenumber) retire 1 point lorsque le numéro trouvé est sur le même trottoir (même parité) sinon 4
- Dans les adresses retournées, plutôt que de renvoyer 30 adresses toutes similaires sauf le housenumber qui change, on sélectionne le numéro le plus proche que ce que l'utilisateur a rentré dans la fonction via ceci :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 CREATE OR REPLACE FUNCTION fct_best_number(tab integer[], i integer) RETURNS INTEGER AS ' DECLARE val integer; BEGIN SELECT (select n from explode_array(tab) as n where (n % 2) = (i % 2) order by abs(i - n) limit 1) INTO val; IF val IS NULL THEN SELECT (select n from explode_array(tab) as n order by abs(i - n) limit 1) INTO val; END IF; RETURN val; END; 'LANGUAGE'plpgsql';
Puis quelques fonctions utilitaires :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 CREATE AGGREGATE to_array ( sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}' ); create or replace function explode_array(in_array anyarray) returns setof anyelement as $$ select ($1)[s] from generate_series(1,array_upper($1, 1)) as s; $$ language sql immutable; CREATE OR REPLACE FUNCTION empty2null(TEXT) RETURNS TEXT AS ' SELECT CASE WHEN ($1 ~ ''^[[:space:]]*$'') THEN NULL ELSE $1 END; 'LANGUAGE sql IMMUTABLE; CREATE OR REPLACE FUNCTION fct_extract_numbers(TEXT) RETURNS INTEGER AS ' SELECT cast(Coalesce(empty2null(regexp_replace($1, ''[^0-9]'', '''', ''g'')), ''0'') as integer); 'LANGUAGE sql IMMUTABLE;
Et enfin, voici le corps de la fonction où j'ai remplacé les arguments :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 SELECT * FROM ( SELECT (rankvalue - (abs(("number" % 2) - (15%2)) * 3) - (CASE WHEN "number"=15 THEN 0 ELSE 1 END)) as rankvalue, country, city, postcode, street, "number" FROM ( SELECT (100 - Coalesce((levenshtein('France', "country") * 10), 50) - Coalesce((levenshtein('Toulouse', "city") * 4), 50) - Coalesce((levenshtein('31000', "postcode") * 4), 50) - Coalesce((levenshtein(fct_gstreet_from_street('France', 'Rue de la Pomme'), "gstreet") * 3), 50)) as rankvalue, "country", "city", "postcode", "street", (SELECT fct_best_number(numbers, 15) FROM to_array( cast(Coalesce(empty2null(regexp_replace(housenumber, '[^0-9]', '', 'g')), '-1') as integer) ) as numbers) "number" FROM osm_new_addresses GROUP BY country, city, postcode, street, gstreet ) sub ) sub2 ORDER BY rankvalue DESC LIMIT 30
A l'échelle d'une région comme Midi-Pyrénées, 400ms c'est raisonnable, mais à l'échelle d'un Pays comme la France, ça met près de 3 minutes et ça ce n'est pas acceptable.

Voici le explain (pour Midi-Pyrénées mais si besoin ce soir je peux poster celui de la France) :

Citation:

"Limit (cost=2144.67..2144.75 rows=30 width=2072) (actual time=400.859..400.862 rows=30 loops=1)"
" Output: (((sub.rankvalue - (abs(((sub.number % 2) - 1)) * 3)) - CASE WHEN (sub.number = 15) THEN 0 ELSE 1 END)), sub.country, sub.city, sub.postcode, sub.street, sub.number"
" -> Sort (cost=2144.67..2148.52 rows=1539 width=2072) (actual time=400.858..400.860 rows=30 loops=1)"
" Output: (((sub.rankvalue - (abs(((sub.number % 2) - 1)) * 3)) - CASE WHEN (sub.number = 15) THEN 0 ELSE 1 END)), sub.country, sub.city, sub.postcode, sub.street, sub.number"
" Sort Key: (((sub.rankvalue - (abs(((sub.number % 2) - 1)) * 3)) - CASE WHEN (sub.number = 15) THEN 0 ELSE 1 END))"
" Sort Method: top-N heapsort Memory: 19kB"
" -> Subquery Scan sub (cost=1206.60..2099.22 rows=1539 width=2072) (actual time=76.066..399.739 rows=1612 loops=1)"
" Output: ((sub.rankvalue - (abs(((sub.number % 2) - 1)) * 3)) - CASE WHEN (sub.number = 15) THEN 0 ELSE 1 END), sub.country, sub.city, sub.postcode, sub.street, sub.number"
" -> HashAggregate (cost=1206.60..2056.90 rows=1539 width=57) (actual time=76.060..398.028 rows=1612 loops=1)"
" Output: ((((100 - COALESCE((levenshtein('France'::text, (osm_new_addresses.country)::text) * 10), 50)) - COALESCE((levenshtein('Toulouse'::text, (osm_new_addresses.city)::text) * 4), 50)) - COALESCE((levenshtein('31000'::text, (osm_new_addresses.postcode)::text) * 4), 50)) - COALESCE((levenshtein((fct_gstreet_from_street('France'::character varying, 'Rue de la Pomme'::character varying))::text, (osm_new_addresses.gstreet)::text) * 3), 50)), osm_new_addresses.country, osm_new_addresses.city, osm_new_addresses.postcode, osm_new_addresses.street, (SubPlan 1)"
" -> Seq Scan on osm_new_addresses (cost=0.00..975.84 rows=15384 width=57) (actual time=0.013..2.777 rows=15384 loops=1)"
" Output: osm_new_addresses.osm_id, osm_new_addresses.data_type, osm_new_addresses.type, osm_new_addresses.name, osm_new_addresses.country, osm_new_addresses.city, osm_new_addresses.postcode, osm_new_addresses.street, osm_new_addresses.housename, osm_new_addresses.housenumber, osm_new_addresses.geometry, osm_new_addresses.gstreet"
" SubPlan 1"
" -> Function Scan on numbers (cost=0.00..0.26 rows=1 width=32) (actual time=0.041..0.041 rows=1 loops=1612)"
" Output: fct_best_number(numbers.numbers, 15)"
"Total runtime: 401.103 ms"

Auriez-vous des axes d'amélioration de cette fonction ?

Merci à vous,
A bientôt