Forums PostgreSQL.fr

krashtest31

Membre

Optimisation d'une requete

Bonjour,

Je me casse les dents sur une requête inexplicablement lente.
Elle prend 26 minutes pour seulement 300 000 lignes ramenées

Tous les index sont en place, les stats sont calculées, les tables ont des VACUUM quotidien, bref ....

Voici la requête :

select
	c.compromis_id as ID_COMPROMIS,
	e.societe_id as ID_SOCIETE,
	c.affaire as ID_LOT,
	o.proprietaire_id as ID_PROPRIETAIRE,
	n_v.perso as ID_NOTAIRE_VENDEUR,
	p.perso as ID_ACHETEUR,
	n_a.perso as ID_NOTAIRE_ACHETEUR,
	e.user_id ::integer as ID_EMP_NEGO,
	c.negoext1 as ID_NEGO_EXT1,
	c.negoext2 as ID_NEGO_EXT2,
	c.negoext3 as ID_NEGO_EXT3,
	c.date as DATE_SIGN_COMPROMIS,
	c.datelimite as DATE_CONDITIONS_SUSP,
	c.dateprevuevente as DATE_PREVUE_VENTE,
	c.dateeffectivevente as DATE_EFFECTIVE_VENTE,
	c.dateencaissement as DATE_ENCAISSEMENT,
	c.datesupprvente as DATE_ANNUL_VENTE,
	c.conditionsuspensive as LIB_CONDITIONS_SUSP,
	m.prixinitiale::double precision as PRIX_INITIAL,
	m.prixvente::double precision as PRIX_VENTE,
	m.tauxcomm::double precision as TAUX_COMM,
	( m.prixvente*(m.tauxcomm/100) )::double precision as COMMISSION_AGENCE_TTC,
	c.tva::double precision as TAUX_TVA,
	( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) )::double precision as COMMISSION_AGENCE_HT,
	(m.prixvente - ( m.prixvente*(m.tauxcomm/100) ))::double precision as PRIX_NET_VENDEUR,
	c.partaffaire::double precision as POURC_PART_SORTANTE,
	c.partacheteur::double precision as POURC_PART_ENTRANTE,
	coalesce(c.partExt1,0) + coalesce(c.partExt2,0) + coalesce(c.partExt3, 0) ::double precision as POURC_PART_EXT,
	CASE
		WHEN 	c.datesupprvente is null then ( ( m.prixvente*(m.tauxcomm/100) ) / (1+(c.tva/100)) ) * ( coalesce(c.partacheteur,0) + coalesce(c.partaffaire,0) )
		ELSE 0
	END ::double precision as CA_HT,
	CASE
		WHEN c.dateencaissement IS NOT NULL THEN 'Vente encaissée'
		WHEN c.datesupprvente IS NOT NULL THEN 'Vente annulée'
		WHEN c.dateeffectivevente IS NOT NULL THEN 'Vente effective'
		WHEN c.date IS NOT NULL THEN 'Vente prévue'
		ELSE null
	END as STATUT_VENTE

from
	ods_transac.compromis c
		LEFT OUTER JOIN ods_transac.prospect p ON ( c.acheteur=p.prospect_id )
		LEFT OUTER JOIN ods_transac.employe e ON ( p.nego_id = e.user_id )
		LEFT OUTER JOIN ods_transac.lot o ON ( c.affaire = o.lot_id )
		LEFT OUTER JOIN ods_transac.mandat_transaction m ON ( o.mandat_id = m.mandat_id )
		LEFT OUTER JOIN ods_transac.notaire n_a ON ( c.notaireacheteur = n_a.notaire_id )
		LEFT OUTER JOIN ods_transac.notaire n_v ON ( c.notairevendeur = n_v.notaire_id )

where
	c.flag_integration = -1

et voici l'explain analyze : https://explain.depesz.com/s/k6OI

Si vous avez la moindre piste ...
Merci d'avance

genamiga

Membre

Re : Optimisation d'une requete

C'est effectivement très long...

Connexion locale ou distante ?

Combien de lignes contiennent les tables concernées ?

Quelle est la configuration matérielle ?

Quelle version de PostgreSQL ?

A partir de la 9.6 tu as Parallel JOIN, aggregate et Parallel query
A partir le la 10.x tu as Parallel bitmap heap scans, Parallel B-tree index scans et Parallel merge joins

Cela peut aider...

Quoique avec 300 000 lignes retournées...

Dernière modification par genamiga (25/05/2018 11:56:24)

gleu

Administrateur

Re : Optimisation d'une requete

Il y a une énorme sous-estimation du nombre de lignes renvoyés par le parcours d'index sur la table lot (1 ligne estimée en retour, 370540 lignes renvoyées). Il serait intéressant de savoir pourquoi et de corriger cela. Cela permettrait d'avoir un plan plus intéressant.

---

Guillaume.

krashtest31

Membre

Re : Optimisation d'une requete

Bonjour et merci de vos réponses

> Connexion locale ou distante ?
Il s'agit d'une connexion distante mais j'ai exécuté la requête sur le serveur par acquis de conscience, les temps d’exécution sont identiques

> Combien de lignes contiennent les tables concernées ?
ods_transac.compromis 34 130
ods_transac.prospect 250 145
ods_transac.employe 2 264
ods_transac.lot 371 383
ods_transac.mandat_transaction 372 158
ods_transac.notaire 36 083

> Quelle est la configuration matérielle ?
3 CPU Xeon 2.5 GHz, 16 Go RAM, baie de disque SSD en RAID

> Quelle version de PostgreSQL ?
9.4

> Il y a une énorme sous-estimation du nombre de lignes renvoyés par le parcours d'index sur la table lot (1 ligne estimée en retour, 370540 lignes renvoyées).
> Il serait intéressant de savoir pourquoi et de corriger cela. Cela permettrait d'avoir un plan plus intéressant.

J'utilise le paramètre "default_statistics_target" à 400 dans le "postgresql.conf"
il y a un ANALYZE tous les soirs (et un VACUUM FULL ANALYZE tous les week-end)
Que puis-je faire d'autre ?

J'ajoute que c'est une des seules requêtes qui me pose problème, les autres du même type s’exécutent parfaitement
dommage qu'on ne puisse pas ajouter des HINT comme Oracle...
Il y a par exemple un index sur le champ "affaire" de la table "compromis" qui n'est pas utilisé, je me demande pourquoi ? ( pour la jointure c.affaire = o.lot_id )