pg 14 query plan merdique sur une jointure complexe

t'as fait un vacuum analyze sur tes tables récemment ? si les stats sont périmées le planner peut se planter

quel est le type de l'index sur created_at ? et la sélectivité de ta clause where sur created_at ? si ça retourne 99% des lignes l'index est pas utile

oui le vacuum analyze tourne toutes les nuits. l'index sur created_at est un btree classique. et la clause where retourne genre 10k lignes sur 50M donc très sélective

ok si la sélectivité est bonne et les stats à jour c'est chelou. t'as quoi dans le explain analyze verbose de la requête ? ptete une conversion de type implicite qui bloque l'index ?

je vous mets le début de l'explain analyze. pas de conversion de type à première vue.

EXPLAIN ANALYZE VERBOSE SELECT a.id, b.name, c.value
FROM huge_table a
JOIN small_table b ON a.b_id = b.id
JOIN other_table c ON a.c_id = c.id
WHERE a.created_at > NOW() - INTERVAL '1 day'
AND b.type = 'active';


QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=1000.00..100000.00 rows=10000 width=72) (actual time=1000.00..10000.00 rows=10000 loops=1)
  ->  Seq Scan on huge_table a  (cost=0.00..80000.00 rows=10000 width=40) (actual time=0.00..9000.00 rows=10000 loops=1)
        Filter: (created_at > (now() - '1 day'::interval))
        Rows Removed by Filter: 49990000
  ->  Index Scan using small_table_pkey on small_table b  (cost=0.00..10.00 rows=1 width=32) (actual time=0.00..0.10 rows=1 loops=10000)
        Index Cond: (id = a.b_id)
        Filter: (type = 'active')

ah je vois le souci. le planner décide de faire le seq scan sur huge_table en premier à cause de ta clause WHERE a.created_at > NOW() - INTERVAL '1 day'. Même si c'est sélectif le planner estime que le coût de l'index scan sur created_at est supérieur au seq scan car la table est grosse et les created_at récents sont très dispersés

c'est ça. si les lignes récentes sont insérées un peu partout (pas contiguës physiquement) l'index scan implique bcp de random i/o. un seq scan peut être plus rapide sur un disque classique

mais c'est un SSD le disque. les random i/o devraient pas être un problème.

même sur ssd, les blocks de données accédés par un index scan peuvent être très éloignés. ça dépend de l'ordre d'insertion. si t'as pas de cluster sur created_at les données physiques sont pas triées

tu peux essayer de forcer l'ordre des jointures avec des CTEs ou subqueries pour que la condition sur b.type soit appliquée plus tôt. ou un SET enable_seqscan = off; juste pour le test pour voir si l'index scan est effectivement plus rapide

ok je tente le SET enable_seqscan = off; pour voir le comportement.

bon en désactivant le seq scan, la requête passe de 10s à 500ms. le planner utilisait bien l'index sur created_at.

SET enable_seqscan = off;
EXPLAIN ANALYZE SELECT a.id, b.name, c.value
FROM huge_table a
JOIN small_table b ON a.b_id = b.id
JOIN other_table c ON a.c_id = c.id
WHERE a.created_at > NOW() - INTERVAL '1 day'
AND b.type = 'active';


QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop  (cost=0.43..100.00 rows=10000 width=72) (actual time=0.04..500.00 rows=10000 loops=1)
  ->  Index Scan using huge_table_created_at_idx on huge_table a  (cost=0.43..10.00 rows=10000 width=40) (actual time=0.03..400.00 rows=10000 loops=1)
        Index Cond: (created_at > (now() - '1 day'::interval))
  ->  Index Scan using small_table_pkey on small_table b  (cost=0.00..10.00 rows=1 width=32) (actual time=0.00..0.10 rows=1 loops=10000)
        Index Cond: (id = a.b_id)
        Filter: (type = 'active')

ça confirme que le planner avait une mauvaise estimation des coûts. t'as ptete des statistiques un peu biaisées ou une distribution de données particulière. tu peux essayer de re-analyser la table avec un ANALYZE huge_table (created_at); après un ALTER TABLE huge_table ALTER COLUMN created_at SET STATISTICS 1000; pour augmenter le nombre d'échantillons.

ou la colonne created_at n'est pas très bien distribuée pour le planner même si elle semble sélective. une option c'est de faire un index partiel si la clause created_at > NOW() - INTERVAL '1 day' est très souvent utilisée.

je vais tester l'augmentation des stats et l'index partiel. en attendant je vais forcer un hint avec un CTE si je peux pas toucher aux paramètres globaux. merci les gars !