Postgres qui bouffe le CPU sur une query triviale même avec index

Salut. 50ms pour un PK lookup c'est chelou. t'as run un explain analyze sur ta query

ouais bien sûr. c'est un index scan mais le cost est super haut. des fois il fait un seq scan même pour un id unique

EXPLAIN ANALYZE SELECT * FROM ma_grosse_table WHERE id = 12345;
                                                      QUERY PLAN                                                     
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using ma_grosse_table_pkey on ma_grosse_table  (cost=0.57..8.59 rows=1 width=230) (actual time=0.022..0.023 rows=1 loops=1)
   Index Cond: (id = 12345)
 Planning Time: 0.123 ms
 Execution Time: 0.035 ms

le explain que tu files est rapide. c'est peut-être pas toujours le cas ou c'est pas la query exacte. regarde si t'as pas de table bloat ou d'index bloat. un VACUUM FULL sur la table peut aider mais ça bloque tout attention

oui c'était un exemple pour montrer. le problème est intermittent. j'ai vu des articles sur le bloat. comment je check ça proprement sans bloquer la prod

tu peux utiliser l'extension pg_stat_statements pour voir les vrais temps et les queries les plus chères. pour le bloat tu as des requêtes sur pg_stat_all_tables et pg_indexes qui te donnent des stats de tupples vivants/morts et la taille des index. ou utiliser pg_repack

j'ai run un check et ouais l'index PK est bloated de ouf genre 40%. pas étonnant le query planner doit hésiter à l'utiliser. pg_repack ça nécessite un downtime

pg_repack c'est sans downtime normalement. mais ça prend des ressources. t'as regardé ton autovacuum config. il tourne assez souvent et avec les bons params pour ta table de 500M

autovacuum est en mode par défaut. ptete qu'il galère avec la taille. faudrait augmenter autovacuum_vacuum_scale_factor ou autovacuum_analyze_scale_factor pour cette table spécifique

exactement. ou baisser le threshold. faut trouver le bon équilibre pour pas qu'il tourne en permanence non plus. et ton work_mem il est comment sur ta session. des fois des gros join ou sort peuvent faire péter le cpu si ça déborde sur disk

work_mem est à 4MB. c'est pas pour des grosses queries c'est vraiment juste le lookup par PK. j'ai essayé de relancer ANALYZE ma_grosse_table; pour rafraîchir les stats mais ça change rien

le souci est ptete pas dans la query elle-même mais dans l'état de l'index. si tu reconstruis l'index avec REINDEX TABLE ma_grosse_table; ça résoudra le bloat mais ça bloque la table. sinon tu peux créer un nouvel index en parallèle puis drop l'ancien

créer un nouvel index en parallèle c pas con. genre CREATE INDEX CONCURRENTLY nouveau_pk ON ma_grosse_table (id); puis après je vire l'ancien et je le renomme

oui mais tu peux pas faire ça sur un PK direct. tu dois d'abord drop la contrainte PK et la recréer. ou tu crées un unique index en concurrently, drop la PK, et recrée la PK sur le nouvel index. c'est tricky

arf oui c'est plus compliqué que prévu. je vais tenter avec pg_repack sur un env de staging d'abord pour voir l'impact. si ça marche je ferais ça sur la prod avec une fenêtre de maintenance courte

bon j'ai testé pg_repack en staging. l'index est nickel maintenant. les queries sont instantanées. je vais planifier ça pour la prod. merci pour l'aide ça m'a sauvé la mise