Indexen gebruiken en maken

Queryprestaties verbeteren in PostgreSQL

Amy McCarty

Instructor

Indexoverzicht

Wat

  • Manier om gesorteerde kolomsleutels te maken voor snellere zoekopdrachten
  • Vergelijkbaar met een boekindex
  • Verwijzing naar de datalocatie

Waarom

  • Snellere queries

Waar

  • Veelgebruikte filterkolommen
  • Primaire sleutel
Queryprestaties verbeteren in PostgreSQL

Indexvoorbeeld

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'
Queryprestaties verbeteren in PostgreSQL

Index als sleutel en pointer

Twee tabellen. Een indextabel bevat de kolom recipe plus een kolom met pointers, die oplopen van _12 tot _18. De tabel met index is dezelfde tabel links met een extra pointer-kolom. De waardes in deze pointer-kolom zijn gelijk aan die in de pointer-kolom van de indextabel. Pijlen tonen hoe de vier waarden voor fried rice in de indextabel overeenkomen met de vier waarden voor fried rice in de tabel met index.

Queryprestaties verbeteren in PostgreSQL

Bestaande indexen vinden

pg_tables
  • Vergelijkbaar met information_schema
    • specifiek voor Postgres
  • Metadata over de database
Queryprestaties verbeteren in PostgreSQL

Bestaande indexen vinden

pg_tables
  • Vergelijkbaar met information_schema
    • specifiek voor Postgres
  • Metadata over de database

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Queryprestaties verbeteren in PostgreSQL

Een index maken

Eettafel met kolommen ingredient, recipe en 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);
Queryprestaties verbeteren in PostgreSQL

Wel of geen index

Gebruik een index

  • Grote tabellen
  • Veelvoorkomende filtercondities
  • Primaire sleutel

Vermijd een index

  • Kleine tabellen
  • Kolommen met veel nulls
  • Vaak geüpdatete tabellen
    • Index raakt gefragmenteerd
    • Schrijft data op twee plaatsen
Queryprestaties verbeteren in PostgreSQL

Vaak geüpdatete tabellen

Twee tabellen. Een indextabel bevat de kolom recipe plus een kolom met pointers, die oplopen van _12 tot _19. De tabel met index is dezelfde tabel links met een extra pointer-kolom. De waardes in deze pointer-kolom zijn gelijk aan die in de pointer-kolom van de indextabel. Pijlen tonen hoe de vier waarden voor spaghetti and meatballs in de indextabel overeenkomen met de vier waarden in de tabel met index. Drie waarden staan bovenaan in de indextabel gegroepeerd. Eén spaghetti and meatballs staat onderaan en hoort bij de nieuwe basil-toevoeging in de tabel met index.

Queryprestaties verbeteren in PostgreSQL

Index-query beoordelen

Query planner

Meerdere koks rond een grote kookpot 

EXPLAIN
SELECT * 
FROM cookbook

 

Queryplan

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Kostenschattingen (tijd)
Queryprestaties verbeteren in PostgreSQL

Laten we oefenen!

Queryprestaties verbeteren in PostgreSQL

Preparing Video For Download...