Forums PostgreSQL.fr

gleu

Administrateur

Re : Optimisation filtre entre 2 tables de relation */*

Donc là, on a confirmation qu'il ne récupère que 17 blocs en cache, ce qui est proprement hallucinant.

Première fois que vous mentionnez l'utilisation de windows. La configuration du paramètre shared_buffers est très différente entre windows et unix. On peut aller très loin avec unix. Sous windows, il est conseillé de rester prudent avec des valeurs allant entre 512 Mo et 1 Go. Ceci étant dit, cela ne changera pas le comportement de PostgreSQL ici que je ne comprends pas.

Que renvoie la requête suivante : SHOW shared_buffers; ?

---

Guillaume.

landalvic

Membre

Re : Optimisation filtre entre 2 tables de relation */*

ça renvoie "8GB"

Effectivement, je n'ai pas mentionné l'utilisation de Windows, désolé ! C'est juste que au début, j'étais parti sur le fait que le projet n'avait pas commencé, donc les données que j'ai sont juste des exemples, mais j'en aurais beaucoup plus en vrai (mais que je pourrais partitionner par source au besoin). Là j'ai essayé de charger la source la plus volumineuse pour voir et tester les performances.
Du coup, je ne m'attendais pas tant à bien paramétrer un serveur (même si en fait, les paramètres par défaut de Postgres sont ultra bas, donc ça change pas mal de chose), mais je pensais que ma requête devait pas être bien fichu pour être aussi lente. Ou alors, on m'aurait peut-être conseillé d'aller vers le noSQL que je ne connais pas ? Ou utiliser des nouvelles fonctions Postgres 11 que je ne connaîtrais pas ?

philippesb

Membre

Re : Optimisation filtre entre 2 tables de relation */*

Bonjour,

Je m'immisce dans votre discussion, je suis débutant sur Postgresql mais as-tu essayé de changer la valeur de "effective_cache_size" et de la mettre à 4 ou 8GB.

landalvic

Membre

Re : Optimisation filtre entre 2 tables de relation */*

Salut !

Merci pour ta remarque. J'ai changé le effective_cache_size pour le mettre à 8GB (il était à 4GB) et j'ai redémarré mon pc.
Le temps de requête est toujours de 15s, après je ne sais pas si ça a changé quelque chose car je ne sais pas bien décrypter le explain :

"Finalize GroupAggregate  (cost=2317589.09..2337078.43 rows=381 width=8) (actual time=13870.584..14447.299 rows=47366 loops=1)"
"  Output: id_observation"
"  Group Key: om.id_observation"
"  Filter: (count(*) = 9)"
"  Rows Removed by Filter: 2970274"
"  Buffers: shared hit=17 read=647524"
"  ->  Gather Merge  (cost=2317589.09..2335364.49 rows=152350 width=16) (actual time=13326.621..14455.302 rows=3116698 loops=1)"
"        Output: id_observation, (PARTIAL count(*))"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=31 read=1651415"
"        ->  Sort  (cost=2316589.06..2316779.50 rows=76175 width=16) (actual time=13211.529..13254.693 rows=1038899 loops=3)"
"              Output: id_observation, (PARTIAL count(*))"
"              Sort Key: om.id_observation"
"              Sort Method: quicksort  Memory: 103129kB"
"              Worker 0:  Sort Method: quicksort  Memory: 70936kB"
"              Worker 1:  Sort Method: quicksort  Memory: 70336kB"
"              Buffers: shared hit=31 read=1651415"
"              Worker 0: actual time=13155.030..13195.864 rows=989009 loops=1"
"                Buffers: shared hit=7 read=505279"
"              Worker 1: actual time=13154.560..13198.533 rows=976200 loops=1"
"                Buffers: shared hit=7 read=498612"
"              ->  Partial HashAggregate  (cost=2309650.65..2310412.40 rows=76175 width=16) (actual time=12722.283..12912.608 rows=1038899 loops=3)"
"                    Output: id_observation, PARTIAL count(*)"
"                    Group Key: om.id_observation"
"                    Buffers: shared hit=17 read=1651415"
"                    Worker 0: actual time=12684.392..12869.667 rows=989009 loops=1"
"                      Buffers: shared read=505279"
"                    Worker 1: actual time=12695.744..12875.018 rows=976200 loops=1"
"                      Buffers: shared read=498612"
"                    ->  Parallel Bitmap Heap Scan on public.observation_modalite om  (cost=387665.68..2265663.42 rows=8797447 width=8) (actual time=2778.934..11443.670 rows=7169273 loops=3)"
"                          Output: id_observation, id_modalite"
"                          Recheck Cond: (om.id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"
"                          Heap Blocks: exact=588740"
"                          Buffers: shared hit=17 read=1651415"
"                          Worker 0: actual time=2741.881..11465.241 rows=6822799 loops=1"
"                            Buffers: shared read=505279"
"                          Worker 1: actual time=2753.232..11473.150 rows=6739039 loops=1"
"                            Buffers: shared read=498612"
"                          ->  Bitmap Index Scan on observation_modalite_id_modalite_idx  (cost=0.00..382387.21 rows=21113872 width=0) (actual time=2400.998..2400.998 rows=21507819 loops=1)"
"                                Index Cond: (om.id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"
"                                Buffers: shared hit=17 read=58784"
"Planning Time: 0.241 ms"
"Execution Time: 14997.862 ms"

gleu

Administrateur

Re : Optimisation filtre entre 2 tables de relation */*

Augmenter le effective_cache_size favorise l'utilisation des index. Comme il utilisait déjà un index, ça ne pouvait pas avoir un grand impact.

---

Guillaume.

landalvic

Membre

Re : Optimisation filtre entre 2 tables de relation */*

Re-bonjour tout le monde !

Je viens de discuter avec quelqu'un qui vient de résoudre tous mes problèmes, donc je partage ma solution avec vous pour ceux qui seraient intéressés par ce problème.

L'idée, c'est d'enregistrer la colonne id_B sous la forme d'un tableau d'int :

create unlogged table in_array
as select id_A, array_agg(id_B) as id_B
from table_AB group by id_B;

on crée un index sur la colonne:
create index idx_in_array on in_array using gin(id_B array_ops);

et la requête :

select id_A
from in_array
where array[1,2] <@ id_B

renvoie la réponse en 60 ms !! On peut donc mettre cette colonne dans table_A directement. ça dénormalise la base, mais en temps de requête c'est juste wouahou !!

gleu

Administrateur

Re : Optimisation filtre entre 2 tables de relation */*

En effet, l'optimisation est intéressante. Ceci étant dit, cela sous-entend qu'il faut créer cette table UNLOGGED à chaque fois qu'il fait exécuter la requête initiale. Donc même si la requête initiale est devenue très rapide, il faut aussi prendre en compte le temps de création et de peuplement de la table UNLOGGED et de son index

---

Guillaume.

landalvic

Membre

Re : Optimisation filtre entre 2 tables de relation */*

En fait non, car là c'est juste pour l'exemple. En vrai, la nouvelle colonne integer[] sera mise directement dans ma table_A, et sera mise à jour lors de l'insertion d'une ligne.
Du coup, je n'ai plus qu'à requêter ma table_A et c'est bon !

philippesb

Membre

Re : Optimisation filtre entre 2 tables de relation */*

C'est vrai que le principe est séduisant. A voir côté applicatif les problèmes ou développement d'adaptation que cela peut engendrer.

rjuju

Administrateur

Re : Optimisation filtre entre 2 tables de relation */*

Le plus gros problème est que cela veut à priori dire supprimer une partie des contraintes d'intégrité.  Le résultat est plus rapide, mais les données peuvent être fausses.

---

Julien.
https://rjuju.github.io/