PostgreSQL La base de donnees la plus sophistiquee au monde.

Forums PostgreSQL.fr

Le forum officiel de la communauté francophone de PostgreSQL

Vous n'êtes pas identifié(e).

#1 20/03/2015 17:28:17

dvl
Membre

interpretation difficile du planificateur

Bonjour,

J'utilise un serveur 9.4 sur un ubuntu 12.04.
Sur ce serveur, j'ai 2 bases spatialisées b1 et b2

J'effectue la requête suivante sur b1 en utilisant une table distante fao_areas qui est dans b2.
select count(*) from  obs_coordgps o,effort e,campagne c,region r, fao_areas  f where o.id_effort=e.id and e.idcampagne=c.id and   e.idcampagne=1 and  c.id_region_fao=r.id and  r.code_fao=f.f_code and st_contains(f.the_geom, o.geom);

J'ai créé une table locale fao_aires_local dans b1 identique à fao_areas de b2 (mêmes champs, mêmes lignes, mêmes index, aucune autre contrainte)
(pg_stats me donne la même chose pour les 2 tables)

Or, quand j'effectue la même requête uniquement avec les bases locales à b1 :
select count(*) from  obs_coordgps o,effort e,campagne c,region r, fao_aires_local  f where o.id_effort=e.id and e.idcampagne=c.id and   e.idcampagne=1 and  c.id_region_fao=r.id and  r.code_fao=f.f_code and st_contains(f.the_geom, o.geom);

le temps d'exécution est considérablement augmenté.

J'ai fait un explain(analyze,buffers) dessus.
La différence s'observe essentiellement au scan de obs_coordgps, en particulier sur les buffers:shared hit.
Je n'arrive pas à interpréter cette différence.

Les 2 plans sont visibles à http://explain.depesz.com/s/OlP9 (pour la table distante) et http://explain.depesz.com/s/qv5L (pour la table locale).
Merci à l'avance si quelqu'un peut m'aider à comprendre ce comportement.

Hors ligne

#2 21/03/2015 00:26:57

gleu
Administrateur

Re : interpretation difficile du planificateur

On est d'accord sur la différence. Je ne vois pas trop d'explication en dehors d'un ordre des données différents. La table obs_coordgps aurait-elle été clusterisé sur un serveur et pas sur l'autre ? si vous la clusterisez à partir de l'index gist_obs_coordgps, qu'observez-vous ?


Guillaume.

Hors ligne

#3 21/03/2015 11:25:58

dvl
Membre

Re : interpretation difficile du planificateur

Merci de votre réponse.
Non, la table obs_coord_gps (ni aucune table d'ailleurs) n'a été clustérisée.
Je précise qu'il y a une très grande différence de volumétrie entre les 2 bases :  la base b2 qui contient la table distante est une base minuscule avec 2 tables de quelques lignes. la base b1 (qui contient obs_coordgps et la table fao_aires_local) est beaucoup plus volumineuse (je pourrai donner les chiffres précis et tenter la clusterisation  lundi).

Hors ligne

#4 23/03/2015 00:09:07

gleu
Administrateur

Re : interpretation difficile du planificateur

La seule raison que je vois est que la table obs_coordgps sur b1 est fortement plus volumineuse que sur b2. Ça expliquerait le nombre considérable de blocs supplémentaires lus sur b1, et du coup la lenteur de la requête.


Guillaume.

Hors ligne

#5 23/03/2015 11:28:47

dvl
Membre

Re : interpretation difficile du planificateur

C'est la même  table obs_coordgps (de la base b1) qui est utilisée dans les 2 requêtes. La seule différence concerne fao_areas et  fao_aires_local qui sont identiques mais dans des bases différentes.

Je viens de refaire tourner les requêtes en simplifiant les tables : je me suis affranchie des jointures intermédiaires en créant une table tmp_obs_coordgps qui contient uniquement les champs "utiles" : geom, idcampagne et code_fao
Et je ne me restreins plus à un seul idcampagne.

Les 2 requêtes sont
-- avec la table distante fao_areas dans b2:
select count(*) from  tmp_obs_coordgps o, fao_areas  f where o.code_fao=f.f_code and st_contains(f.the_geom, o.geom);
http://explain.depesz.com/s/51v3

-- avec la table locale fao_aires_local dans b1 :
select count(*) from  tmp_obs_coordgps o, fao_aires_local   f where o.code_fao=f.f_code and st_contains(f.the_geom, o.geom);
http://explain.depesz.com/s/ePz

Les différences entre les 2 extractions sont toujours du même ordre.

J'ai fait un
cluster  tmp_obs_coordgps using  gist_tmp_obs_coordgps;
Cela ne change rien :
http://explain.depesz.com/s/luP

Les volumétries des 2 bases :
5470 MB pour b1 et 19 MB pour b2

Je trouve bizarre la différence de "width" entre  fao_areas (548) et fao_aires_local (26336) : les requêtes sur pg_stats pour les 2 tables donnent les mêmes résultats.
Peut-être, le problème vient de là mais j'ai vraiment beaucoup de mal à interpréter ces plans d'exécution.

Dernière modification par dvl (23/03/2015 14:14:49)

Hors ligne

#6 24/03/2015 11:23:49

dvl
Membre

Re : interpretation difficile du planificateur

Je tente de relancer le problème que je trouve très intrigant d'autant plus que je viens de lire le post sur la lenteur de lecture des foreign tables.
C'est quand même curieux d'obtenir un  temps 20 fois meilleur avec une foreign table qu'avec une table (a priori rigoureusement identique) locale.

J'ai relancé à nouveau le explain analyze mais avec buffers et verbose
http://explain.depesz.com/s/4hO  pour la table distante
http://explain.depesz.com/s/BvDb pour la table locale

J'aimerais avoir un début de piste pour comprendre pourquoi dans un cas (table distante) il utilise un Bitmap Index Scan et un Bitmap Heap Scan (pour la jointure avec la table distante) et dans l'autre un Index Scan (pour la jointure avec la table locale) et pourquoi les 2 tables locale et distante annoncent des width aussi différents (dans les 2 cas, dans pg_stats, la colonne geom a un avg_width de 26330)

Merci à l'avance

Hors ligne

Pied de page des forums