Uso e creazione degli indici

Migliorare le prestazioni delle query in PostgreSQL

Amy McCarty

Instructor

Panoramica sugli indici

Cosa

  • Crea chiavi ordinate su colonne per velocizzare la ricerca
  • Simile all'indice di un libro
  • Riferimento alla posizione dei dati

Perché

  • Query più veloci

Dove

  • Colonne usate spesso nei filtri
  • Chiave primaria
Migliorare le prestazioni delle query in PostgreSQL

Esempio di indice

ingrediente ricetta
pomodori spaghetti & polpette
cipollotti riso fritto
uova riso fritto
carne macinata spaghetti & polpette
pasta spaghetti & polpette
riso riso fritto
salsa di soia riso fritto 
SELECT *
FROM cookbook
WHERE recipe = 'fried rice'
Migliorare le prestazioni delle query in PostgreSQL

Indice come chiave e puntatore

Due tabelle. Una tabella Indice contiene la colonna recipe e una colonna di puntatori, da _12 a _18 in sequenza. La tabella con indice è la stessa a sinistra con una colonna puntatore aggiuntiva. I valori della colonna puntatore coincidono tra le due tabelle. Le frecce mostrano come i quattro valori per riso fritto nella tabella Indice corrispondono ai quattro nella Tabella con indice.

Migliorare le prestazioni delle query in PostgreSQL

Trovare gli indici esistenti

pg_tables
  • Simile a information_schema
    • specifico di Postgres
  • Metadati del database
Migliorare le prestazioni delle query in PostgreSQL

Trovare gli indici esistenti

pg_tables
  • Simile a information_schema
    • specifico di Postgres
  • Metadati del database

 

SELECT * FROM pg_indexes
schemaname tablename indexname tablespace indexdef
food dinner recipe_index null CREATE INDEX recipe_index ...
Migliorare le prestazioni delle query in PostgreSQL

Creare un indice

Tavolo per la cena con colonne ingrediente, ricetta e porzioni. pomodori - spaghetti & polpette - 4. cipollotti - riso fritto - 2. uova - riso fritto - 2. carne macinata - spaghetti & polpette - 4. pasta - spaghetti & polpette - 4. riso - riso fritto - 2. salsa di soia - riso fritto - 2.

CREATE INDEX recipe_index 
ON cookbook (recipe);

CREATE INDEX CONCURRENTLY recipe_index
ON cookbook (recipe, serving_size);
Migliorare le prestazioni delle query in PostgreSQL

Usarlo o no

Quando usare un indice

  • Tabelle grandi
  • Filtri frequenti
  • Chiave primaria

Quando evitarlo

  • Tabelle piccole
  • Colonne con molti null
  • Tabelle aggiornate spesso
    • L'indice si frammenta
    • Scrive i dati in due punti
Migliorare le prestazioni delle query in PostgreSQL

Tabelle aggiornate spesso

Due tabelle. Una tabella Indice contiene la colonna recipe e una colonna di puntatori, da _12 a _19 in sequenza. La tabella con indice è la stessa a sinistra con una colonna puntatore aggiuntiva. I valori della colonna puntatore coincidono tra le due tabelle. Le frecce mostrano come i quattro valori per spaghetti and meatballs nella tabella Indice corrispondono ai quattro nella Tabella con indice. Tre valori sono ordinati insieme in alto nella tabella Indice. Uno spaghetti and meatballs è in fondo, corrispondente alla nuova voce basil nella Tabella con indice.

Migliorare le prestazioni delle query in PostgreSQL

Valutare le query con indici

Query planner

Più chef attorno a una grande pentola 

EXPLAIN
SELECT * 
FROM cookbook

 

Piano di query

Seq scan on cookbook (cost=0.00...22.70
  rows = 1270 width = 36)
  • Stime di costo (tempo)
Migliorare le prestazioni delle query in PostgreSQL

Passiamo alla pratica !

Migliorare le prestazioni delle query in PostgreSQL

Preparing Video For Download...