Il ya plusieurs mois - public à PostgreSQL.
Vous l'avez déjà utilisé plus de 6000 XNUMX fois, mais une fonctionnalité pratique qui est peut-être passée inaperçue est indices structurels, qui ressemble à ceci :
Écoutez-les et vos demandes « deviendront douces et soyeuses ». 🙂
Mais sérieusement, de nombreuses situations rendent une requête lente et gourmande en ressources sont typiques et peuvent être reconnus par la structure et les données du plan.
Dans ce cas, chaque développeur individuel n'a pas à rechercher lui-même une option d'optimisation, en s'appuyant uniquement sur son expérience - nous pouvons lui dire ce qui se passe ici, quelle pourrait en être la raison, et comment aborder une solution. C'est ce que nous avons fait.
Examinons ces cas de plus près : comment ils sont définis et à quelles recommandations ils conduisent.
Pour mieux vous immerger dans le sujet, vous pouvez d'abord écouter le bloc correspondant de , et ensuite seulement passer à une analyse détaillée de chaque exemple :
#1 : indice « sous-tri »
Quand survient
Afficher la dernière facture du client "LLC Kolokolchik".
Comment identifier
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
Recommandations
Indice utilisé développer avec des champs de tri.
Exemple:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
Vous pouvez immédiatement remarquer que plus de 100 enregistrements ont été soustraits de l'index, qui ont ensuite tous été triés, puis il n'en reste qu'un seul.
Correction :
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
Même sur un échantillon aussi primitif - 8.5 fois plus rapide et 33 fois moins de lectures. Plus vous avez de « faits » pour chaque valeur, plus l’effet est évident fk.
Je note qu'un tel index fonctionnera comme un index « préfixe » pas pire qu'avant pour d'autres requêtes avec fk, où trier par pk il n'y en avait pas et il n'y en a pas (vous pouvez en savoir plus à ce sujet ). Y compris, il fournira normalement prise en charge explicite des clés étrangères sur ce domaine.
#2 : intersection d'index (BitmapAnd)
Quand survient
Afficher tous les accords pour le client « LLC Kolokolchik », conclus au nom de « NAO Buttercup ».
Comment identifier
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index ScanRecommandations
Créer index composé par les champs des deux originaux ou développez l'un des champs existants avec les champs du second.
Exemple:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
Correction :
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
Le gain ici est moindre, puisque Bitmap Heap Scan est assez efficace en soi. Mais peu importe 7 fois plus rapide et 2.5 fois moins de lectures.
#3 : Fusionner les index (BitmapOr)
Quand survient
Afficher les 20 premières demandes de traitement « nous » les plus anciennes ou non attribuées, la vôtre en priorité.
Comment identifier
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index ScanRecommandations
À utiliser UNION [TOUS] pour combiner des sous-requêtes pour chacun des blocs de conditions OR.
Exemple:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
Correction :
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
Nous avons profité du fait que les 20 enregistrements requis ont été immédiatement reçus dans le premier bloc, de sorte que le second, avec le Bitmap Heap Scan plus « coûteux », n'a même pas été exécuté - en fin de compte 22 fois plus rapide, 44 fois moins de lectures!
Une histoire plus détaillée sur cette méthode d'optimisation sur des exemples concrets peut être lu dans les articles и .
Version généralisée sélection ordonnée basée sur plusieurs clés (et pas seulement la paire const/NULL) est abordé dans l'article .
#4 : On lit beaucoup de choses inutiles
Quand survient
En règle générale, cela se produit lorsque vous souhaitez « attacher un autre filtre » à une demande déjà existante.
"Et tu n'as pas le même, mais avec boutons en nacre? » film "Le bras de diamant"
Par exemple, en modifiant la tâche ci-dessus, affichez les 20 premières demandes de traitement « critiques » les plus anciennes, quelle que soit leur finalité.
Comment identifier
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
Recommandations
Créer [plus] spécialisé index avec la condition WHERE ou inclure des champs supplémentaires dans l'index.
Si la condition du filtre est "statique" pour vos besoins, c'est-à-dire n'implique pas une expansion liste de valeurs à l'avenir - il est préférable d'utiliser un index WHERE. Divers statuts booléens/énumérations entrent bien dans cette catégorie.
Si la condition de filtrage peut prendre différentes significations, alors il est préférable d'étendre l'index avec ces champs - comme dans la situation avec BitmapAnd ci-dessus.
Exemple:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
Correction :
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
Comme vous pouvez le constater, le filtrage a complètement disparu du plan, et la requête est devenue 5 fois plus rapide.
#5 : table clairsemée
Quand survient
Diverses tentatives pour créer votre propre file d'attente de traitement des tâches, lorsqu'un grand nombre de mises à jour/suppressions d'enregistrements sur la table conduisent à une situation d'un grand nombre d'enregistrements « morts ».
Comment identifier
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recommandations
Effectuer manuellement régulièrement VIDE [PLEIN] ou réaliser une formation suffisamment fréquente en affinant ses paramètres, notamment .
Dans la plupart des cas, ces problèmes sont causés par une mauvaise composition des requêtes lors d'appels à partir d'une logique métier, comme ceux évoqués dans .
Mais vous devez comprendre que même le VIDE PLEIN ne peut pas toujours aider. Pour de tels cas, il vaut la peine de vous familiariser avec l'algorithme de l'article .
#6 : Lecture à partir du « milieu » de l’index
Quand survient
Il semble que nous lisons un peu, que tout a été indexé et que nous n'avons filtré personne en excès - mais nous lisons quand même beaucoup plus de pages que nous le souhaiterions.
Comment identifier
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
Recommandations
Examinez attentivement la structure de l'index utilisé et les champs clés spécifiés dans la requête - très probablement une partie de l'index n'est pas définie. Très probablement, vous devrez créer un index similaire, mais sans les champs de préfixe ni .
Exemple:
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
Tout semble aller bien, même selon l'index, mais c'est en quelque sorte suspect - pour chacun des 20 enregistrements lus, nous avons dû soustraire 4 pages de données, 32 Ko par enregistrement - n'est-ce pas audacieux ? Et le nom de l'index tbl_fk_org_fk_cli_idx qui fait réfléchir.
Correction :
CREATE INDEX ON tbl(fk_cli);
Soudainement - 10 fois plus rapide et 4 fois moins à lire!
D'autres exemples de situations d'utilisation inefficace des index peuvent être vus dans l'article .
#7 : CTE × CTE
Quand survient
En demande a obtenu un CTE « gros » de différentes tables, puis j'ai décidé de le faire entre elles JOIN.
Le cas est pertinent pour les versions inférieures à la v12 ou les requêtes avec WITH MATERIALIZED.
Comment identifier
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
Recommandations
Analysez soigneusement la demande - et ? Si oui, alors appliquer "dictionnaire" dans hstore/json selon le modèle décrit dans .
#8 : échanger sur le disque (écrit temporairement)
Quand survient
Le traitement ponctuel (tri ou unicité) d'un grand nombre d'enregistrements ne rentre pas dans la mémoire allouée à cet effet.
Comment identifier
-> *
&& temp written > 0Recommandations
Si la quantité de mémoire utilisée par l'opération ne dépasse pas largement la valeur spécifiée du paramètre , cela vaut la peine de le corriger. Vous pouvez immédiatement dans la configuration pour tout le monde, ou vous pouvez via SET [LOCAL] pour une demande/transaction spécifique.
Exemple:
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
Correction :
SET work_mem = '128MB'; -- перед выполнением запроса
Pour des raisons évidentes, si seule la mémoire est utilisée et non le disque, la requête sera exécutée beaucoup plus rapidement. Dans le même temps, une partie de la charge du disque dur est également supprimée.
Mais vous devez comprendre que vous ne pourrez pas toujours allouer beaucoup de mémoire - il n'y en aura tout simplement pas assez pour tout le monde.
#9 : statistiques non pertinentes
Quand survient
Ils ont versé beaucoup de choses dans la base de données à la fois, mais n'ont pas eu le temps de la chasser ANALYZE.
Comment identifier
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10Recommandations
Réalisez-le ANALYZE.
Cette situation est décrite plus en détail dans .
#10 : « quelque chose s’est mal passé »
Quand survient
Il y a eu une attente pour un verrou imposé par une requête concurrente, ou les ressources matérielles du processeur/hyperviseur étaient insuffisantes.
Comment identifier
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
Recommandations
Utiliser externe Système de surveillance serveur pour blocage ou consommation anormale de ressources. Nous avons déjà parlé de notre version d'organiser ce processus pour des centaines de serveurs и .
Source: habr.com