Forums PostgreSQL.fr

gom

Membre

Index plus pris en compte si utilisation de fonctions ?!

Bonjour,

Je viens de créer une fonction me permettant de retourner un numéro de semaine à partir d'un numéro de mois (voir jeu d'essai en commentaire dans la fonction). Je suis obligé de faire ainsi car notre table calendrier ne suit pas le calendrier officiel ... spécificité client ... bref !

-- Function: mon_schema.f_sem_deb_intervalle(character varying, integer, integer)

-- DROP FUNCTION mon_schema.f_sem_deb_intervalle(character varying, integer, integer);

CREATE OR REPLACE FUNCTION mon_schema.f_sem_deb_intervalle(annee_mois character varying, nb_annee_deduire integer, nb_mois_deduire integer)
  RETURNS character varying AS
$BODY$
declare

	v_annee integer;
	v_mois integer;
	v_annee_final integer;
	v_mois_final integer;
	v_annee_mois_final character varying(7);
	v_annee_sem_deb character varying(7);

begin

	-- Jeu d'essai : f_sem_deb_intervalle(2010M03, 1, 6)

	-- Année extraite à partir du paramètre correspondant à l'année et mois de référence "annee_mois"
	v_annee := substr(annee_mois, 1, 4); -- 2010

	-- Mois extrait à partir du paramètre correspondant à l'année et mois de référence "annee_mois"
	v_mois := substr(annee_mois, 6, 2); -- 3

	v_annee_final := v_annee - nb_annee_deduire; -- 2009

	-- "- 1", car on ne déduit pas le mois courant
	if nb_mois_deduire = 1 then
		v_mois_final := v_mois - 1;
	else
		if nb_mois_deduire > 1 then
			v_mois_final := v_mois - (nb_mois_deduire - 1); -- 3 - (6 - 1) = -2
		else
			v_mois_final := v_mois;
		end if;
	end if;

	if v_mois_final <= 0 then -- oui
		v_mois_final := 12 + v_mois_final; -- 12 + -2 = 10
		v_annee_final := v_annee_final - 1; -- 2008
	end if;

	v_annee_mois_final := trim(to_char(v_annee_final, '0000')) || 'M' || trim(to_char(v_mois_final, '00')); -- 2008M10

	v_annee_sem_deb := (SELECT anneesem FROM mon_schema.calendrier WHERE anneemois = v_annee_mois_final AND prem_sem_mois = 1); -- 2008S40

	return v_annee_sem_deb;
end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION mon_schema.f_sem_deb_intervalle(character varying, integer, integer) OWNER TO user_mon_schema;

Le problème est que désormais, mon index sur ma table "calendrier" n'est plus utilisé ?!

La requête où cette fonction est utilisée :

SELECT
  mon_schema.ref_accords.code_gt,
  mon_schema.calendrier.annee,
  mon_schema.calendrier.semaine,
  mon_schema.calendrier.mois_affectation,
  mon_schema.calendrier.num_mois_affectation,
  mon_schema.type_marque.libelle_court_tmarq,
  mon_schema.ref_accords.AADR_RAISSOC,
  mon_schema.gtin.gencode,
  sum(mon_schema.vte_hbd.ca_ht_avec_droit_taxes),
  sum(mon_schema.vte_hbd.marge_val_hbd),
  case when sum(mon_schema.vte_hbd.ca_ht_avec_droit_taxes) = 0
  then 0
  else sum(mon_schema.vte_hbd.marge_val_hbd)/sum(mon_schema.vte_hbd.ca_ht_avec_droit_taxes)
end
,
  sum(mon_schema.vte_hbd.3NET_COUT_HBD_HOP),
  sum(mon_schema.vte_hbd.3NET_COUT_HBD_OP),
  sum(mon_schema.vte_hbd.Marge_val_hbd_OP),
  sum(mon_schema.vte_hbd.Marge_val_hbd_HOP)
FROM
  mon_schema.ref_accords INNER JOIN mon_schema.gtin ON (mon_schema.gtin.aacc_number=mon_schema.ref_accords.aacc_numero)
   INNER JOIN mon_schema.vte_hbd ON (mon_schema.vte_hbd.gtin=mon_schema.gtin.gencode)
   INNER JOIN mon_schema.calendrier ON (mon_schema.vte_hbd.anneesem=mon_schema.calendrier.anneesem)
   INNER JOIN mon_schema.type_marque ON (mon_schema.type_marque.cod_tmarq= mon_schema.gtin.cod_tmarq)
  
WHERE
( mon_schema.gtin.gencode NOT LIKE '%000000'  )
  AND  
  (
   ( mon_schema.calendrier.anneesem between mon_schema.f_sem_deb_intervalle(( '2010M03' ), 0, 0) and mon_schema.f_sem_fin_intervalle(( '2010M03' ), 0, 0)  )
   AND
   ( mon_schema.ref_accords.code_gt in ('1', '2', '3', '5') or ('*' in ('1', '2', '3', '5')) )
  )
GROUP BY
  mon_schema.ref_accords.code_gt, 
  mon_schema.calendrier.annee, 
  mon_schema.calendrier.semaine, 
  mon_schema.calendrier.mois_affectation, 
  mon_schema.calendrier.num_mois_affectation, 
  mon_schema.type_marque.libelle_court_tmarq, 
  mon_schema.ref_accords.AADR_RAISSOC, 
  mon_schema.gtin.gencode

Le plan d'exécution :

GroupAggregate  (cost=4195170.72..4370739.46 rows=2128106 width=82)
  ->  Sort  (cost=4195170.72..4200490.98 rows=2128106 width=82)
        Sort Key: ref_accords.code_gt, calendrier.annee, calendrier.semaine, calendrier.mois_affectation, calendrier.num_mois_affectation, type_marque.libelle_court_tmarq, ref_accords.aadr_raissoc, gtin.gencode
        ->  Hash Join  (cost=64108.33..3971494.66 rows=2128106 width=82)
              Hash Cond: ((vte_hbd.gtin)::text = (gtin.gencode)::text)
              ->  Hash Join  (cost=1373.00..3832581.68 rows=10979318 width=57)
                    Hash Cond: ((vte_hbd.anneesem)::text = (calendrier.anneesem)::text)
                    ->  Seq Scan on vte_hbd  (cost=0.00..3337384.00 rows=102408400 width=46)
                    ->  Hash  (cost=1369.74..1369.74 rows=261 width=27)
                          ->  Seq Scan on calendrier  (cost=0.00..1369.74 rows=261 width=27)
                                Filter: (((anneesem)::text >= (mon_schema.f_sem_deb_intervalle('2010M03'::character varying, 0, 0))::text) AND ((anneesem)::text <= (mon_schema.f_sem_fin_intervalle('2010M03'::character varying, 0, 0))::text))
              ->  Hash  (cost=60825.83..60825.83 rows=152760 width=39)
                    ->  Hash Join  (cost=4462.64..60825.83 rows=152760 width=39)
                          Hash Cond: (gtin.cod_tmarq = type_marque.cod_tmarq)
                          ->  Hash Join  (cost=4461.55..58681.38 rows=164205 width=40)
                                Hash Cond: ((gtin.aacc_number)::text = (ref_accords.aacc_numero)::text)
                                ->  Seq Scan on gtin  (cost=0.00..43820.88 rows=583793 width=39)
                                      Filter: ((gencode)::text !~~ '%000000'::text)
                                ->  Hash  (cost=4336.48..4336.48 rows=10006 width=44)
                                      ->  Seq Scan on ref_accords  (cost=0.00..4336.48 rows=10006 width=44)
                                            Filter: (((code_gt)::text = ANY (('{1,2,3,5}'::character varying[])::text[])) OR ('*'::text = ANY ('{1,2,3,5}'::text[])))
                          ->  Hash  (cost=1.04..1.04 rows=4 width=7)
                                ->  Seq Scan on type_marque  (cost=0.00..1.04 rows=4 width=7)

Et, enfin, voici l'index qui est utilisé lorsque je n'utilise pas mes fonctions, mais que je passe en dur les numéros de semaine (mon_schema.calendrier.anneesem between '2010S09' and '2010S13') :

-- Index: mon_schema.calendrier_anneesem_idx

-- DROP INDEX mon_schema.calendrier_anneesem_idx;

CREATE INDEX calendrier_anneesem_idx
  ON mon_schema.calendrier
  USING btree
  (anneesem)
TABLESPACE "TBS_DIM_INDEX_F";

Gôm

PS : "Problème bonus" aucun index n'est utilisé du tout ... ? Je pense que c'est anormal concernant la table "vte_hbd", non ?

Dernière modification par gom (30/04/2010 17:03:28)

gleu

Administrateur

Re : Index plus pris en compte si utilisation de fonctions ?!

PostgreSQL n'utilise pas l'index car il n'a aucune idée, au moment de l'établissement du plan d'exécution, de la valeur qui va être retournée par la fonction. Or cette valeur, elle peut soit valider l'utilisation de l'index soit valider sa non-utilisation. Et comme il doute, il préfère ne pas prendre en compte l'index.

---

Guillaume.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Aïe, mais moi je sais bien que c'est mieux avec !

Pas moyen de forcer l'utilisation de l'Index ?

gleu

Administrateur

Re : Index plus pris en compte si utilisation de fonctions ?!

Si, en n'utilisant pas cette fonction.

---

Guillaume.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Heu bah comment je peux faire autrement ?! Je suis bien obligé d'utiliser ma fonction !

Marc Cousin

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Je pense que passer la fonction en immutable suffirait à obtenir le bon plan gom : en faisant cela, tu dis à l'optimiseur que la fonction est constante à paramètre connu, et il peut la substituer par sa valeur avant de planifier la requête.
CF le paragraphe sur IMMUTABLE dans http://docs.postgresql.fr/8.4/sql-createfunction.html

---

Marc.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Yououuuuuuuuuuh ! Marc Cousin : roi de PostgreSQL !

Merci beaucoup, ça fonctionne grâce à IMMUTABLE.

Gôm

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Je crois que ma requête peut encore être optimisée, mais je vais encore avoir besoin de vos conseils.

http://explain.depesz.com/s/BlJ

HashAggregate  (cost=3809696.22..3813839.33 rows=110483 width=82) (actual time=348760.149..349421.049 rows=363524 loops=1)
  ->  Hash Join  (cost=55653.34..3805000.69 rows=110483 width=82) (actual time=11838.695..347702.662 rows=363524 loops=1)
        Hash Cond: (gtin.cod_tmarq = type_marque.cod_tmarq)
        ->  Hash Join  (cost=55652.25..3803449.02 rows=118867 width=83) (actual time=11829.967..347307.814 rows=381684 loops=1)
              Hash Cond: ((gtin.aacc_number)::text = (ref_accords.aacc_numero)::text)
              ->  Hash Join  (cost=51190.15..3791486.94 rows=420754 width=82) (actual time=11470.691..345928.471 rows=1209316 loops=1)
                    Hash Cond: ((vte_hbd.gtin)::text = (gtin.gencode)::text)
                    ->  Hash Join  (cost=20.66..3727415.76 rows=632302 width=57) (actual time=18.839..327313.381 rows=4067925 loops=1)
                          Hash Cond: ((vte_hbd.anneesem)::text = (calendrier.anneesem)::text)
                          ->  Seq Scan on vte_hbd  (cost=0.00..3337134.24 rows=102383424 width=46) (actual time=7.542..287561.365 rows=102383428 loops=1)
                          ->  Hash  (cost=20.47..20.47 rows=15 width=27) (actual time=11.156..11.156 rows=5 loops=1)
                                ->  Index Scan using calendrier_anneesem_idx on calendrier  (cost=0.00..20.47 rows=15 width=27) (actual time=11.144..11.150 rows=5 loops=1)
                                      Index Cond: (((anneesem)::text >= '2010S09'::text) AND ((anneesem)::text <= '2010S13'::text))
                    ->  Hash  (cost=43809.61..43809.61 rows=588790 width=39) (actual time=11451.665..11451.665 rows=586767 loops=1)
                          ->  Seq Scan on gtin  (cost=0.00..43809.61 rows=588790 width=39) (actual time=7.632..11042.778 rows=586767 loops=1)
                                Filter: ((gtin)::text !~~ '%000000'::text)
              ->  Hash  (cost=4336.48..4336.48 rows=10050 width=44) (actual time=359.230..359.230 rows=10602 loops=1)
                    ->  Seq Scan on ref_accords  (cost=0.00..4336.48 rows=10050 width=44) (actual time=5.497..350.629 rows=10602 loops=1)
                          Filter: (((rgtp_code)::text = ANY (('{1,2,3,5}'::character varying[])::text[])) OR ('*'::text = ANY ('{1,2,3,5}'::text[])))
        ->  Hash  (cost=1.04..1.04 rows=4 width=7) (actual time=8.716..8.716 rows=4 loops=1)
              ->  Seq Scan on type_marque  (cost=0.00..1.04 rows=4 width=7) (actual time=8.708..8.711 rows=4 loops=1)
Total runtime: 349521.383 ms

Je ne vois pas pourquoi il fait un Seq Scan sur "vte_hbd" étant donné que j'ai cet Index là :

-- Index: mon_schema.vte_hbd_a_s_idx

-- DROP INDEX mon_schema.vte_hbd_a_s_idx;

CREATE INDEX vte_hbd_a_s_idx
  ON mon_schema.vte_hbd
  USING btree
  (anneesem)
TABLESPACE "TBS_DIM_INDEX_F";

Dernière modification par gom (03/05/2010 09:37:37)

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Peut-être devrais-je créer un Index qui inclus également mon champ "gtin" étant donné qu'il fait également partie de la jointure ?

-- Index: mon_schema.vte_hbd_idx_2

-- DROP INDEX mon_schema.vte_hbd_idx_2;

CREATE INDEX vte_hbd_idx_2
  ON mon_schema.vte_hbd
  USING btree
  (gtin, anneesem)
TABLESPACE "TBS_FACT_INDEX_F";

Dernière modification par gom (03/05/2010 09:46:29)

Marc Cousin

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Je pense que le problème provient du fait que PostgreSQL ne déduise pas de :
( mon_schema.calendrier.anneesem between mon_schema.f_sem_deb_intervalle(( '2010M03' ), 0, 0) and mon_schema.f_sem_fin_intervalle(( '2010M03' ), 0, 0)  )
et
INNER JOIN mon_schema.calendrier ON (mon_schema.vte_hbd.anneesem=mon_schema.calendrier.anneesem)

que
vte_hbd.anneesem est dans le même between.
L'optimiseur le fait pour les égalités, mais pas pour les ranges pour le moment. Peut être qu'en rajoutant cette information à la requête, les performances seraient meilleures ?

---

Marc.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Peut être qu'en rajoutant cette information à la requête, les performances seraient meilleures ?

Comment puis-je ?

Marc Cousin

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

En rajoutant mon_schema.vte_hbd.anneesem between mon_schema.f_sem_deb_intervalle(( '2010M03' ), 0, 0) and mon_schema.f_sem_fin_intervalle(( '2010M03' ), 0, 0)
dans la clause WHERE.

---

Marc.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Résout un problème mais en créer (ou fait apparaître) un autre :

http://explain.depesz.com/s/Cpv

HashAggregate  (cost=673404.59..673525.26 rows=3218 width=82) (actual time=341560.831..342227.067 rows=363524 loops=1)
  ->  Hash Join  (cost=4463.19..673267.82 rows=3218 width=82) (actual time=465.039..340202.721 rows=363524 loops=1)
        Hash Cond: (gtin.cod_tmarq = type_marque.cod_tmarq)
        ->  Hash Join  (cost=4462.10..673221.57 rows=3462 width=83) (actual time=457.420..339759.613 rows=381684 loops=1)
              Hash Cond: ((gtin.aacc_number)::text = (ref_accords.aacc_numero)::text)
              ->  Nested Loop  (cost=0.00..668541.01 rows=12256 width=82) (actual time=79.743..337961.877 rows=1209316 loops=1)
                    ->  Nested Loop  (cost=0.00..612224.71 rows=17820 width=57) (actual time=51.770..130110.709 rows=4067925 loops=1)
                          ->  Index Scan using calendrier_anneesem_idx on calendrier  (cost=0.00..20.47 rows=15 width=27) (actual time=12.188..12.198 rows=5 loops=1)
                                Index Cond: (((anneesem)::text >= '2010S09'::text) AND ((anneesem)::text <= '2010S13'::text))
                          ->  Index Scan using vte_hbd_a_s_idx on vte_hbd  (cost=0.00..40525.33 rows=23063 width=46) (actual time=10.479..25410.726 rows=813585 loops=5)
                                Index Cond: (((vte_hbd.anneesem)::text >= '2010S09'::text) AND ((vte_hbd.anneesem)::text <= '2010S13'::text) AND ((vte_hbd.anneesem)::text = (calendrier.anneesem)::text))
                    ->  Index Scan using gtin_idx on gtin  (cost=0.00..3.15 rows=1 width=39) (actual time=0.049..0.049 rows=0 loops=4067925)
                          Index Cond: ((gtin.gencode)::text = (vte_hbd.gtin)::text)
                          Filter: ((gtin.gencode)::text !~~ '%000000'::text)
              ->  Hash  (cost=4336.48..4336.48 rows=10050 width=44) (actual time=362.980..362.980 rows=10602 loops=1)
                    ->  Seq Scan on ref_accords  (cost=0.00..4336.48 rows=10050 width=44) (actual time=3.910..354.297 rows=10602 loops=1)
                          Filter: (((code_gt)::text = ANY (('{1,2,3,5}'::character varying[])::text[])) OR ('*'::text = ANY ('{1,2,3,5}'::text[])))
        ->  Hash  (cost=1.04..1.04 rows=4 width=7) (actual time=7.604..7.604 rows=4 loops=1)
              ->  Seq Scan on type_marque  (cost=0.00..1.04 rows=4 width=7) (actual time=7.596..7.599 rows=4 loops=1)
Total runtime: 342332.099 ms

Dernière modification par gom (03/05/2010 15:07:35)

Marc Cousin

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

En fait, ce qu'on voit de la requête, c'est qu'on génère 4 millions d'enregistrements sur la jointure de calendrier, vte_hbd, et qu'ensuite, les jointures et filtres sur les autres tables font redescendre le nombre d'enregistrements sélectionnés : On divise par 4 en jointurant sur gtin et son gencode, puis encore par 4 en jointurant sur ref_accords.

On a donc 2 possibilités je pense :
- Soit les critères discriminants sont éclatés dans plusieurs tables, et dans ce cas, on ne fera pas beaucoup mieux : il faudra de toutes façons ramener trop d'enregistrements quelque part, puis filtrer sur les autres tables
- Soit il y a des critères très discriminants sur une des tables, et PostgreSQL ne le voit pas. La seule chose qui est sûre, c'est que la clause WHERE rajoutée n'était pas une si bonne idée : elle a induit PostgreSQL en erreur, malgré le temps d'exécution légèrement meilleur. Les estimations d'enregistrements sont très fausses, maintenant.

La question est donc : y a t'il une des contraintes de la clause WHERE qui aurait du être utilisable pour restreindre fortement le nombre d'enregistrements ?

---

Marc.

gom

Membre

Re : Index plus pris en compte si utilisation de fonctions ?!

Aïe ... j'ai 3 critères discriminants et chacun porte sur une table différente ! Je crois que je suis bloqué, je ne vais pas pouvoir faire mieux concernant le temps d'exécution de cette requête.

Quoi que ... je crois me rappeler que tu m'avais parlé de la possibilité de créer un Index sur lequel on ajoutait un critère, non ?

Je m'explique ...

mon_schema.gtin.gencode NOT LIKE '%000000'

Suite à la mise en place d'une nouvelle règle métier au sein de l'application qui s'appuie sur mon entrepôt de données, il faut toujours que ce critère soit ajouté à toutes les requêtes qui utilisent la table "gtin". N'est-il pas possible que ce critère discriminant soit "forcé" sans pour autant être mis systématiquement dans la clause "WHERE" ?

Je pense justement à ce que tu m'avais expliqué sur les Index avec conditions ... me gourais-je complètement ?!

Gôm

Dernière modification par gom (04/05/2010 09:36:36)

gleu

Administrateur

Re : Index plus pris en compte si utilisation de fonctions ?!

Il faut savoir que PostgreSQL ne peut pas utiliser un index quand on utilise la notation % dans ces cas là : '%qqc' et '%qqc%' (il peut l'utiliser dans le cas d'un 'qqc%'). À la rigueur, il faudrait changer le filtre par celui-ci : substr(contenu, length(contenu)-5, length(contenu)) <> '000000'. Du coup, il serait possible ensuite de créer un index sur cette partie. Mais bon, comme il s'agit d'un sous-ensemble certainement important, il y a peu de chance que l'index soit utilisé. En fait, la question intéressante est : quelle est la proportion de lignes où mon_schema.gtin.gencode correspond à '%000000' et quelle est la proportion de lignes où mon_schema.gtin.gencode ne correspond pas à '%000000'. À partir de là, il est possible de dire si un index, quelqu'il soit, sera intéressant.

gencode a une taille fixe ?

---

Guillaume.