Vous n'êtes pas identifié(e).
Pages : 1
J'ai pu redémarrer le serveur plus tôt que prévu.
J'ai passé shared_buffers à 4096 Mo.
J'ai passé work_memory à 1024 Mo.
"Sort (cost=129764478.36..129765829.05 rows=540276 width=20) (actual time=133169.574..133448.809 rows=4980744 loops=1)"
" Sort Key: pi.pk_putative_inparalog"
" Sort Method: quicksort Memory: 585729kB"
" -> Nested Loop (cost=71949.14..129713035.07 rows=540276 width=20) (actual time=917.099..131957.156 rows=4980744 loops=1)"
" -> Bitmap Heap Scan on putative_inparalog pi (cost=71949.14..113545.26 rows=11266 width=16) (actual time=866.098..1194.070 rows=10839 loops=1)"
" Recheck Cond: ((pk_organism_rapporteur = 118) AND (pk_organism = 24))"
" -> BitmapAnd (cost=71949.14..71949.14 rows=11266 width=0) (actual time=850.094..850.094 rows=0 loops=1)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism_rapporteur (cost=0.00..10781.48 rows=582726 width=0) (actual time=359.111..359.111 rows=772378 loops=1)"
" Index Cond: (pk_organism_rapporteur = 118)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism (cost=0.00..61161.78 rows=3310766 width=0) (actual time=368.301..368.301 rows=3112295 loops=1)"
" Index Cond: (pk_organism = 24)"
" -> Index Scan using ndex_putative_inparalog_sequence_pk_putative_inparalog on ln_putative_inparalog_sequence ln_pi (cost=0.00..9183.24 rows=185628 width=8) (actual time=9.050..11.986 rows=460 loops=10839)"
" Index Cond: (ln_pi.pk_putative_inparalog = pi.pk_putative_inparalog)"
"Total runtime: 133710.546 ms"
Je passe donc à 2 minutes pour le résultat. Soit une minute de moins par rapport au explain analyse précédant.
La nested loop reste sembl-t-il l'étape la plus coûteuse. Mais 30% de gain, c'est déjà un bon progrès !! 
-MOD-
En fait, je viens de refaire la requete (le explain analyse suivant a été fait après redémarrage de postgres).
Et on tombe à .... 3 secondes !!!!
"Sort (cost=129764478.36..129765829.05 rows=540276 width=20) (actual time=3181.201..3463.712 rows=4980744 loops=1)"
" Sort Key: pi.pk_putative_inparalog"
" Sort Method: quicksort Memory: 585729kB"
" -> Nested Loop (cost=71949.14..129713035.07 rows=540276 width=20) (actual time=548.637..2184.248 rows=4980744 loops=1)"
" -> Bitmap Heap Scan on putative_inparalog pi (cost=71949.14..113545.26 rows=11266 width=16) (actual time=548.611..561.114 rows=10839 loops=1)"
" Recheck Cond: ((pk_organism_rapporteur = 118) AND (pk_organism = 24))"
" -> BitmapAnd (cost=71949.14..71949.14 rows=11266 width=0) (actual time=544.536..544.536 rows=0 loops=1)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism_rapporteur (cost=0.00..10781.48 rows=582726 width=0) (actual time=227.712..227.712 rows=772378 loops=1)"
" Index Cond: (pk_organism_rapporteur = 118)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism (cost=0.00..61161.78 rows=3310766 width=0) (actual time=192.021..192.021 rows=3112295 loops=1)"
" Index Cond: (pk_organism = 24)"
" -> Index Scan using ndex_putative_inparalog_sequence_pk_putative_inparalog on ln_putative_inparalog_sequence ln_pi (cost=0.00..9183.24 rows=185628 width=8) (actual time=0.006..0.080 rows=460 loops=10839)"
" Index Cond: (ln_pi.pk_putative_inparalog = pi.pk_putative_inparalog)"
"Total runtime: 3697.362 ms"
J'ai fait de nombreux tests en changeant pk_organism=XX et pk_organism_rapporteur=XXX à différente valeurs.
Le premier select de chaque combinaison est long, le second quasi instantané. (une histoire de cache ou un truc du genre ?)
Je suppose que c'est normal, le problème de mon programme, c'est qu'il va à chaque fois appellé des combinaisons possibles pour ces critères de séléction.
Par exemple:
pk_organism=1 AND pk_organism_rapporteur=2
pk_organism=1 AND pk_organism_rapporteur=3
pk_organism=1 AND pk_organism_rapporteur=4
...
pk_organism=2 AND pk_organism_rapporteur=1
pk_organism=2 AND pk_organism_rapporteur=3
pk_organism=2 AND pk_organism_rapporteur=4
...
Donc à chaque fois il semble que j'appel la version longue du select.
Merci pour vos conseils, je redémarerai le serveur la semaine prochaine pour augmenter les shared_buffers (d'autres personnes travaillents dessus en ce moment).
Je vous tiendrai au courant.
Encore merci!
@flo
On peut oublier la colonne 'pk_version'.
Elle est vide pour l'instant et n'est pas utilisée dans la base de données (faut que je la supprime d'ailleurs).
Effectivement, 'ln_putative_inparalog_sequence' est une table de lien entre sequence et putative_inparalog.
Plus précisement entre la clé primaire de 'putative-inparalog' et la clé primaire de 'sequences'.
La colonne 'pk_sequence' de 'sequences' est effectivement référencée dans 'putative_inparalog', mais pas pour décrire la composition en sequence d'un 'putative_inparalog' (c'est le rôle de la table de lien 'ln_putative_inparalog_sequence' ). Son role est autre et n'est pas forcement de la même valeur que les sequences composant un 'putative_inparalog' (c'est juste une référence à la sequence qui a permet de déduire la relation décrite par un putative_inparalog).
Je vais essayer de résumer le type de données qui peut être contenu dans ces tables.
Soit trois organismes, HOMME, SINGE et SOURIS.
L'homme possède trois séquences: H1, H2 et H3
L'homme possède trois séquences: S1 et S2
L'homme possède 2 autres séquences: M1 et M2
Les tables pourraient contenir les lignes suivantes:
Table putative_inparalog
|pk_putative_inparalog|pk_sequence|pk_organism|pk_organism_rapporteur|
| 1 | H1| HOMME| SINGE |
| 2 | H1| HOMME| SOURIS |
| 3 | S1| SOURIS| HOMME |
| 4 | S1| SOURIS| SINGE |
Table ln_putative_inparalog_sequence
|pk_putative_inparalog|pk_sequence|
| 1 | H1|
| 1 | H2|
| 1 | H3|
| 2 | H1|
| 2 | H2|
| 3 | S1|
| 4 | S1|
| 4 | S2|
Par exemple, le premier inparalog (pk_putative_inparalog=1) est une relation qui a été déduite depuis la sequence protéique de l'homme H1 (pk_sequence=H1 dans putative_inparalog).
Cette relation est une comparaison HOMME / SINGE et elle relie les trois sequences H1, H2 et H3 (les 3 premieres lignes de ln_putative_inparalog_sequence)
Une seconde relation existe entre l'HOMME et la SOURIS (pk_putative_inparalog=2), déduite depuis la même sequence H1 (pk_sequence=H1 dans putative_inparalog).
Contrairement à la comparaison homme/singe, elle ne relie que 2 séquences humains H1 et H2 (les 4èmes et 5èmes lignes de ln_putative_inparalog_sequence)
...etc....
Toutes les colonnes sont nécessaires pour décrire ces relation biologique. Chaque comparaison par paire d'organisme peut conclure à un regroupement différent des séquences.
Ce regroupement est codé par la table de lien ln_putative_inparalog_sequence.
Si vous voyez une optimisation possible, je suis preneur. Mais j'y ai déjà beaucoup réfléchi. Normalement ces tables dérivent l'info minimum dont j'ai besoin pour mes calculs par la suite.
Héhé, ça vous rappelle vos cours de bio?
@rjuju
Merci pour les conseils, je vais essayer tout ça.
Vous voulez parler du fichier postgres.conf ?
Pour l'instant il est configuré comme suit:
shared_buffers = 16MB # min 128kB
temp_buffers = 32MB
#max_prepared_transactions = 0 # zero disables the feature
work_mem = 64MB # min 64kB
maintenance_work_mem = 256MB
max_stack_depth = 32MB
Il y a 32 Go de RAM sur le serveur, donc je dois pouvoir modifier tout ça sans soucis.
@flo
J'ai rajouté les tables organism, sequences et les déclarations des clés primaires.
Ces données représentent des gènes et les relations évolutionnaires qui les relient (je suis bioinformaticien).
Dans 'sequences' sont stockées les données de séquences protéiques.
Dans 'organism' sont stockées les descriptions des organismes.
'putative_inparalog' et 'ln_putative_inparalog' decrivent la relation qui existe entre les gènes de ces organisms.
Dans ces deux tables, 'pk_sequence' est donc une clé étrangère correspondant à la primaire de 'sequences'.
De même pour 'pk_organism' et 'pk_organism_rapporteur' clés étrangères correspondant à la primaire de 'organism'.
Un 'putative_inparalog' est un concept biologique reliant deux organismes ( codés par 'pk_organism' et 'pk_organism_rapporteur' dans la table 'putative_inparalog').
Cette entité peut contenir de 1 à n sequences d'où la table 'ln_putative_inparalog', reliant la clé primaire d'un 'putative_inparalog' avec 1 à n 'pk_sequence'
Cependant je n'ai pas besoin de faire de jointure avec 'organism' et 'sequences' dans mon problème, les données de ces tables sont pré-chargées en mémoire dans mon programme, et j'ai juste besoin d'un requête qui me renvoi les entiers la jointure de 'putative_inparalog' avec 'ln_sequence_putative_inparalog' sur la colonne 'pk_putative_inparalog', avec pour condition la paire d'organism correspondante (pk_organism' et 'pk_organism_rapporteur')
Voilà, j'espère que j'étais clair...
@gleu
Voici le résultat du explain analyse
"Sort (cost=129764478.36..129765829.05 rows=540276 width=20) (actual time=175049.496..175760.162 rows=4980744 loops=1)"
" Sort Key: pi.pk_putative_inparalog"
" Sort Method: external sort Disk: 146072kB"
" -> Nested Loop (cost=71949.14..129713035.07 rows=540276 width=20) (actual time=1408.355..168662.285 rows=4980744 loops=1)"
" -> Bitmap Heap Scan on putative_inparalog pi (cost=71949.14..113545.26 rows=11266 width=16) (actual time=1375.289..1841.050 rows=10839 loops=1)"
" Recheck Cond: ((pk_organism_rapporteur = 118) AND (pk_organism = 24))"
" -> BitmapAnd (cost=71949.14..71949.14 rows=11266 width=0) (actual time=1366.817..1366.817 rows=0 loops=1)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism_rapporteur (cost=0.00..10781.48 rows=582726 width=0) (actual time=400.910..400.910 rows=772378 loops=1)"
" Index Cond: (pk_organism_rapporteur = 118)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism (cost=0.00..61161.78 rows=3310766 width=0) (actual time=830.796..830.796 rows=3112295 loops=1)"
" Index Cond: (pk_organism = 24)"
" -> Index Scan using ndex_putative_inparalog_sequence_pk_putative_inparalog on ln_putative_inparalog_sequence ln_pi (cost=0.00..9183.24 rows=185628 width=8) (actual time=11.731..15.311 rows=460 loops=10839)"
" Index Cond: (ln_pi.pk_putative_inparalog = pi.pk_putative_inparalog)"
"Total runtime: 176024.211 ms"
J'ai oublié de préciser la version: PostgreSQL 8.4.10
Autre observation, lors de la requête, postgres ne semble pas utiliser de CPU pendant un bon moment:
(ligne extraite du 'top', le CPU est à 6%, la RAM à 0.4%)
31315 postgres 20 0 207m 128m 18m D 6 0.4 592:38.45 postgres: linard orthoinspector_eukaryota__2011_12 XXX.XX.XX.XXX 7(39872) SELECT
Bonjour à tous,
Je viens demander votre aide pour optimiser une de mes requête.
Après mise à jour de ma base de données, je me suis retrouvé avec plusieurs tables de centaines de millions de lignes pour une base de donnée d'environ 700Go (tables+index).
La plupart de mes requêtes restent efficaces avec l'utilisation de nombreux index sur mes colonnes, cependant j'ai une requête en particulier qui reste très très lente et c'est là que je fais appelle à votre expérience.
Voici les tables cocernées, leurs index et la requête:
Tables:
CREATE TABLE organism (
pk_organism integer NOT NULL,
identifier character varying(255),
genus character varying(255),
species character varying(255),
phylum character varying(2048)
);
CREATE TABLE sequences (
pk_sequence integer NOT NULL,
pk_version integer,
checksum character varying(64),
description text,
sequence text,
identifier character varying(255)
);
CREATE TABLE putative_inparalog (
pk_putative_inparalog integer NOT NULL,
pk_sequence integer,
pk_organism integer,
pk_organism_rapporteur integer,
pk_version integer
);
CREATE TABLE ln_putative_inparalog_sequence (
pk_putative_inparalog integer,
pk_sequence integer
);
Index et contraintes:
ALTER TABLE ONLY organism
ADD CONSTRAINT pk_organism PRIMARY KEY (pk_organism);
ALTER TABLE ONLY sequences
ADD CONSTRAINT pk_sequences PRIMARY KEY (pk_sequence);
ALTER TABLE ONLY putative_inparalog
ADD CONSTRAINT pk_putative_inparalog PRIMARY KEY (pk_putative_inparalog);
ALTER TABLE ONLY putative_inparalog
ADD CONSTRAINT fk_putative_ref10 FOREIGN KEY (pk_organism_rapporteur) REFERENCES organism(pk_organism) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY putative_inparalog
ADD CONSTRAINT fk_putative_reference_organism9 FOREIGN KEY (pk_organism) REFERENCES organism(pk_organism) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY putative_inparalog
ADD CONSTRAINT fk_putative_reference_sequence7 FOREIGN KEY (pk_sequence) REFERENCES sequences(pk_sequence) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY putative_inparalog
ADD CONSTRAINT fk_putative_reference_version FOREIGN KEY (pk_version) REFERENCES version(pk_version) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY ln_putative_inparalog_sequence
ADD CONSTRAINT fk_ln_putat_reference_putative FOREIGN KEY (pk_putative_inparalog) REFERENCES putative_inparalog(pk_putative_inparalog) ON UPDATE RESTRICT ON DELETE RESTRICT;
ALTER TABLE ONLY ln_putative_inparalog_sequence
ADD CONSTRAINT fk_ln_putat_reference_sequence FOREIGN KEY (pk_sequence) REFERENCES sequences(pk_sequence) ON UPDATE RESTRICT ON DELETE RESTRICT;
CREATE INDEX index_putative_inparalog_pk_organism ON putative_inparalog USING btree (pk_organism);
CREATE INDEX index_putative_inparalog_pk_organism_rapporteur ON putative_inparalog USING btree (pk_organism_rapporteur);
CREATE INDEX index_putative_inparalog_pk_sequence ON putative_inparalog USING btree (pk_sequence);
CREATE INDEX index_putative_inparalog_sequence_pk_sequence ON ln_putative_inparalog_sequence USING btree (pk_sequence);
CREATE INDEX ndex_putative_inparalog_sequence_pk_putative_inparalog ON ln_putative_inparalog_sequence USING btree (pk_putative_inparalog);
Exemple de requête:
select pi.pk_putative_inparalog,pi.pk_sequence,pi.pk_organism,pi.pk_organism_rapporteur,ln_pi.pk_sequence
from putative_inparalog as pi, ln_putative_inparalog_sequence as ln_pi
where pi.pk_organism=24
and pk_organism_rapporteur=118
and pi.pk_putative_inparalog=ln_pi.pk_putative_inparalog
order by pi.pk_putative_inparalog;
Autres infos:
La table ln_putative_inparalog_sequence fait environ 8 milliards de lignes. (400go avec index)
La table ln_putative_inparalog_sequence fait environ 300 millions de lignes.
Execution de la requete cité ci-dessus:
Durée totale d'exécution de la requête :505030 ms. (8 minutes)
4980744 lignes récupérées (5 colonnes d'entiers)
plan de requête:
"Sort (cost=129764478.36..129765829.05 rows=540276 width=20)"
" Sort Key: pi.pk_putative_inparalog"
" -> Nested Loop (cost=71949.14..129713035.07 rows=540276 width=20)"
" -> Bitmap Heap Scan on putative_inparalog pi (cost=71949.14..113545.26 rows=11266 width=16)"
" Recheck Cond: ((pk_organism_rapporteur = 118) AND (pk_organism = 24))"
" -> BitmapAnd (cost=71949.14..71949.14 rows=11266 width=0)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism_rapporteur (cost=0.00..10781.48 rows=582726 width=0)"
" Index Cond: (pk_organism_rapporteur = 118)"
" -> Bitmap Index Scan on index_putative_inparalog_pk_organism (cost=0.00..61161.78 rows=3310766 width=0)"
" Index Cond: (pk_organism = 24)"
" -> Index Scan using ndex_putative_inparalog_sequence_pk_putative_inparalog on ln_putative_inparalog_sequence ln_pi (cost=0.00..9183.24 rows=185628 width=8)"
" Index Cond: (ln_pi.pk_putative_inparalog = pi.pk_putative_inparalog)"
Apparement, la Nested Loop prend la majorité du temps. Comment l'éviter ?
Petit détail, j'aimerai que le requête soit également compatible avec mySQL (le logiciel supporte postgres & mySQL).
Merci pour vos conseils.
Pages : 1
