Le portail francophone de la géomatique

Postgis et QGIS ont chacun leurs algorithmes pour le traitement géo. Donc oui, il peut exister de grosses différences de perf suivant les opérations spatiales.
Pour certaines d'entre elles, QGIS se sert de GEOS (a travers ogr par exemple). GEOS est une bibliothèque spatiale qui est aussi utilisée dans PostGIS.
Même dans ce cas, il peut y avoir des différences notables de perf pour la même opération.

Nico

Quelle est la ram allouée à PG ? la base a été réglée pour la machine, ou c'est la conf par défaut qui est active ?
(show shared_buffers)

Les disques aussi ont leur importance (surtout lors du premier accès aux données, pour les mettre en ram). la bd est sur un disque dédié, rapide, ou sur le meme disque que l'OS ?

avez-vous lancé un analyze <table> apres la création de l'index géo ?

Nicolas

Je vous recommande la lecture de la doc  sur la conf postgresql: peu de paramètres a changer, mais certains ont une grande influence (https://www.postgresql.org/docs/9.6/run … ource.html).

Si, bien sur, on peut mettre beaucoup de 512 MB aux shared buffers ! Heureusement d'ailleurs: cette mémoire est celle qui stocke les données pendant une requête.
Juste pour rire, la conf du serveur LeBonCoin, PostgreSQL, en 2012:

(https://www.postgresql.fr/temoignages/le_bon_coin)

Il y a des sites en ligne qui permettent de tuner un peu son serveur en fonction de ses usages (ici par exemple: https://pgtune.leopard.in.ua/#/)

Si votre serveur ne fait quasiment que du PG, vous pouvez mettre 1/4 à 1/3 de la ram pour les shared_buffers.

Nicolas

J'ai utilisé récemment PGTunes (https://pgtune.leopard.in.ua/#/) qui permet de configurer des paramètres assez important dans le fichier postgresql.conf. Par contre faite une sauvegarde de ce fichier avant toute modification et bien penser a arrêter le "service" postgresql  avant toute modification et  le redémarrer par la suite avant de lancer l'interface de commande

Hello !

Je crois que c'est plutot la commande CLUSTER TABLE (https://www.postgresql.org/docs/12/sql-cluster.html) qui permet de réorganiser les données de la table en suivant l'ordre d'un index: lors de l'appel a l'index, les données qu'il faut aller chercher dans la table sont alors ordonnées physiquement comme l'index => plus rapide a aller chercher (surtout sur des disques mécaniques ou la localisation physique des données sur le disque est importante pour la perf).
Lors de la création, un index est écrit dans le bon ordre par défaut (ordre naturel de l'operateur d'index).
REINDEX est surtout utile lorsque l'index est cassé, ou que la table a subit de nombreuses modifs (insert/update/delete).

L'opérateur distance <-> déclenche bien l'usage des index, meme dans le cas d'un self join. (la partie lateral rendant un des champs constant vis a vis de l'index, je crois).
C'est bien l'intéret de ce "nouvel" opérateur dans PostGIS. Il évite d'utiliser des distances ou buffers, qui par nature ne permettent pas de trouver une réponse exacte: il y a aura tjs un dataset dans lequel la distance min entre deux objets est supérieure au buffer qu'on utilise. Et si on utilise un trop gros buffer, l'index n'est plus selectif et donc inutile.

Le plan de la requête KNN:

QUERY PLAN
Nested Loop  (cost=0.29..571924.24 rows=1000000 width=48) (actual time=0.275..125458.410 rows=999999 loops=1)
  Output: p.id, r.id, ((p.geom <-> r.geom)), st_makeline(p.geom, r.geom)
  Buffers: shared hit=15148569
  ->  Seq Scan on work_nico.testpt p  (cost=0.00..18264.00 rows=1000000 width=36) (actual time=0.011..88.278 rows=1000000 loops=1)
        Output: p.geom, p.id
        Buffers: shared hit=8264
  ->  Limit  (cost=0.29..0.53 rows=1 width=44) (actual time=0.124..0.124 rows=1 loops=1000000)
        Output: r.id, ((p.geom <-> r.geom)), r.geom
        Buffers: shared hit=15140305
        ->  Index Scan using testpt_geom_gist on work_nico.testpt r  (cost=0.29..82053.62 rows=333333 width=44) (actual time=0.124..0.124 rows=1 loops=1000000)
              Output: r.id, (p.geom <-> r.geom), r.geom
              Order By: (r.geom <-> p.geom)
              Filter: (p.id < r.id)
              Rows Removed by Filter: 11
              Buffers: shared hit=15140305
Planning time: 0.350 ms
Execution time: 125517.935 ms

(la meme requete, sur 10000 lignes au lieu de 1M, sans index, prend deja 33s)

Nico

Avec 128 Mo de ram, il se peut que toutes les données ne tiennent pas en mémoire, PG doit alors les stocker dans des fichiers tmp et les traiter par lot.

La volumétrie de ma table de test (1M de lignes), avec index geo:

select pg_size_pretty(pg_total_relation_size('work_nico.testpt')) as tot_size,
       pg_size_pretty(pg_relation_size('work_nico.testpt')) as rel_size,
       pg_size_pretty(pg_indexes_size('work_nico.testpt')) as indexes_size;

tot_size    rel_size    indexes_size
142 MB    65 MB    77 MB

Où l'index est plus volumineux que les data elles-mêmes

(un exemple plus extreme: la table osm_polygon france, avec index sur tous les champs: index 3.5x plus gros que les data !
tot_size    rel_size    indexes_size
98 GB    22 GB    76 GB
)

Nico

Non, non, c'était intéressant

Oui j'ai corrigé la mauvaise commande et la mauvaise information sur l'utilisation de l'index.

REINDEX concerne en effet la reconstruction d'index corrompus ou bloatés.

Pour l'opérateur <->, la doc m'a un peu induit en erreur, je pense qu'elle devrait être un peu
plus explicite, non ?

C'est la réflexion que je me suis faite après avoir écrit mon message, j'étais pas vraiment sur et j'ai fait un test,
dans un premier temps avec 1G points, j'ai vite abandonné en attendant la construction de l'index :p,
pour me borner a 1M.

L'utilisation du buffer permet tout de même de réduire le temps d’exécution ( ~4 fois moins dans mon test ), mais cela peut être au prix
de l'exhaustivité. Tout dépends du cas d'utilisation je dirais.

Dernière modification par tumasgiu (Sat 04 April 2020 14:01)