Používání a vytváření indexů

Optimalizace výkonu dotazů v PostgreSQL

Amy McCarty

Instructor

Přehled indexů

Co

  • Metoda vytváření seřazených klíčů pro rychlejší vyhledávání
  • Podobné jako rejstřík v knize
  • Odkaz na umístění dat

Proč

  • Rychlejší dotazy

Kde

  • Časté filtrovací sloupce
  • Primární klíč
Optimalizace výkonu dotazů v PostgreSQL

Příklad indexu

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'
Optimalizace výkonu dotazů v PostgreSQL

Index jako klíč a ukazatel

Dvě tabulky. Tabulka indexu obsahuje sloupec recipe a sloupec ukazatelů od _12 do _18. Tabulka s indexem je stejná tabulka vlevo s přidaným sloupcem ukazatelů. Šipky ukazují, jak čtyři záznamy fried rice v tabulce indexu odpovídají čtyřem záznamům fried rice v tabulce s indexem.

Optimalizace výkonu dotazů v PostgreSQL

Vyhledání existujících indexů

pg_tables
  • Podobné jako information_schema
    • specifické pro Postgres
  • Metadata o databázi
Optimalizace výkonu dotazů v PostgreSQL

Vyhledání existujících indexů

pg_tables
  • Podobné jako information_schema
    • specifické pro Postgres
  • Metadata o databázi

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Optimalizace výkonu dotazů v PostgreSQL

Vytvoření indexu

Tabulka dinner se sloupci ingredient, recipe a serving size. tomatoes - spaghetti & meatballs - 4. green onions - fried rice - 2. eggs - fried rice - 2. ground beef - spaghetti & meatballs - 4. pasta - spaghetti & meatballs - 4. rice - fried rice - 2. soy sauce - fried rice - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Optimalizace výkonu dotazů v PostgreSQL

Kdy index použít a kdy ne

Kdy index použít

  • Velké tabulky
  • Časté podmínky filtrování
  • Primární klíč

Kdy index nevyužívat

  • Malé tabulky
  • Sloupce s mnoha hodnotami null
  • Často aktualizované tabulky
    • Index se fragmentuje
    • Data se zapisují na dvě místa
Optimalizace výkonu dotazů v PostgreSQL

Často aktualizované tabulky

Dvě tabulky. Tabulka indexu obsahuje sloupec recipe a sloupec ukazatelů od _12 do _19. Tabulka s indexem je stejná tabulka vlevo s přidaným sloupcem ukazatelů. Šipky ukazují, jak čtyři záznamy spaghetti and meatballs v tabulce indexu odpovídají čtyřem záznamům v tabulce s indexem. Tři záznamy jsou seřazeny nahoře, jeden je na konci tabulky indexu, odpovídá novému záznamu basil.

Optimalizace výkonu dotazů v PostgreSQL

Hodnocení dotazů pomocí indexu

Plánovač dotazů

Více kuchařů kolem velkého hrnce 

EXPLAIN
SELECT * 
FROM cookbook

 

Plán dotazu

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Odhady nákladů (času)
Optimalizace výkonu dotazů v PostgreSQL

Lass uns üben!

Optimalizace výkonu dotazů v PostgreSQL

Preparing Video For Download...