Vous n'êtes pas identifié(e).
Pages : 1
Bonjour à tous,
Version : PostgreSQL 12.4 sur CentOS Linux release 7.7.1908.
J'ai un problème sur une requête, sans limit elle s'exécute très rapidement, si j'ajoute un limit les perfs sont totalement dégradés (et le plan d'exécution n'est plus du tout le même).
J'ai lancé un analyze sur les tables concernés (en essayant d'augmenter le statistics sur certaines colonnes), j'ai essayé pas mal de chose et je ne sais pas trop quoi faire de plus.
Est-ce que vous auriez une idée ?
Chaque table a une PK sur l'ID (Ex : pk_SF).
Chaque table à une FK vers la table qui suit dans les join, donc SF a une FK vers FI, FI vers FA, et FA vers US. La colonne Id_Cu est indexée (fk_US_ID_CU).
J'ai collé le plan d'exécution ici pour une meilleure visibilité : https://explain.dalibo.com/
Je ne sais pas comment le formater dans ce message, je vais regarder.
explain(analyze,buffers) select SF.ID
from SF
inner join FI on SF.ID_FI=FI.ID
inner join FA on FI.ID_FA=FA.ID
inner join US on FA.ID_US=US.ID
where US.ID_Cu=3589
order by SF.ID asc limit 500;
Limit (cost=1001.72..7982.26 rows=500 width=8) (actual time=11286.838..11367.893 rows=500 loops=1)
Buffers: shared hit=50681527 read=300668
-> Gather Merge (cost=1001.72..4725621.47 rows=338414 width=8) (actual time=11286.837..11367.852 rows=500 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=50681527 read=300668
-> Nested Loop (cost=1.70..4685560.07 rows=141006 width=8) (actual time=11281.667..11284.336 rows=376 loops=3)
Buffers: shared hit=50681527 read=300668
-> Nested Loop (cost=1.28..3662972.00 rows=2101335 width=8) (actual time=0.128..8512.638 rows=1317068 loops=3)
Buffers: shared hit=34876711 read=300666
-> Nested Loop (cost=0.86..2726320.62 rows=2101335 width=8) (actual time=0.095..5764.520 rows=1317068 loops=3)
Buffers: shared hit=19071965 read=300594
-> Parallel Index Scan using pk_SF on SF (cost=0.43..1446549.99 rows=2101335 width=8) (actual time=0.049..2213.763 rows=1317068 loops=3)
Buffers: shared hit=3296007 read=271734
-> Index Scan using pk_FI on FI (cost=0.43..0.61 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3951204)
Index Cond: (FI.ID = SF.ID_FI)
Buffers: shared hit=15775958 read=28860
-> Index Scan using pk_FA on FA (cost=0.42..0.45 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3951204)
Index Cond: (FA.ID = FI.ID_FA)
Buffers: shared hit=15804746 read=72
-> Index Scan using pk_US on US (cost=0.42..0.49 rows=1 width=4) (actual time=0.002..0.002 rows=0 loops=3951204)
Index Cond: (US.ID = FA.ID_US)
Filter: (ID_Cu = 3589)
Rows Removed by Filter: 1
Buffers: shared hit=15804816 read=2
Planning Time: 0.948 ms
Execution Time: 11367.991 ms
explain(analyze,buffers) select SF.ID
from SF
inner join FI on SF.ID_FI=FI.ID
inner join FA on FI.ID_FA=FA.ID
inner join US on FA.ID_US=US.ID
where US.ID_Cu=3589
order by SF.ID asc;
Gather Merge (cost=221551.06..244443.77 rows=199067 width=8) (actual time=242.353..261.827 rows=64225 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=320499 read=10667
-> Sort (cost=220551.05..221048.72 rows=199067 width=8) (actual time=237.831..239.886 rows=32112 loops=2)
Sort Key: SF.ID
Sort Method: quicksort Memory: 3484kB
Worker 0: Sort Method: quicksort Memory: 1831kB
Buffers: shared hit=320499 read=10667
-> Nested Loop (cost=3018.77..203030.28 rows=199067 width=8) (actual time=2.695..227.630 rows=32112 loops=2)
Buffers: shared hit=320492 read=10667
-> Nested Loop (cost=3018.34..64734.51 rows=80414 width=4) (actual time=2.626..105.165 rows=32176 loops=2)
Buffers: shared hit=68890 read=4990
-> Parallel Hash Join (cost=3017.91..7059.10 rows=10456 width=4) (actual time=2.573..31.903 rows=4622 loops=2)
Hash Cond: (FA.ID_US = US.ID)
Buffers: shared hit=3297 read=28
-> Parallel Seq Scan on FA (cost=0.00..3632.16 rows=155816 width=8) (actual time=0.008..10.456 rows=132444 loops=2)
Buffers: shared hit=2046 read=28
-> Parallel Hash (cost=2958.81..2958.81 rows=4728 width=4) (actual time=2.256..2.257 rows=3905 loops=2)
Buckets: 8192 Batches: 1 Memory Usage: 384kB
Buffers: shared hit=1207
-> Parallel Bitmap Heap Scan on US (cost=170.71..2958.81 rows=4728 width=4) (actual time=0.857..3.411 rows=7810 loops=1)
Recheck Cond: (ID_Cu = 3589)
Heap Blocks: exact=1171
Buffers: shared hit=1207
-> Bitmap Index Scan on fk_US_ID_CU (cost=0.00..168.70 rows=8038 width=0) (actual time=0.635..0.636 rows=7810 loops=1)
Index Cond: (ID_Cu = 3589)
Buffers: shared hit=36
-> Index Scan using fk_FI_FA on FI (cost=0.43..4.80 rows=72 width=8) (actual time=0.004..0.015 rows=7 loops=9244)
Index Cond: (FA.ID = FI.ID_FA)
Buffers: shared hit=65593 read=4962
-> Index Scan using fk_SF_FI on SF (cost=0.43..1.67 rows=5 width=8) (actual time=0.003..0.003 rows=1 loops=64351)
Index Cond: (SF.ID_FI = FI.ID)
Buffers: shared hit=251602 read=5677
Planning Time: 0.764 ms
Execution Time: 265.550 ms
Hors ligne
Hors ligne
J'ai essayé de passe par un CTE et de mettre le limit puis le order by + limit en dehors ça n'a rien changé.
Hors ligne
Avec un LIMIT, vous demandez à PostgreSQL de favoriser le coût de départ et non pas le coût final. Faire un Sort a un coût de départ très fort, donc pour ne pas faire le tri, il bascule sur la lecture d'index (les données sont déjà triées dans un index). ET même si lire un index est rapide (0.02 ms ici), si vous le lisez 4 millions de fois (3951204 exactement), forcément ça prend beaucoup de temps.
Comment faire pour améliorer ça? Déjà, commencer par mettre à jour votre serveur PostgreSQL. D'après https://why-upgrade.depesz.com/show?fro … &keywords=, vous avez 479 bugs non corrigés sur votre version et une recherche rapide montre que le planificateur/optimiseur a quelques bugs de corrigé. Pas sûr que ça corrige votre problème, mais ça devrait déjà être la première étape.
Guillaume.
Hors ligne
Bonjour,
Ok merci pour votre retour, je vais dupliquer l'instance et faire un upgrade pour voir ce que ça donne.
Hors ligne
Pages : 1
