Utilizarea și crearea indecșilor

Îmbunătățirea performanței interogărilor în PostgreSQL

Amy McCarty

Instructor

Prezentare generală a indecșilor

Ce este

  • Metodă de creare a cheilor sortate pentru a îmbunătăți căutarea
  • Similar cu indexul dintr-o carte
  • Referință la locația datelor

De ce

  • Interogări mai rapide

Unde

  • Coloane frecvente de filtrare
  • Cheie primară
Îmbunătățirea performanței interogărilor în PostgreSQL

Exemplu de index

ingredient recipe
tomatoes spaghetti & meatballs
green onions fried rice
eggs fried rice
ground beef spaghetti & meatballs
pasta spaghetti & meatballs
rice fried rice
soy sauce fried rice 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Îmbunătățirea performanței interogărilor în PostgreSQL

Indexul ca cheie și pointer

Două tabele. Un tabel de index conține coloana rețetă și o coloană de pointeri, de la _12 la _18 secvențial. Tabelul cu index este același tabel din stânga, cu o coloană suplimentară de pointeri. Valorile din această coloană sunt identice cu cele din tabelul de index. Săgețile arată cum cele patru valori pentru orez prăjit din tabelul de index corespund celor patru valori pentru orez prăjit din tabelul cu index.

Îmbunătățirea performanței interogărilor în PostgreSQL

Găsirea indecșilor existenți

pg_tables
  • Similar cu information_schema
    • specific Postgres
  • Metadate despre bază de date
Îmbunătățirea performanței interogărilor în PostgreSQL

Găsirea indecșilor existenți

pg_tables
  • Similar cu information_schema
    • specific Postgres
  • Metadate despre bază de date

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Îmbunătățirea performanței interogărilor în PostgreSQL

Crearea unui index

Tabel dinner cu coloanele ingredient, rețetă și număr de porții. roșii - spaghetti cu chiftele - 4. ceapă verde - orez prăjit - 2. ouă - orez prăjit - 2. carne tocată - spaghetti cu chiftele - 4. paste - spaghetti cu chiftele - 4. orez - orez prăjit - 2. sos de soia - orez prăjit - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Îmbunătățirea performanței interogărilor în PostgreSQL

Să folosim sau nu un index

Folosiți un index

  • Tabele mari
  • Condiții frecvente de filtrare
  • Cheie primară

Evitați un index

  • Tabele mici
  • Coloane cu multe valori nule
  • Tabele actualizate frecvent
    • Indexul se fragmentează
    • Datele se scriu în două locuri
Îmbunătățirea performanței interogărilor în PostgreSQL

Tabele actualizate frecvent

Două tabele. Un tabel de index conține coloana rețetă și o coloană de pointeri, de la _12 la _19 secvențial. Tabelul cu index este același tabel din stânga, cu o coloană suplimentară de pointeri. Valorile din această coloană sunt identice cu cele din tabelul de index. Săgețile arată cum cele patru valori pentru spaghetti cu chiftele din tabelul de index corespund celor patru valori din tabelul cu index. Trei valori sunt grupate la începutul tabelului de index. O intrare pentru spaghetti cu chiftele se află la sfârșit, corespunzând intrării noi pentru busuioc din tabelul cu index.

Îmbunătățirea performanței interogărilor în PostgreSQL

Evaluarea interogărilor cu index

Planificatorul de interogări

Mai mulți bucătari în jurul unui vas mare de gătit 

EXPLAIN
SELECT * 
FROM cookbook

 

Plan de interogare

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Estimări de cost (timp)
Îmbunătățirea performanței interogărilor în PostgreSQL

Să exersăm!

Îmbunătățirea performanței interogărilor în PostgreSQL

Preparing Video For Download...