Comment forcer Postgres à utiliser un indice particulier?

comment forcer Postgres à utiliser un index alors qu'il insisterait pour faire un balayage séquentiel?

74
demandé sur mike 2008-11-21 21:56:57

6 réponses

en supposant que vous posez des questions sur la caractéristique commune" index hinting " trouvée dans de nombreuses bases de données, PostgreSQL ne fournit pas une telle caractéristique. C'était une décision consciente prise par L'équipe de PostgreSQL. Un bon aperçu de pourquoi et ce que vous pouvez faire à la place peut être trouvé ici . Les raisons sont essentiellement que C'est un piratage de performances qui a tendance à causer plus de problèmes plus tard dans la ligne que vos données changent, alors que l'optimiseur de PostgreSQL peut réévaluer le plan basé sur statistique. En d'autres termes, ce qui pourrait être un bon plan de requête aujourd'hui ne sera probablement pas un bon plan de requête pour tous les temps, et des indices d'index forcer un plan de requête particulier pour tous les temps.

comme marteau très contondant, utile pour les tests, vous pouvez utiliser les paramètres enable_seqscan et enable_indexscan . Voir:

ce sont ne convient pas pour une utilisation en cours de production . Si vous avez des problèmes avec le choix du plan de requête, vous devriez voir la documentation pour suivre les problèmes de performance de requête . Ne vous contentez pas de mettre enable_ params et partez.

sauf si vous avez une très bonne raison d'utiliser l'indice, Postgres peut faire le bon choix. Pourquoi?

  • pour petites tables, c'est plus rapide pour faire des scans séquentiels.
  • Postgres n'utilise pas d'index lorsque les types de données ne correspondent pas correctement, vous pouvez avoir besoin d'inclure des casts appropriés.
  • vos paramètres de planification pourraient causer des problèmes.

Voir aussi ce vieux message de groupe de discussion .

77
répondu Patryk Kordylewski 2014-09-09 05:58:33

probablement la seule raison valable pour utiliser

set enable_seqscan=false

est quand vous écrivez des requêtes et que vous voulez voir rapidement ce que le plan de requête serait réellement s'il y avait de grandes quantités de données dans la table(s). Ou bien sûr si vous devez rapidement confirmer que votre requête n'utilise pas un index simplement parce que l'ensemble de données est trop petit.

52
répondu Niraj Bhawnani 2012-11-16 01:33:06

la question sur elle-même est très invalide. Forcer (en faisant enabl_seqscan=off par exemple) est une très mauvaise idée. Il peut être utile de vérifier s'il sera plus rapide, mais le code de production ne devrait jamais utiliser de telles astuces.

au lieu de cela-expliquez analyse de votre requête, lisez-le, et découvrez pourquoi PostgreSQL choisit mauvais (à votre avis) le plan.

Il ya des outils sur le web qui aident à la lecture expliquer analyser les résultats - l'un d'eux est explain.depesz.com - écrit par moi.

une autre option est de rejoindre le canal #postgresql sur freenode réseau irc, et de parler à des gars là - bas pour vous aider-comme l'optimisation de requête n'est pas une question de"poser une question, obtenir réponse être heureux". c'est plus comme une conversation, avec beaucoup de choses à vérifier, beaucoup de choses à apprendre.

10
répondu user80168 2009-07-09 17:58:04

parfois PostgreSQL ne fait pas le meilleur choix d'indices pour une condition particulière. Par exemple, supposons qu'il y ait une table de transactions avec plusieurs millions de lignes, dont il y en a plusieurs centaines pour un jour donné, et que la table comporte quatre index: transaction_id, client_id, date et description. Vous voulez lancer la requête suivante:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description = 'Refund'
GROUP BY client_id

PostgreSQL peut choisir d'utiliser l'index transactions_description_idx au lieu de transactions_date_idx, qui peut conduire à la requête prenant plusieurs minutes au lieu de moins d'une seconde. Si c'est le cas, vous pouvez forcer l'utilisation de l'index à la date en faisant croire à la condition comme ceci:

SELECT client_id, SUM(amount)
FROM transactions
WHERE date >= 'yesterday'::timestamp AND date < 'today'::timestamp AND
      description||'' = 'Refund'
GROUP BY client_id
8
répondu Ziggy Crueltyfree Zeitgeister 2015-06-16 05:17:16

produit Postgresplus Advanced Server de EnterpriseDB prend en charge la syntaxe Oracle hints, bien que ce produit n'est pas libre.

0
répondu 2009-07-09 17:18:44

il y a un tic pour pousser postgres à préférer un seqscan ajoutant un OFFSET 0 dans le sous-query

c'est pratique pour optimiser les requêtes reliant de grandes/grandes tables quand vous cherchez seulement les N premiers/derniers éléments.

permet de dire que vous êtes à la recherche de premier/dernier 20 éléments impliquant plusieurs tables ayant 100k (ou plus) entrées, aucun point de construire / lier toute la requête sur toutes les données lorsque ce que vous cherchez est dans le les 100 ou 1000 premières entrées. Dans ce scénario, par exemple, il s'avère être plus de 10 fois plus rapide de faire une recherche séquentielle.

voir Comment puis-je empêcher Postgres de l'in-lining, une sous-requête?

0
répondu Antony Gibbs 2017-10-26 21:26:53