L'indice de couverture n'est pas seulement une autre fonctionnalitĂ© qui peut ĂȘtre utile. Cette chose est purement pratique. Sans eux, Index Only Scan ne peut pas gagner. Bien que l'indice de couverture dans diffĂ©rentes situations soit efficace de diffĂ©rentes maniĂšres.
Il ne s'agit pas vraiment de couvrir les index: Ă proprement parler, les soi-disant index inclusifs sont apparus dans Postgres. Mais, dans l'ordre: un index de couverture est un index qui contient toutes les valeurs de colonne requises par la requĂȘte; cependant, l'accĂšs Ă la table elle-mĂȘme n'est plus nĂ©cessaire. Presque. Vous pouvez
lire sur «presque» et d'autres nuances dans un article de
Yegor Rogov , inclus dans sa série d'index de 10 (!) Parties. Et l'
index inclus est crĂ©Ă© spĂ©cifiquement pour la recherche sur des requĂȘtes typiques: les valeurs des champs qui ne peuvent pas ĂȘtre recherchĂ©s sont ajoutĂ©es Ă l'index de recherche, elles sont nĂ©cessaires uniquement pour ne plus se rĂ©fĂ©rer Ă la table. Ces index sont formĂ©s avec le mot clĂ© INCLUDE.
Anastasia Lubennikova (Postgres Professional) a finalisĂ© la mĂ©thode btree afin que des colonnes supplĂ©mentaires puissent ĂȘtre incluses dans l'index. Ce correctif Ă©tait inclus dans PostgreSQL 11. Mais les correctifs pour les mĂ©thodes d'accĂšs GiST / SP-GiST n'avaient pas eu le temps de mĂ»rir avant la sortie de cette version. Le 12 GiST a mĂ»ri.
Un désir constructif d'avoir des index inclusifs pour GiST est né il y a longtemps: un patch de test d'Andrey Borodin a été
offert à la communauté à la mi-avril 2018. Il a fait tout le travail de base, trÚs difficile.
Début août 2019, Alexander Korotkov a ajouté des améliorations cosmétiques et a validé le patch.
Ă des fins de dĂ©monstration et de recherche, nous allons gĂ©nĂ©rer un ensemble de 3 millions de rectangles. Dans le mĂȘme temps, quelques mots sur le type de boĂźte, car toutes les manipulations avec lui ne sont pas intuitives.
Le type de boĂźte - c'est-Ă -dire le rectangle - a longtemps Ă©tĂ© dans Postgres, il est dĂ©fini par 2 points (le point de type gĂ©omĂ©trique) - les sommets opposĂ©s du rectangle (c'est-Ă -dire que le rectangle ne peut pas ĂȘtre oblique, jonchĂ© sur le cĂŽtĂ©). Nous lisons dans la
documentation : «les valeurs de type box sont écrites sous l'une des formes suivantes:
( ( x1 , y1 ) , ( x2 , y2 ) ) ( x1 , y1 ) , ( x2 , y2 ) x1 , y1 , x2 , y2
En pratique, vous devez Ă©crire, par exemple, comme ceci:
SELECT box('1,2', '3,4'); box
Tout d'abord, Postgres nous montre le sommet supérieur droit, puis le coin inférieur gauche. Si on écrit comme ça,
SELECT box('5,2', '3,4'); box
alors nous nous assurerons que Postgres n'a pas donné les pics qu'ils lui ont donnés. Il a calculé le coin supérieur droit et inférieur gauche à partir de notre coin supérieur gauche et inférieur droit. Il s'agit d'une propriété pratique lorsque l'emplacement des sommets n'est pas connu à l'avance - en cas de génération aléatoire, par exemple. La notation «1,2», «3,4» équivaut au point (1,2), au point (3,4). Ce formulaire est parfois plus pratique.
Pour les entreprises: recherchez dans 3 millions de rectangles
CREATE TABLE boxes(id serial, thebox box, name text);
Nous générerons 3 millions de rectangles aléatoires. Nous voulons une distribution normale, mais pour ne pas utiliser l'extension
tablefunc , nous utilisons l'approche «pauvre»: nous utilisons random () - random (), qui donne également une belle image (voir la figure) avec des rectangles, plus ils sont grands, plus ils sont proches du centre. Leurs centres de gravité sont également aléatoires. De telles distributions sont caractéristiques de certains types de données réelles sur les villes. Et ceux qui veulent se plonger dans les lois de la statistique ou rafraßchir la mémoire peuvent lire ici la différence des variables aléatoires.
INSERT INTO boxes(thebox, name) SELECT box( point( random()-random(), random()-random() ), point( random()-random(), random()-random() ) ), 'box no.' || x FROM generate_series(1,3000000) AS g(x);
La taille de la table qui affiche
\dt+ est de 242 Mo. Vous pouvez maintenant lancer la recherche.
Nous recherchons sans index:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Nous voyons qu'il y a un Parallel Seq Scan - balayage séquentiel (bien que parallélisé).
Créez un index régulier et non inclusif:
CREATE INDEX ON boxes USING gist(thebox);
La taille de l'index
boxes_thebox_idx , qui affiche
\di+ , 262 Mo. En rĂ©ponse Ă la mĂȘme demande, nous obtenons:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Le temps de recherche a été réduit d'un facteur trois, et au lieu de Parallel Seq Scan, ils ont reçu un Bitmap Index Scan. Il ne se parallélise pas, mais il fonctionne plus rapidement.
Maintenant, tuez l'ancien index et créez-en un:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name);
Index de
boxes_thebox_name_idx plus gros: 356MB. C'est parti:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Index Only Scan est utilisé, mais l'image est triste: le temps est presque 2 fois plus long que sans lui. Nous lisons le manuel du créateur d'indices,
partie I :
âčLes index PostgreSQL rang ne contiennent pas d'informations permettant de juger de la visibilitĂ© des lignes. Par consĂ©quent, la mĂ©thode d'accĂšs renvoie toutes les versions des lignes qui relĂšvent de la condition de recherche, qu'elles soient visibles ou non pour la transaction en cours. Cependant, si le mĂ©canisme d'indexation devait examiner la table Ă chaque fois pour dĂ©terminer la visibilitĂ©, cette mĂ©thode d'analyse ne serait pas diffĂ©rente de l'analyse d'index ordinaire. Le problĂšme est rĂ©solu par le fait que PostgreSQL prend en charge la soi-disant carte de visibilitĂ© des tables, dans laquelle le processus de vide marque les pages dans lesquelles les donnĂ©es n'ont pas changĂ© suffisamment longtemps pour que toutes les transactions puissent les voir, quels que soient l'heure de dĂ©but et le niveau d'isolement. Si l'identifiant de la ligne renvoyĂ©e par l'index fait rĂ©fĂ©rence Ă une telle page, alors la visibilitĂ© ne peut pas ĂȘtre vĂ©rifiĂ©e. âșâș
Nous faisons le VIDE. Répétez:
EXPLAIN ANALYZE SELECT thebox, name FROM boxes WHERE thebox @> box('0.5, 0.4','0.3, 0.2'); QUERY PLAN
Une toute autre affaire! Deux fois le gain par rapport Ă l'indice non inclus.
Sélectivité et gain
Les performances des index inclusifs dĂ©pendent fortement de la sĂ©lectivitĂ© des conditions dans les requĂȘtes. Pour Ă©tudier un peu cette dĂ©pendance, nous allons rĂ©soudre le problĂšme inverse: nous allons gĂ©nĂ©rer une Ă©tiquette avec un index de type point et nous chercherons combien de points tomberont dans la case donnĂ©e. RĂ©partissez les points de maniĂšre uniforme au carrĂ©.
CREATE TABLE test_covergist(id serial, tochka point, name text);
INSERT INTO test_covergist(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,3000000) AS g(x);
La taille de la table est de 211 Mo.
CREATE INDEX on test_covergist USING gist(tochka);
Taille 213 Mo.
Nous allons évidemment mettre tous les points disponibles dans un carré:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Nous avons demandé à EXPLAIN de montrer les tampons. Cela vous sera utile. Maintenant, le temps d'exécution de la demande est supérieur à 2 secondes, on peut voir que Buffers: shared read = 54287. Dans une autre situation, nous pourrions voir un mélange de lecture partagée et de hit partagé - c'est-à -dire que certains tampons sont lus à partir du disque (ou du cache du systÚme d'exploitation), et certains à partir du cache de tampon. Nous connaissons la taille approximative de la table et des index, nous nous protégerons donc en définissant des tampons partagés pour que tout rentre - redémarrez Postgres avec l'option
-o "-c shared_buffers=1GB"
Maintenant:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Autrement dit, la lecture partagée est devenue un succÚs partagé et le temps a été réduit trois fois.
Un autre détail important dans EXPLAIN: 3 millions de points sont retournés, et la prévision du nombre retourné d'enregistrements est de 3 000. Spoiler: ce nombre ne changera avec aucune sélectivité. L'optimiseur ne sait pas comment évaluer la cardinalité lors de l'utilisation de types de boßtes ou de points. Et le plan ne changera pas: pour toute taille de rectangle, il y aura un scan d'index bitmap sur test_covergist_tochka_idx.
Voici deux autres mesures avec le nombre d'enregistrements émis, différents par ordre de grandeur:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Il renvoie 10 fois moins d'enregistrements (réels ... lignes = 269882), le temps a diminué d'environ 5 fois.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','30000,30000') @> tochka; QUERY PLAN
Le contenu d'un carré de 30K à 30K (2780) est compté en seulement 16 ms. Et quand il y a des dizaines d'enregistrements, ils sont déjà extraits en fractions de ms, et de telles mesures ne sont pas trÚs fiables.
Enfin, mesurez la mĂȘme chose avec l'indice inclusif:
CREATE INDEX on test_covergist USING gist(tochka) INCLUDE(name);
Taille 316 Mo.
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Le temps est presque le mĂȘme qu'avec un index conventionnel, bien qu'Index Only Scan.
Mais:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
Et c'était 151 ms. Et, en conséquence:
EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist WHERE box('0,0','300000,300000') @> tochka; QUERY PLAN
C'est dĂ©jĂ une fraction de ms pour les mĂȘmes enregistrements de 2780 points.
Des tampons comme des fusils
Une explication peut ĂȘtre recherchĂ©e et trouvĂ©e dans un fusil de chasse qui n'a pas encore tirĂ© mais qui Ă©tait accrochĂ© au mur: le nombre de blocs lus. Dans le cas d'un index inclusif, seuls les blocs de l'index lui-mĂȘme sont lus (Heap Fetches: 0). Dans trois cas, il s'agissait des numĂ©ros 40492, 3735 et 52. Mais lors de l'utilisation de l'index normal, les blocs lus consistent en la somme des bits lus dans l'index Bitmap Heap Scan (54248 avec 3 millions d'enregistrements) et ceux qui ont dĂ» ĂȘtre lus Ă partir du tas (27223). , car le champ de nom ne peut pas ĂȘtre extrait d'un index standard. 54248 + 27223 = 81471. L'exclusivitĂ© Ă©tait 40492. Pour deux autres cas: 29534 + 2510 = 31044 et 2655 + 31 = 2686. Dans le cas d'un index rĂ©gulier, plus de blocs sont lus de toute façon, mais avec une amĂ©lioration de la sĂ©lectivitĂ©, le nombre de blocs lus commence Ă diffĂ©rer par des ordres de grandeur plutĂŽt que 2 fois en raison du fait que le nombre de blocs nĂ©cessaires d'un tas diminue plus lentement que la lecture de blocs d'index.
Mais peut-ĂȘtre que le point n'est pas du tout la sĂ©lectivitĂ©, mais simplement la taille de la table? Juste au cas oĂč, nous rĂ©pĂ©tons les mĂȘmes Ă©tapes, gĂ©nĂ©rant une table avec 300 000, et non 3 millions d'enregistrements:
CREATE TABLE test_covergist_small(id serial, tochka point, name text); INSERT INTO test_covergist_small(tochka, name) SELECT point(trunc(1000000*random()), trunc(1000000*random())), 'point no.' || gx FROM generate_series(1,300000) AS g(x); CREATE INDEX ON test_covergist_small USING gist(tochka); EXPLAIN (ANALYZE, buffers) SELECT tochka, name FROM test_covergist_small WHERE box('0,0','3000000,3000000') @> tochka; QUERY PLAN
Ensuite, rĂ©pĂ©tez la mĂȘme chose pour l'index inclusif. Voici les rĂ©sultats:
Dans le cas d'une couverture Ă 100% des points, la requĂȘte Ă©tait mĂȘme un peu plus lente qu'avec l'index habituel. De plus, comme dans le cas de 3 millions, tout s'est mis en place. Autrement dit, la sĂ©lectivitĂ© est importante.
Notre sociĂ©tĂ© a testĂ© des indices GiST inclusifs sur des donnĂ©es rĂ©elles - un ensemble de plusieurs millions de rectangles sur une carte de Moscou. La conclusion est la mĂȘme: dans de nombreuses situations, de tels index accĂ©lĂšrent sensiblement les requĂȘtes. Mais l'article ne peut pas ĂȘtre illustrĂ© par des images et des nombres de tests: ces donnĂ©es ne sont pas du domaine public.
Au lieu d'une conclusion
Revenons un instant aux rectangles alĂ©atoires. Essayons de faire de mĂȘme avec spgist. Vous pouvez vous rappeler ou dĂ©couvrir ce que c'est que de comprendre les diffĂ©rences entre SP-GiST et GiST en lisant l'article
Indexes dans PostgreSQL - 6 . Créez un index inclusif:
CREATE INDEX ON boxes USING spgist(thebox) INCLUDE(name); ERROR: access method "spgist" does not support included columns
Hélas, pour SP-GiST, les index inclusifs ne sont pas encore implémentés.
Il y a donc place à amélioration!