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 11/11/2019 17:58:58

landalvic
Membre

Optimisation filtre entre 2 tables de relation */*

Bonjour à tous !

Voilà ma situation qui me semble assez classique :

J'ai 2 tables qu'on appellera table A et table B pour simplifier, qui sont en relation */*. Il y a donc une table de passage entre les 2, et c'est sur cette table là que je souhaite me concentrer :

tableAB :

| id_A  | id_B |
|    1   |    1   |
|    1   |    2   |
|    1   |    3   |
|    2   |    1   |
|    2   |    2   |
|    2   |    4   |
|    3   |    1   |
|    3   |    3   |
|    3   |    4   |

Je cherche à faire une requête sql sur cette table qui récupère les id_A pour lesquels il y a au moins id_B qui vaut 1 et 2, et ce de la façon la plus optimisée possible ! (car dans la réalité, cette table à des centaines de millions de lignes)

Personnellement j'ai 2 solutions, qui deviennent très lentes (quelques minutes) avec un grand nombre de lignes :

- 1 : Avec un group by :

select id_A
from tableAB
where id_B in (1, 2)
group by id_A
having count(*) = 2
;

- 2 : Avec autant de jointure sur la table tableAB qu'il y a de filtre :

select id_A
from tableAB t1
inner join tableAB t2 using (id_A)
where t1.idB = 1 and t2.idB = 2
;

Voilà ! J'espère que mon problème est assez clair (n'hésitez pas à me poser des questions pour plus d'info). Je n'ai jamais réussi à être suffisamment clair sur Google pour tomber sur des gens qui se posent la même question que moi :'(
Vous pouvez utiliser tout ce que vous voulez, changer ce que vous voulez, je n'ai pas de contrainte, mon projet n'est pas encore commencé. Donc vous pouvez même me conseiller une autre solution que Postgres si jamais ce n'est vraiment pas la bonne solution de stockage ^^.

Merci d'avance à tous !! J'espère qu'il existe une belle solution à mon problème, car sinon je suis dans la merde ! ^^

Julien

Hors ligne

#2 11/11/2019 19:23:05

gleu
Administrateur

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

La solution 1 me semble la plus compréhensible et certainement la plus optimisable. Pour aller plus loin, il serait bon d'avoir le plan d'exécution choisi par PostgreSQL.


Guillaume.

Hors ligne

#3 12/11/2019 11:14:48

landalvic
Membre

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

Merci pour ta réponse !

du coup, voici le plan d'exécution :

"Finalize GroupAggregate  (cost=5717224.49..5803265.15 rows=381 width=8)"
"  Group Key: id_observation"
"  Filter: (count(*) = 9)"
"  ->  Gather Merge  (cost=5717224.49..5801551.21 rows=152350 width=16)"
"        Workers Planned: 2"
"        ->  Partial GroupAggregate  (cost=5716224.46..5782966.22 rows=76175 width=16)"
"              Group Key: id_observation"
"              ->  Sort  (cost=5716224.46..5738217.80 rows=8797334 width=8)"
"                    Sort Key: id_observation"
"                    ->  Parallel Seq Scan on observation_modalite om  (cost=0.00..4460957.03 rows=8797334 width=8)"
"                          Filter: (id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"


et voici ma vrai requête :

SELECT om.id_observation
FROM observation_modalite om
WHERE om.id_modalite in (1268, 2846, 1989, 2002, 2015, 2064, 2072, 2085, 2134)
GROUP BY id_observation
HAVING COUNT(*) = 9
;

sachant que dans ma table, il n'y a que id_observation et id_modalite en clé primaire les deux.
Dans ma table, il y a 312 millions de lignes et ma requête prend 3 minutes pour 50 000 résultats

Qu'est-ce que vous en pensez ?

Hors ligne

#4 12/11/2019 11:51:52

landalvic
Membre

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

C'est passé à 20 secondes en modifiant le shared_buffer et le work_mem car effectivement, je n'avais pas pensé à les modifier à la base :'(.
C'est vraiment cool, mais ça ne change pas ma question qui est : comment optimiser le plus possible ? 20 secondes, est-ce le mieux que l'on puisse faire ?

Hors ligne

#5 12/11/2019 12:29:40

gleu
Administrateur

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

Un EXPLAIN simple ne donne pas suffisamment d'informations. Le mieux est de configurer localement track_io_timing à on et de faire un "EXPLAIN (ANALYZE,BUFFERS)". Et comme il parallélise, l'option VERBOSE en plus.


Guillaume.

Hors ligne

#6 12/11/2019 12:37:58

landalvic
Membre

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

Ok ! C'est la première fois que j'entends ces termes, mais du coup j'ai lancé :


EXPLAIN  (ANALYZE,BUFFERS,VERBOSE)
select om.id_observation
from observation_modalite om
where om.id_modalite in (1268, 2846, 1989, 2002, 2015, 2064, 2072, 2085, 2134)
group by id_observation
HAVING COUNT(*) = 9
;

après avoir mis track_io_timing à on et j'ai obtenu :


"Finalize GroupAggregate  (cost=4512882.14..4532371.48 rows=381 width=8) (actual time=20616.984..21189.158 rows=47366 loops=1)"
"  Output: id_observation"
"  Group Key: om.id_observation"
"  Filter: (count(*) = 9)"
"  Rows Removed by Filter: 2970274"
"  Buffers: shared hit=12811 read=554424"
"  I/O Timings: read=6109.857"
"  ->  Gather Merge  (cost=4512882.14..4530657.54 rows=152350 width=16) (actual time=20092.770..20731.064 rows=3122424 loops=1)"
"        Output: id_observation, (PARTIAL count(*))"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=35853 read=1655213"
"        I/O Timings: read=18327.956"
"        ->  Sort  (cost=4511882.11..4512072.55 rows=76175 width=16) (actual time=20021.579..20061.547 rows=1040808 loops=3)"
"              Output: id_observation, (PARTIAL count(*))"
"              Sort Key: om.id_observation"
"              Sort Method: quicksort  Memory: 73498kB"
"              Worker 0:  Sort Method: quicksort  Memory: 73450kB"
"              Worker 1:  Sort Method: quicksort  Memory: 73145kB"
"              Buffers: shared hit=35853 read=1655213"
"              I/O Timings: read=18327.956"
"              Worker 0: actual time=19988.610..20029.165 rows=1042636 loops=1"
"                Buffers: shared hit=11656 read=552133"
"                I/O Timings: read=6133.245"
"              Worker 1: actual time=19993.806..20034.291 rows=1036128 loops=1"
"                Buffers: shared hit=11386 read=548656"
"                I/O Timings: read=6084.854"
"              ->  Partial HashAggregate  (cost=4504943.70..4505705.45 rows=76175 width=16) (actual time=19544.592..19729.494 rows=1040808 loops=3)"
"                    Output: id_observation, PARTIAL count(*)"
"                    Group Key: om.id_observation"
"                    Buffers: shared hit=35839 read=1655213"
"                    I/O Timings: read=18327.956"
"                    Worker 0: actual time=19506.391..19694.452 rows=1042636 loops=1"
"                      Buffers: shared hit=11649 read=552133"
"                      I/O Timings: read=6133.245"
"                    Worker 1: actual time=19518.568..19704.769 rows=1036128 loops=1"
"                      Buffers: shared hit=11379 read=548656"
"                      I/O Timings: read=6084.854"
"                    ->  Parallel Seq Scan on public.observation_modalite om  (cost=0.00..4460957.03 rows=8797334 width=8) (actual time=742.694..18360.253 rows=7169273 loops=3)"
"                          Output: id_observation, id_modalite"
"                          Filter: (om.id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"
"                          Rows Removed by Filter: 97110849"
"                          Buffers: shared hit=35839 read=1655213"
"                          I/O Timings: read=18327.956"
"                          Worker 0: actual time=704.495..18323.326 rows=7185395 loops=1"
"                            Buffers: shared hit=11649 read=552133"
"                            I/O Timings: read=6133.245"
"                          Worker 1: actual time=716.665..18329.640 rows=7134518 loops=1"
"                            Buffers: shared hit=11379 read=548656"
"                            I/O Timings: read=6084.854"
"Planning Time: 0.092 ms"
"Execution Time: 21254.141 ms"

Hors ligne

#7 12/11/2019 14:29:08

gleu
Administrateur

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

Merci. Du coup, voici une représentation du plan : https://explain.dalibo.com/plan/GN. Ce qui pose problème sur ce plan, c'est le parcours séquentiel sur la table public.observation_modalite. Il serait bon de vérifier s'il existe un index sur la colonne id_modalite. Si oui, combien de lignes contient la table ? et combien satisfont le prédicat de cette réquête ? (très difficile à interpréter sur un plan parallélisé, donc je préfère que vous vérifiez cette information directement).


Guillaume.

Hors ligne

#8 12/11/2019 15:54:06

landalvic
Membre

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

Merci pour ta réponse !
Je n'ai pas mis d'index sur la colonne id_modalite, car elle est clé primaire avec id_observation. Et je ne sais pas du tout comment Postgres indexe les clés primaires multiples.
Je vais donc indexer id_modalite pour voir si ça change le plan d'exécution.

Je fais un index classique ? genre :

CREATE INDEX observation_modalite_id_modalite_idx ON observation_modalite (id_modalite);

J'y connais pas grand chose dans les différents types d'index ^^

Hors ligne

#9 12/11/2019 16:12:19

gleu
Administrateur

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

Avant d'indexer quoi que ce soit, il serait intéressant de répondre à mes autres questions smile à savoir, combien de lignes dans la table et combien de lignes pour le filtre WHERE ?


Guillaume.

Hors ligne

#10 12/11/2019 16:24:26

landalvic
Membre

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

Dans la table, il y a 312 millions de lignes, et environ 48000 id_observation qui satisfont le where.
Il y a également 5,5 millions d'id_observation différents au total, et 3700 id_modalite différents au total.

Hors ligne

#11 12/11/2019 17:02:24

gleu
Administrateur

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

Dans cas, un index sera intéressant. c'est étonnant qu'il n'utilise pas celui de la clé primaire. C'est peut être dû au fait que la colonne id_modalite est déclarée en deuxième.


Guillaume.

Hors ligne

#12 12/11/2019 18:11:26

landalvic
Membre

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

Avec l'index, ça donne ça :

"Finalize GroupAggregate  (cost=2317589.09..2337078.43 rows=381 width=8) (actual time=14272.021..14886.363 rows=47366 loops=1)"
"  Output: id_observation"
"  Group Key: om.id_observation"
"  Filter: (count(*) = 9)"
"  Rows Removed by Filter: 2970274"
"  Buffers: shared hit=14 read=616505"
"  I/O Timings: read=7509.667"
"  ->  Gather Merge  (cost=2317589.09..2335364.49 rows=152350 width=16) (actual time=13748.892..14677.881 rows=3117443 loops=1)"
"        Output: id_observation, (PARTIAL count(*))"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=28 read=1651418"
"        I/O Timings: read=22002.470"
"        ->  Sort  (cost=2316589.06..2316779.50 rows=76175 width=16) (actual time=13630.579..13673.690 rows=1039148 loops=3)"
"              Output: id_observation, (PARTIAL count(*))"
"              Sort Key: om.id_observation"
"              Sort Method: quicksort  Memory: 100306kB"
"              Worker 0:  Sort Method: quicksort  Memory: 72319kB"
"              Worker 1:  Sort Method: quicksort  Memory: 71810kB"
"              Buffers: shared hit=28 read=1651418"
"              I/O Timings: read=22002.470"
"              Worker 0: actual time=13584.340..13626.985 rows=1018511 loops=1"
"                Buffers: shared hit=7 read=520246"
"                I/O Timings: read=7248.187"
"              Worker 1: actual time=13560.178..13602.503 rows=1007654 loops=1"
"                Buffers: shared hit=7 read=514667"
"                I/O Timings: read=7244.616"
"              ->  Partial HashAggregate  (cost=2309650.65..2310412.40 rows=76175 width=16) (actual time=13123.054..13321.630 rows=1039148 loops=3)"
"                    Output: id_observation, PARTIAL count(*)"
"                    Group Key: om.id_observation"
"                    Buffers: shared hit=14 read=1651418"
"                    I/O Timings: read=22002.470"
"                    Worker 0: actual time=13087.067..13283.415 rows=1018511 loops=1"
"                      Buffers: shared read=520246"
"                      I/O Timings: read=7248.187"
"                    Worker 1: actual time=13087.082..13269.680 rows=1007654 loops=1"
"                      Buffers: shared read=514667"
"                      I/O Timings: read=7244.616"
"                    ->  Parallel Bitmap Heap Scan on public.observation_modalite om  (cost=387665.68..2265663.42 rows=8797447 width=8) (actual time=2826.175..11814.164 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=557718"
"                          Buffers: shared hit=14 read=1651418"
"                          I/O Timings: read=22002.470"
"                          Worker 0: actual time=2791.450..11820.733 rows=7028096 loops=1"
"                            Buffers: shared read=520246"
"                            I/O Timings: read=7248.187"
"                          Worker 1: actual time=2790.551..11808.679 rows=6951334 loops=1"
"                            Buffers: shared read=514667"
"                            I/O Timings: read=7244.616"
"                          ->  Bitmap Index Scan on observation_modalite_id_modalite_idx  (cost=0.00..382387.21 rows=21113872 width=0) (actual time=2464.200..2464.200 rows=21507819 loops=1)"
"                                Index Cond: (om.id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"
"                                Buffers: shared hit=14 read=58787"
"                                I/O Timings: read=462.320"
"Planning Time: 0.156 ms"
"Execution Time: 15234.919 ms"

Hors ligne

#13 12/11/2019 23:46:12

gleu
Administrateur

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

Donc légèrement mieux (on passe de 21 secondes à 15 secondes). La majorité des données sont lues en dehors du cache. Quel est la taille du cache de PostgreSQL ? (paramètre shared_buffers) et quel est la taille de la mémoire sur ce serveur ?


Guillaume.

Hors ligne

#14 13/11/2019 14:36:36

landalvic
Membre

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

Justement, j'ai changé le shared_buffers hier, je l'ai passé à 4GB, c'est ce qui a permis de passer la requête de 2/3 minutes à 20s.

Je suis juste en local pour l'instant, j'ai 32Go de Ram sur mon pc. Tu penses il faut que je le monte plus le shared_buffers ?

Dernière modification par landalvic (13/11/2019 15:17:01)

Hors ligne

#15 13/11/2019 22:44:35

gleu
Administrateur

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

Normalement, le shared_buffers se configure à un quart de la RAM pour un serveur dédié. Donc avec 32 Go, cela donne plutôt 8 Go. Si l'EXPLAIN montré ci-dessus est avec un shared_buffers à 4 Go, il est clair que ce n'est pas suffisant vu le nombre de shared read (lecture hors du cache). Donc oui, à augmenter.


Guillaume.

Hors ligne

#16 14/11/2019 11:55:05

landalvic
Membre

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

J'ai augmenté le shared_buffers à 8, et j'obtiens :

"Finalize GroupAggregate  (cost=2317589.09..2337078.43 rows=381 width=8) (actual time=14518.673..15087.257 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=639017"
"  I/O Timings: read=7567.982"
"  ->  Gather Merge  (cost=2317589.09..2335364.49 rows=152350 width=16) (actual time=13993.616..15019.597 rows=3117602 loops=1)"
"        Output: id_observation, (PARTIAL count(*))"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=31 read=1651415"
"        I/O Timings: read=22473.124"
"        ->  Sort  (cost=2316589.06..2316779.50 rows=76175 width=16) (actual time=13875.676..13916.966 rows=1039201 loops=3)"
"              Output: id_observation, (PARTIAL count(*))"
"              Sort Key: om.id_observation"
"              Sort Method: quicksort  Memory: 102365kB"
"              Worker 0:  Sort Method: quicksort  Memory: 71135kB"
"              Worker 1:  Sort Method: quicksort  Memory: 70944kB"
"              Buffers: shared hit=31 read=1651415"
"              I/O Timings: read=22473.124"
"              Worker 0: actual time=13816.285..13857.613 rows=993242 loops=1"
"                Buffers: shared hit=7 read=507219"
"                I/O Timings: read=7458.554"
"              Worker 1: actual time=13818.836..13858.960 rows=989164 loops=1"
"                Buffers: shared hit=7 read=505179"
"                I/O Timings: read=7446.587"
"              ->  Partial HashAggregate  (cost=2309650.65..2310412.40 rows=76175 width=16) (actual time=13383.926..13578.904 rows=1039201 loops=3)"
"                    Output: id_observation, PARTIAL count(*)"
"                    Group Key: om.id_observation"
"                    Buffers: shared hit=17 read=1651415"
"                    I/O Timings: read=22473.124"
"                    Worker 0: actual time=13345.817..13535.945 rows=993242 loops=1"
"                      Buffers: shared read=507219"
"                      I/O Timings: read=7458.554"
"                    Worker 1: actual time=13354.979..13540.496 rows=989164 loops=1"
"                      Buffers: shared read=505179"
"                      I/O Timings: read=7446.587"
"                    ->  Parallel Bitmap Heap Scan on public.observation_modalite om  (cost=387665.68..2265663.42 rows=8797447 width=8) (actual time=2849.913..12049.282 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=580233"
"                          Buffers: shared hit=17 read=1651415"
"                          I/O Timings: read=22473.124"
"                          Worker 0: actual time=2812.704..12074.545 rows=6850408 loops=1"
"                            Buffers: shared read=507219"
"                            I/O Timings: read=7458.554"
"                          Worker 1: actual time=2821.821..12072.373 rows=6824623 loops=1"
"                            Buffers: shared read=505179"
"                            I/O Timings: read=7446.587"
"                          ->  Bitmap Index Scan on observation_modalite_id_modalite_idx  (cost=0.00..382387.21 rows=21113872 width=0) (actual time=2481.332..2481.332 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"
"                                I/O Timings: read=442.210"
"Planning Time: 0.103 ms"
"Execution Time: 15546.507 ms"

ça me surprend vachement qu'en doublant la mémoire, on ait 0 impact sur la requête !

Dernière modification par landalvic (14/11/2019 11:57:15)

Hors ligne

#17 14/11/2019 14:55:56

gleu
Administrateur

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

Avez-vous redémarré PostgreSQL pour prendre en compte cette modification ?

Si vous l'avez redémarré, exécuter la requête une seule fois ne suffira pas. On est là sur la configuration d'un cache disque. Si la table n'est pas en mémoire, forcément, la requête ne sera pas plus rapide. On voit "Buffers: shared hit=17 read=639017", autrement dit 99,997339 % des lectures ne se font pas dans le cache de PostgreSQL. Bref, même si la modification du paramètre a été prise en compte, il n'est pas étonnant que la requête ne soit pas plus rapide dans ce cas précis.


Guillaume.

Hors ligne

#18 14/11/2019 15:19:13

landalvic
Membre

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

J'ai carrément redémarré mon pc pour prendre en compte la modification. J'avais également redémarré mon pc la fois où j'étais passé à 4GB, et ça avait fonctionné tout de suite, donc je pense que de ce côté là c'est bon.
Et j'ai exécuté plusieurs fois la requête, car la première fois ça dure beaucoup plus longtemps.

Hors ligne

#19 14/11/2019 15:26:21

gleu
Administrateur

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

Il est difficile de croire que vous ayez ce plan après plusieurs exécutions de la même requête. Même si vous pouvez toujours avoir du shared read, il devrait y avoir beaucoup plus de shared hit.


Guillaume.

Hors ligne

#20 14/11/2019 15:30:38

landalvic
Membre

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

Pour avoir bonne conscience, je viens de redémarrer mon pc encore une fois, vérifié que le shared_buffers est bien à 8GB, exécuté la requête 5 fois d'affilé :
La première requête est à 3 minutes, toutes les autres sont entre 15 et 16s, rien n'a changé.

Hors ligne

#21 14/11/2019 15:36:35

landalvic
Membre

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

Je ne m'y connais pas trop dans ce domaine, mais il y a peut-être d'autres properties que le shared_buffers que je n'aurais pas pensé à modifier ?

Hors ligne

#22 14/11/2019 18:13:52

gleu
Administrateur

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

Et quel est le plan d'exécution à 15 seconde, après une Xè exécution (mais pas la première) ?


Guillaume.

Hors ligne

#23 14/11/2019 18:29:21

landalvic
Membre

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

Voilà le plan d'exécution à la 15e requête :

"Finalize GroupAggregate  (cost=2317589.09..2337078.43 rows=381 width=8) (actual time=14969.106..15541.747 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=639136"
"  I/O Timings: read=7851.726"
"  ->  Gather Merge  (cost=2317589.09..2335364.49 rows=152350 width=16) (actual time=14437.261..15555.058 rows=3116744 loops=1)"
"        Output: id_observation, (PARTIAL count(*))"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        Buffers: shared hit=31 read=1651415"
"        I/O Timings: read=23337.757"
"        ->  Sort  (cost=2316589.06..2316779.50 rows=76175 width=16) (actual time=14303.667..14344.054 rows=1038915 loops=3)"
"              Output: id_observation, (PARTIAL count(*))"
"              Sort Key: om.id_observation"
"              Sort Method: quicksort  Memory: 102376kB"
"              Worker 0:  Sort Method: quicksort  Memory: 70912kB"
"              Worker 1:  Sort Method: quicksort  Memory: 71115kB"
"              Buffers: shared hit=31 read=1651415"
"              I/O Timings: read=23337.757"
"              Worker 0: actual time=14214.966..14253.523 rows=988493 loops=1"
"                Buffers: shared hit=7 read=505006"
"                I/O Timings: read=7753.136"
"              Worker 1: actual time=14260.127..14299.176 rows=992819 loops=1"
"                Buffers: shared hit=7 read=507273"
"                I/O Timings: read=7732.895"
"              ->  Partial HashAggregate  (cost=2309650.65..2310412.40 rows=76175 width=16) (actual time=13778.963..13982.802 rows=1038915 loops=3)"
"                    Output: id_observation, PARTIAL count(*)"
"                    Group Key: om.id_observation"
"                    Buffers: shared hit=17 read=1651415"
"                    I/O Timings: read=23337.757"
"                    Worker 0: actual time=13737.212..13921.995 rows=988493 loops=1"
"                      Buffers: shared read=505006"
"                      I/O Timings: read=7753.136"
"                    Worker 1: actual time=13751.130..13954.467 rows=992819 loops=1"
"                      Buffers: shared read=507273"
"                      I/O Timings: read=7732.895"
"                    ->  Parallel Bitmap Heap Scan on public.observation_modalite om  (cost=387665.68..2265663.42 rows=8797447 width=8) (actual time=2830.351..12413.656 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=580352"
"                          Buffers: shared hit=17 read=1651415"
"                          I/O Timings: read=23337.757"
"                          Worker 0: actual time=2789.453..12442.657 rows=6823441 loops=1"
"                            Buffers: shared read=505006"
"                            I/O Timings: read=7753.136"
"                          Worker 1: actual time=2803.421..12439.173 rows=6851337 loops=1"
"                            Buffers: shared read=507273"
"                            I/O Timings: read=7732.895"
"                          ->  Bitmap Index Scan on observation_modalite_id_modalite_idx  (cost=0.00..382387.21 rows=21113872 width=0) (actual time=2475.568..2475.568 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"
"                                I/O Timings: read=451.659"
"Planning Time: 0.108 ms"
"Execution Time: 16087.849 ms"

Hors ligne

#24 14/11/2019 18:48:35

gleu
Administrateur

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

Vous êtes le seul à bosser sur ce serveur ou d'autres utilisateurs exécutent des requêtes en même temps que vous ? parce que, si vous êtes seul, on ne devrait pas avoir ces chiffres là.

Pouvez-vous essayer sans parallélisation ? ("set max_parallel_workers_per_gather to 0") Ça devrait être plus long mais le plan sera plus simple à lire.


Guillaume.

Hors ligne

#25 15/11/2019 10:45:29

landalvic
Membre

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

Ben c'est sur mon PC, celui qui est à mes pieds. Donc c'est sûr que je suis tout seul, et l'appli que je fais tourner en local sur ces données, je ne l'ai pas lancée depuis un moment, je fais toutes les requêtes depuis pgadmin dernièrement.

Donc la seule chose que je vois, c'est genre windows qui limiterait la RAM que peut utiliser Postgres, mais je n'ai jamais entendu parler d'une telle interaction.

Voilà sans les parallel_workers :

"HashAggregate  (cost=2632956.82..2633909.00 rows=381 width=8) (actual time=21105.852..21581.969 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=1651415"
"  I/O Timings: read=10858.797"
"  ->  Bitmap Heap Scan on public.observation_modalite om  (cost=387665.68..2527387.46 rows=21113872 width=8) (actual time=2861.750..17574.039 rows=21507819 loops=1)"
"        Output: id_observation, id_modalite"
"        Recheck Cond: (om.id_modalite = ANY ('{1268,2846,1989,2002,2015,2064,2072,2085,2134}'::bigint[]))"
"        Heap Blocks: exact=1592631"
"        Buffers: shared hit=17 read=1651415"
"        I/O Timings: read=10858.797"
"        ->  Bitmap Index Scan on observation_modalite_id_modalite_idx  (cost=0.00..382387.21 rows=21113872 width=0) (actual time=2470.048..2470.048 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"
"              I/O Timings: read=439.315"
"Planning Time: 0.092 ms"
"Execution Time: 21633.487 ms"

Hors ligne

Pied de page des forums