Vous n'êtes pas identifié(e).
Pages : 1
C'est plutôt une logique d'autocomplétion. La personne recherche par exemple Paris, elle va taper "Pa" et voir toutes les communes qui correspondent à Pa (selon le libellé, le code...), puis cliquer sur Paris et c'est fini.
Du coup, il me semble que récupérer 5 à la fois me semble mieux, car dans l'exemple au dessus, la personne trouve son choix juste en récupérant 5 éléments (10 si on compte l'écriture de P).
Mais quand on a une liste de code petite, tels que les âges avec genre 30 modalités, la personne va taper par exemple "14" et en filtre il y aura "14/18 ans", on va pas balancer une requête à chaque caractère, on récupère toute la liste d'un coup et puis c'est bon.
Sauf que des listes, j'en ai des tas de taille différente ! Allant du sexe avec deux modalités (homme, femme) jusqu'aux nomenclatures où il peut y en avoir 10000 (et la géo 40000)
Du coup, à partir de combien il vaut mieux faire une requête à chaque caractère VS le fait de tout récupérer d'un coup
Admettons que l'interface soit capable de gérer 1000000 de lignes (J'ai pas l'impression que ce soit l'interface le facteur limitant ? Après tout, avec de la virtualisation d'affichage, rien ne sera lent). Personnellement, je suis en React, et j'ai un composant qui fait liste déroulante en affichant max 100 éléments, avec un champ de recherche qui permet de filtrer ces éléments.
Le plus lent dans l'histoire me parait être de sortir 40000 lignes de la bdd à convertir en objet, puis en terme de réseau à convertir en json via l'api pour l'interface. Non ?
Bonjour !!
Petite question toute bête : A partir de combien de lignes dans une table il vaut mieux faire une recherche en sql plutôt que de tout récupérer ?
Par exemple, si je dois afficher les modalités pour la dimension âge, je vais avoir 0-14, 14-18, 18-25… Je peux facilement tout récupérer d'un coup et faire une liste déroulante dans l'interface.
Par contre, pour la géographie et ses 40000 modalités, je me dis qu'il vaut mieux faire une recherche spécifique qui renvoie max 5 résultats plutôt que de tout récupérer, non ?
Du coup, je me demandais, c'est quoi à peu près le nombre limite qui fait qu'on fait plutôt l'un ou plutôt l'autre ? (ou autre chose ?)
Julien
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 !
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 !!
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"
ç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 ?
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"
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"
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 ?
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é.
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.
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 !
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 ?
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"
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.
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 ^^
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"
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 ?
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 ?
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
Pages : 1
