Optimisation de la requête postgrès (forçage d'un scan index)
voici ma requête. J'essaie de l'amener à utiliser un scan index, mais il ne fera que seq scan.
soit dit en passant, la table metric_data contient 130 millions de lignes. Le tableau metrics comporte environ 2000 lignes.
metric_data tableau colonnes:
metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)
Comment puis-je obtenir cette requête pour utiliser mon index de clé primaire?
SELECT
S.metric,
D.t,
D.d
FROM metric_data D
INNER JOIN metrics S
ON S.id = D.metric_id
WHERE S.NAME = ANY (ARRAY ['cpu', 'mem'])
AND D.t BETWEEN '2012-02-05 00:00:00'::TIMESTAMP
AND '2012-05-05 00:00:00'::TIMESTAMP;
expliquer:
Hash Join (cost=271.30..3866384.25 rows=294973 width=25)
Hash Cond: (d.metric_id = s.id)
-> Seq Scan on metric_data d (cost=0.00..3753150.28 rows=29336784 width=20)
Filter: ((t >= '2012-02-05 00:00:00'::timestamp without time zone)
AND (t <= '2012-05-05 00:00:00'::timestamp without time zone))
-> Hash (cost=270.44..270.44 rows=68 width=13)
-> Seq Scan on metrics s (cost=0.00..270.44 rows=68 width=13)
Filter: ((sym)::text = ANY ('{cpu,mem}'::text[]))
4 réponses
à des fins de test, vous pouvez forcer l'utilisation de l'index en "désactivant" les scans séquentiels-le mieux dans votre session actuelle seulement:
SET enable_seqscan = OFF;
détails dans le manuel ici. j'ai cité "désactiver", parce que vous ne pouvez pas réellement désactiver les scans de table séquentiels. Mais toute autre option disponible est désormais préférable pour Postgres. Cela prouvera que l'indice multicolumn sur (metric_id, t) peut être utilisé - tout simplement pas comme efficace comme indice sur la colonne de tête.
vous obtiendrez probablement de meilleurs résultats en changeant l'ordre des colonnes dans votre PRIMARY KEY (et l'index utilisé pour l'implémenter derrière les rideaux avec elle) à (t, metric_id) . Ou créez un index additionnel avec des colonnes inversées comme ça.
vous n'avez pas normalement à forcer de meilleurs plans d'interrogation par intervention manuelle. Si définir enable_seqscan = OFF conduit à un beaucoup meilleur plan, quelque chose n'est probablement pas juste dans votre base de données. Considérez cette réponse connexe:
vous ne pouvez pas forcer la numérisation de l'index dans ce cas car cela ne le rendra pas plus rapide.
vous avez actuellement l'index sur metric_data (metric_id, t) , mais le serveur ne peut pas profiter de cet index pour votre requête, parce qu'il doit être en mesure de discriminer par metric_data.t seulement (sans metric_id ), mais il n'y a pas un tel index. Le serveur peut utiliser des sous-champs de l'index composés, mais seulement à partir du début. Par exemple, la recherche par metric_id sera en mesure d'utiliser cette index.
si vous créez un autre index sur metric_data (t) , votre requête fera usage de cet index et fonctionnera beaucoup plus rapidement.
Aussi, vous devez vous assurer que vous avez un index sur metrics (id) .
il semble que vous n'ayez pas les contraintes FK appropriées:
CREATE TABLE metric_data
( metric_id integer
, t timestamp
, d double precision
, PRIMARY KEY (metric_id, t)
, FOREIGN KEY metrics_xxx_fk (metric_id) REFERENCES metrics (id)
)
et dans le tableau metrics:
CREATE TABLE metrics
( id INTEGER PRIMARY KEY
...
);
vérifiez également si vos statistiques sont suffisantes (et suffisamment fines, puisque vous avez l'intention de sélectionner 0,2 % de la table metrics_data)