Re: requête plus rapide avec une table distante

Merci à Daniel Verite et Dimitri Fontaine pour leurs réponses.

En fait, la requête initiale qui m'a posé problème était plus complexe
et faisait appel à plusieurs jointures.
J'ai tenté de circonscrire le problème et j'ai créé la table
tmp_obs_coordgps et son index gist_tmp_obs_coordgps qui n'a donc pas eu
le temps de devenir "obèse" ;-)
Malgré tout, j'ai lancé un reindex et le explain : pas de différences
significatives dans les durées (http://explain.depesz.com/s/iSjH)

Dans la requête initiale (http://explain.depesz.com/s/qv5L) avec toutes
les jointures, le plan d'éxecution utilisait aussi un Bitmap Index Scan
(et Bitmap Heap Scan) dans la requête avec la table locale et on voit
déjà un nombre de buffers beaucoup plus important qu'avec la table
distante, le problème vient effectivement de là.
Je suis quand même très désorientée par ces différences importantes
(qu'est-ce qui empêche avec la table locale d'utiliser la technique
utilisée pour la table distante qui est considérablement plus rapide ?).
J'ajoute que j'ai essayé en modifiant l'ordre des requêtes pour jouer
avec le cache mais les temps restent toujours dans les mêmes ordres de
grandeur.

Dominique

Le 25/03/2015 18:13, Dimitri Fontaine a écrit :
> Dominique Vallée <dominique(dot)vallee(at)mnhn(dot)fr> writes:
>> Je donne les liens des explain analyze pour les 2 requêtes :
> On remarque les éléments suivants :
>
> - le scan séquentiel de la table locale prend 0.320 ms
>
> Seq Scan on public.fao_aires_local f
> (cost=0.00..88.22 rows=322 width=26,336)
> (actual time=0.008..0.320 rows=322 loops=1)
> Output: f.gid, f.f_code, f.f_level, f.ocean, f.subocean, f.the_geom
> Buffers: shared hit=85
>
> - le scan séquentiel de la table distante prend 293 ms, soit presque
> mille fois plus de temps
>
> Foreign Scan on public.fao_areas f
> (cost=100.00..114.29 rows=143 width=548)
> (actual time=203.733..293.851 rows=322 loops=1)
> Output: f.gid, f.f_code, f.f_level, f.ocean, f.subocean, f.the_geom
> Remote SQL: SELECT f_code, the_geom FROM public.fao_areas
>
> - le plan d'exécution avec une table distante privilégie un Bitmap
> Index Scan qui prend 5 ms
>
> Bitmap Index Scan on gist_tmp_obs_coordgps
> (cost=0.00..4.60 rows=50 width=0)
> (actual time=5.141..5.141 rows=7,582 loops=322)
> Index Cond: (f.the_geom && o.geom)
> Buffers: shared hit=13925 read=3351
>
> - le plan d'exécution local privilégie un Index Scan qui prend 1811 ms
>
> Index Scan using gist_tmp_obs_coordgps on public.tmp_obs_coordgps o
> (cost=0.29..69.70 rows=3 width=35)
> (actual time=1.827..1,811.050 rows=1,552 loops=322)
> Output: o.geom, o.idcampagne, o.code_fao
> Index Cond: (f.the_geom && o.geom)
> Filter: (((f.f_code)::text = o.code_fao) AND _st_contains(f.the_geom, o.geom))
> Rows Removed by Filter: 6030
> Buffers: shared hit=20678741
>
> Le point étonnant ici est le nombre de buffers que le plan Index Scan
> doit lire (depuis les shared buffers, donc en mémoire, mais quand
> même) : 20 millions de buffers lus contre moins de 14 mille pour le
> bitmap index scan.
>
> Pas le temps de procéder à une analyze plus détaillée, voilà simplement
> les éléments qui me sautent au yeux, en espérant que ça aide déjà.
>

--
Dominique Vallée
UMS 3468 Bases de données sur la Biodiversité, Ecologie, Environnement et Sociétés (BBEES)
MNHN - Muséum national d'Histoire naturelle
01 40 79 53 70

--
Envoi via la liste pgsql-fr-generale (pgsql-fr-generale(at)postgresql(dot)org)