Använda och skapa index

Förbättra frågeprestanda i PostgreSQL

Amy McCarty

Instructor

Översikt av index

Vad

  • Metod för att skapa sorterade kolumnnycklar och förbättra sökning
  • Liknar ett bokindex
  • Referens till datats plats

Varför

  • Snabbare frågor

Var

  • Vanliga filterkolumner
  • Primärnyckel
Förbättra frågeprestanda i PostgreSQL

Indexexempel

ingrediens recept
tomater spaghetti och köttbullar
salladslök stekt ris
ägg stekt ris
nötfärs spaghetti och köttbullar
pasta spaghetti och köttbullar
ris stekt ris
sojasås stekt ris 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Förbättra frågeprestanda i PostgreSQL

Index som nyckel och pekare

Två tabeller. En indextabell innehåller receptkolumnen samt en pekarkolumn med värden från _12 till _18 i sekvens. Tabellen med index är samma tabell som till vänster men med en extra pekarkolumn med samma värden. Pilar visar hur de fyra värdena för stekt ris i indextabellen motsvarar de fyra värdena för stekt ris i tabellen med index.

Förbättra frågeprestanda i PostgreSQL

Hitta befintliga index

pg_tables
  • Liknar information_schema
    • specifik för Postgres
  • Metadata om databasen
Förbättra frågeprestanda i PostgreSQL

Hitta befintliga index

pg_tables
  • Liknar information_schema
    • specifik för Postgres
  • Metadata om databasen

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Förbättra frågeprestanda i PostgreSQL

Skapa ett index

Middagstabell med kolumnerna ingrediens, recept och portionsstorlek. tomater - spaghetti och köttbullar - 4. salladslök - stekt ris - 2. ägg - stekt ris - 2. nötfärs - spaghetti och köttbullar - 4. pasta - spaghetti och köttbullar - 4. ris - stekt ris - 2. sojasås - stekt ris - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Förbättra frågeprestanda i PostgreSQL

Att använda eller inte använda

Använd ett index

  • Stora tabeller
  • Vanliga filtervillkor
  • Primärnyckel

Undvik ett index

  • Små tabeller
  • Kolumner med många null-värden
  • Tabeller som uppdateras ofta
    • Index fragmenteras
    • Data skrivs på två ställen
Förbättra frågeprestanda i PostgreSQL

Tabeller som uppdateras ofta

Två tabeller. En indextabell innehåller receptkolumnen samt en pekarkolumn med värden från _12 till _19 i sekvens. Tabellen med index är samma tabell som till vänster men med en extra pekarkolumn med samma värden. Pilar visar hur de fyra värdena för spaghetti och köttbullar i indextabellen motsvarar de fyra värdena för spaghetti och köttbullar i tabellen med index. Tre av värdena är samlade överst i indextabellen. Ett spaghetti och köttbullar-värde finns längst ned i indextabellen, motsvarande den nya basilikumposten i tabellen med index.

Förbättra frågeprestanda i PostgreSQL

Utvärdering av indexfrågor

Frågeoptimeraren

Flera kockar kring en stor gryta 

EXPLAIN
SELECT * 
FROM cookbook

 

Frågeplan

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Uppskattad kostnad (tid)
Förbättra frågeprestanda i PostgreSQL

Nu kör vi en övning!

Förbättra frågeprestanda i PostgreSQL

Preparing Video For Download...