Indeksy i podstawy wydajności: pomagamy PostgreSQL szybciej znajdować dane

Witaj z powrotem.

W poprzedniej lekcji poznałeś funkcje agregujące.

Nauczyłeś się liczyć wiersze.

Sumować wartości.

Obliczać średnie.

Znajdować wartości minimalne i maksymalne.

Grupować dane.

Filtrować grupy za pomocą HAVING.

Bardzo dobrze.

Teraz PostgreSQL potrafi odpowiadać na pytania typu:

Ile mamy produktów?
Jaka jest całkowita wartość magazynu?
Ile zamówień ma każdy klient?
Która kategoria ma najwięcej produktów?

Świetnie.

Ale teraz pojawia się inne pytanie.

Bardzo poważne pytanie.

Co się stanie, kiedy tabela stanie się ogromna?

Kiedy tabela ma dziesięć wierszy, wszystko jest szybkie.

Kiedy tabela ma dziesięć milionów wierszy, PostgreSQL zaczyna patrzeć na ciebie inaczej.

Mniej więcej tak:

Jesteś pewien tego zapytania?

Tutaj pomagają indeksy.

Indeksy pomagają PostgreSQL szybciej znajdować dane.

To nie magia.

Ale to potężne narzędzie.

Jak dobra mapa.

Bez indeksu PostgreSQL może być zmuszony przeczytać całą tabelę.

Z indeksem PostgreSQL może szybciej przejść do potrzebnych danych.

Bardzo przydatne.

Bardzo bazodanowe.

Bardzo “proszę, nie zmuszaj mojego serwera do płaczu”.

Czego się nauczysz

W tej lekcji nauczysz się:

• dlaczego zapytania mogą stawać się wolne;
• czym jest indeks;
• jak indeksy pomagają PostgreSQL szybciej znajdować wiersze;
• czym jest skanowanie sekwencyjne;
• czym jest skanowanie przez indeks;
• jak używać EXPLAIN;
• jak używać EXPLAIN ANALYZE;
• jak stworzyć indeks;
• kiedy indeksy są przydatne;
• kiedy indeksy nie są przydatne;
• dlaczego indeksy nie są darmowe;
• jak indeksy wpływają na wstawianie i aktualizowanie danych;
• jak działają indeksy unikalne;
• jak działają indeksy na kilku kolumnach;
• jakie są typowe błędy z indeksami.

Pod koniec tej lekcji zrozumiesz podstawową ideę wydajności w bazach danych.

To jeszcze nie rakietowa nauka.

Jeszcze nie.

Tylko tyle, żeby przestać pisać zapytania, po których PostgreSQL wygląda na zmęczonego.

Szlachetny cel.

Przygotowanie bazy danych

Otwórz PostgreSQL:

sudo -iu postgres psql

Połącz się ze swoją bazą danych:

\c learning_postgresql

Jeśli baza jeszcze nie istnieje, utwórz ją:

CREATE DATABASE learning_postgresql;

Potem połącz się z nią:

\c learning_postgresql

Teraz usuń starą tabelę, jeśli istnieje:

DROP TABLE IF EXISTS products;

Utwórz nową tabelę products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  quantity INTEGER CHECK (quantity >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ta tabela jest prosta.

Ale wystarczy do nauki indeksów.

Mamy:

• nazwę produktu;
• kategorię;
• cenę;
• ilość;
• datę utworzenia.

Zwykła mała tabela.

Na razie.

Za chwilę stanie się małą armią wierszy.

PostgreSQL jest gotowy.

Chyba.

Dodajemy kilka wierszy

Dodaj kilka produktów:

INSERT INTO products (name, category, price, quantity)
VALUES
  ('Laptop', 'Electronics', 900.00, 5),
  ('Mouse', 'Electronics', 25.00, 30),
  ('Keyboard', 'Electronics', 70.00, 15),
  ('Desk Chair', 'Furniture', 150.00, 8),
  ('Bookshelf', 'Furniture', 120.00, 4),
  ('Notebook', 'Office', 5.00, 100),
  ('Pen', 'Office', 2.00, 200),
  ('Whiteboard', 'Office', 80.00, 3);

Sprawdź dane:

SELECT * FROM products;

Ta tabela jest malutka.

Malutka tabela jest szybka nawet bez indeksów.

PostgreSQL może mrugnąć i ją przeczytać.

Ale prawdziwe aplikacje nie zostają małe na zawsze.

Dane rosną.

Logi rosną.

Zamówienia rosną.

Użytkownicy rosną.

Baza danych rośnie.

A potem pewnego dnia jakieś zapytanie staje się wolne i wszyscy patrzą na programistę.

Bardzo niesprawiedliwe.

Ale czasem też słuszne.

Dodajemy więcej wierszy do testów

Żeby zobaczyć idee wydajności, potrzebujemy więcej wierszy.

PostgreSQL ma przydatną funkcję generate_series.

Potrafi generować wiele wierszy.

Wykonaj to:

INSERT INTO products (name, category, price, quantity)
SELECT
  'Product ' || number,
  CASE
    WHEN number % 4 = 0 THEN 'Electronics'
    WHEN number % 4 = 1 THEN 'Furniture'
    WHEN number % 4 = 2 THEN 'Office'
    ELSE 'Kitchen'
  END,
  (number % 100) + 1,
  (number % 50) + 1
FROM generate_series(1, 100000) AS number;

To wstawi sto tysięcy produktów.

Nie prawdziwych produktów.

Fałszywych produktów.

Ale przydatnych fałszywych produktów.

Jak hantle treningowe dla PostgreSQL.

Teraz sprawdź liczbę wierszy:

SELECT COUNT(*) AS total_products
FROM products;

Powinieneś zobaczyć ponad sto tysięcy wierszy.

Teraz tabela jest wystarczająco duża, żeby przykłady wydajności były ciekawsze.

Jeszcze nie ogromna.

Ale już nie mała zabawka.

Dlaczego zapytania stają się wolne

Zapytanie może być wolne z wielu powodów.

Dla początkującego najczęstszy powód jest prosty:

PostgreSQL musi sprawdzić zbyt wiele wierszy.

Przykład:

SELECT *
FROM products
WHERE category = 'Electronics';

Jeśli nie ma indeksu na category, PostgreSQL może być zmuszony sprawdzić każdy wiersz.

Jeden po drugim.

Jak szukanie jednej kartki w zagraconym garażu.

Możliwe.

Ale mało eleganckie.

To nazywa się skanowanie sekwencyjne.

Skanowanie sekwencyjne

Skanowanie sekwencyjne oznacza, że PostgreSQL czyta tabelę wiersz po wierszu.

Sprawdza każdy wiersz i pyta:

Czy ten wiersz spełnia warunek?

Dla małych tabel to jest w porządku.

Dla dużych tabel może być wolne.

Przykład:

SELECT *
FROM products
WHERE category = 'Electronics';

Bez indeksu na category, PostgreSQL może przeczytać całą tabelę.

To nie zawsze jest złe.

Czasem skanowanie sekwencyjne jest najlepszym wyborem.

Ale jeśli często szukasz po konkretnej kolumnie, indeks może pomóc.

Słowo kluczowe to często.

Nie twórz indeksów przypadkowo.

Indeksy to narzędzia.

Nie dekoracje.

Czym jest indeks?

Indeks to specjalna struktura bazy danych, która pomaga PostgreSQL szybciej znajdować wiersze.

Wyobraź sobie książkę.

Jeśli książka nie ma indeksu, musisz przewracać stronę po stronie.

Jeśli książka ma indeks, możesz znaleźć temat i przejść do odpowiedniej strony.

Indeks w bazie danych działa podobnie.

Bez indeksu:

Czytaj wiele wierszy i sprawdzaj je.

Z indeksem:

Użyj indeksu, żeby szybciej znaleźć pasujące wiersze.

Indeksy w PostgreSQL zwykle są przechowywane osobno od tabeli.

Tabela zawiera prawdziwe dane.

Indeks zawiera uporządkowane odwołania do tych danych.

Bardzo uproszczone.

Ale na początek wystarczy.

Jak GPS dla wierszy.

Bez irytującego głosu.

Sprawdzamy zapytanie przez EXPLAIN

PostgreSQL może pokazać, jak planuje wykonać zapytanie.

Użyj EXPLAIN.

EXPLAIN
SELECT *
FROM products
WHERE category = 'Electronics';

Możesz zobaczyć coś takiego:

Seq Scan on products
  Filter: ((category)::text = 'Electronics'::text)

Dokładny wynik może być inny.

Ale ważna część to:

Seq Scan

To oznacza, że PostgreSQL planuje skanować tabelę.

Będzie czytał wiersze i je filtrował.

To bardzo przydatna informacja.

PostgreSQL pokazuje ci swój plan.

Jak GPS bazy danych.

Tylko z mniejszą liczbą kolorów.

EXPLAIN ANALYZE

EXPLAIN pokazuje plan.

EXPLAIN ANALYZE naprawdę wykonuje zapytanie i pokazuje, co się stało.

Wykonaj:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Możesz zobaczyć:

Seq Scan on products
  Filter: ((category)::text = 'Electronics'::text)
  Rows Removed by Filter: ...
Planning Time: ...
Execution Time: ...

Dokładne liczby zależą od twojego komputera.

Ważne części to:

Seq Scan
Execution Time
Rows Removed by Filter

Rows Removed by Filter oznacza, że PostgreSQL sprawdził wiersze i odrzucił wiele z nich.

To jest praca.

Czasem konieczna.

Czasem możliwa do uniknięcia.

EXPLAIN ANALYZE jest bardzo przydatne.

Ale pamiętaj:

Ono naprawdę wykonuje zapytanie.

Dlatego uważaj z zapytaniami, które zmieniają dane.

Nie uruchamiaj go lekkomyślnie na niebezpiecznym DELETE.

PostgreSQL nie zapyta:

Czy jesteś emocjonalnie gotowy?

Po prostu wykona.

Tworzymy indeks

Teraz stwórz indeks na category:

CREATE INDEX idx_products_category
ON products(category);

Nazwa idx_products_category to po prostu nazwa.

Popularny styl nazewnictwa to:

idx_tabela_kolumna

Teraz wykonaj zapytanie ponownie:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Możesz zobaczyć, że używany jest indeks.

Na przykład:

Bitmap Index Scan
Bitmap Heap Scan

albo:

Index Scan

Dokładny plan zależy od PostgreSQL i twoich danych.

Główna idea:

PostgreSQL ma teraz jeszcze jedną opcję.

Może użyć indeksu.

Bez indeksu musiał czytać tabelę.

Z indeksem może szybciej znaleźć potrzebne wiersze.

Może.

Nie zawsze.

PostgreSQL wybiera to, co uważa za najlepsze.

Jest mądry.

Zazwyczaj.

Dlaczego PostgreSQL nadal może użyć skanowania sekwencyjnego

Czasem tworzysz indeks, a PostgreSQL nadal używa skanowania sekwencyjnego.

Nie panikuj.

To może być normalne.

Dlaczego?

Bo jeśli wiele wierszy spełnia warunek, przeczytanie całej tabeli może być szybsze.

Przykład:

SELECT *
FROM products
WHERE category = 'Electronics';

Jeśli dwadzieścia pięć procent tabeli to Electronics, PostgreSQL może zdecydować:

Pasuje dużo wierszy. Przeczytam tabelę.

Indeks jest najbardziej przydatny wtedy, gdy zapytanie zwraca małą część tabeli.

Na przykład:

SELECT *
FROM products
WHERE id = 50000;

To jest bardzo selektywne.

Jeden wiersz.

Idealne dla indeksu.

Szukanie produktu po id jest szybkie, bo id jest już indeksowane przez klucz główny.

Klucze główne automatycznie tworzą indeksy.

PostgreSQL zrobił to za ciebie.

Bardzo miło.

Bardzo profesjonalnie.

Indeks klucza głównego

Kiedy tworzysz to:

id SERIAL PRIMARY KEY

PostgreSQL automatycznie tworzy unikalny indeks dla id.

Dlatego to zapytanie jest szybkie:

SELECT *
FROM products
WHERE id = 50000;

Sprawdź:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE id = 50000;

Powinieneś zobaczyć coś w rodzaju:

Index Scan using products_pkey

products_pkey to indeks klucza głównego.

Nie stworzyłeś go ręcznie.

PostgreSQL stworzył go, ponieważ klucze główne muszą być unikalne i łatwe do wyszukiwania.

To jeden z powodów, dlaczego klucze główne są ważne.

To nie dekoracje.

To dowody osobiste bazy danych.

Indeks na price

Stwórzmy indeks na price.

CREATE INDEX idx_products_price
ON products(price);

Teraz wykonaj:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price = 50.00;

PostgreSQL może użyć indeksu.

To zależy od danych.

Teraz spróbuj:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price > 90.00;

Indeks może też pomagać przy wyszukiwaniu zakresów.

Przykłady:

WHERE price = 50.00
WHERE price > 90.00
WHERE price BETWEEN 10.00 AND 20.00

Indeksy są szczególnie przydatne, gdy szukasz, filtrujesz, sortujesz albo robisz JOIN po kolumnie.

Ale znowu:

Nie twórz indeksów na każdej kolumnie.

To nie optymalizacja.

To konfetti dla bazy danych.

A konfetti potem trudno sprzątać.

Indeks i ORDER BY

Indeksy mogą też pomagać przy sortowaniu.

Przykład:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY price ASC
LIMIT 10;

Ponieważ stworzyliśmy indeks na price, PostgreSQL może użyć go, żeby szybciej znaleźć najtańsze produkty.

To przydatne przy zapytaniach typu:

Pokaż najtańsze produkty.
Pokaż najdroższe produkty.
Pokaż najnowsze posty.
Pokaż ostatnie zamówienia.

Częsty przykład to indeks na created_at.

CREATE INDEX idx_products_created_at
ON products(created_at);

Wtedy to zapytanie może stać się szybsze:

SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 10;

To bardzo częste w prawdziwych aplikacjach.

Blogi.

Zamówienia.

Wiadomości.

Logi.

Wszyscy chcą ostatnich dziesięciu rzeczy.

Bo najwyraźniej ludzie kochają słowo “najnowsze”.

Indeks i JOIN

Indeksy mogą pomagać także przy JOIN.

Wyobraź sobie dwie tabele:

orders
customers

Zwykle:

orders.customer_id references customers.id

Kiedy robisz JOIN:

SELECT
  o.id,
  c.name,
  o.total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id;

PostgreSQL musi dopasować wiersze.

Indeksy na kolumnach używanych w JOIN mogą pomóc.

Klucze główne są indeksowane automatycznie.

Więc customers.id jest już indeksowane, jeśli jest kluczem głównym.

Ale klucze obce nie zawsze są indeksowane automatycznie.

To oznacza, że orders.customer_id może potrzebować indeksu, jeśli często robisz JOIN albo filtrujesz po tej kolumnie.

Przykład:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

To bardzo częsty indeks w prawdziwych projektach.

Kolumny kluczy obcych często są dobrymi kandydatami do indeksu.

Nie zawsze.

Ale często.

Odpowiedź numer jeden w bazach danych:

To zależy.

Denerwujące.

Ale prawdziwe.

Indeks unikalny

Indeks unikalny zabrania duplikatów wartości.

Przykład:

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  name VARCHAR(100) NOT NULL
);

Stwórz unikalny indeks:

CREATE UNIQUE INDEX idx_users_email_unique
ON users(email);

Dodaj użytkownika:

INSERT INTO users (email, name)
VALUES ('anna@example.com', 'Anna');

Spróbuj dodać innego użytkownika z tym samym emailem:

INSERT INTO users (email, name)
VALUES ('anna@example.com', 'Another Anna');

PostgreSQL to odrzuci.

Dobrze.

Email zwykle powinien być unikalny.

Indeks unikalny robi dwie rzeczy:

• pomaga szybciej wyszukiwać;
• chroni dane przed duplikatami.

Bardzo przydatne.

Bardzo odpowiedzialne.

Jak ochroniarz dla twojej tabeli.

Tylko bez ciemnych okularów.

UNIQUE constraint czy indeks unikalny?

Możesz też napisać:

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

To tworzy ograniczenie unikalności.

PostgreSQL również tworzy unikalny indeks za kulisami.

Dla początkującego prosta zasada:

Używaj UNIQUE w definicji tabeli, kiedy to jest reguła danych.

Na przykład:

Email musi być unikalny.
Username musi być unikalny.
Kod produktu musi być unikalny.

Używaj jawnych indeksów, kiedy chcesz poprawić szybkość wyszukiwania.

Prosta zasada:

Reguła danych: constraint.
Pomoc wydajnościowa: index.

Nieidealna.

Ale bardzo praktyczna.

Indeksy na kilka kolumn

Indeks może zawierać więcej niż jedną kolumnę.

Przykład:

CREATE INDEX idx_products_category_price
ON products(category, price);

To może pomóc zapytaniom typu:

SELECT *
FROM products
WHERE category = 'Electronics'
ORDER BY price ASC;

Indeks jest uporządkowany najpierw według category, potem według price.

Kolejność kolumn ma znaczenie.

Ten indeks jest przydatny dla:

WHERE category = 'Electronics'

oraz:

WHERE category = 'Electronics'
ORDER BY price ASC

Ale może być mniej przydatny dla:

WHERE price = 50.00

Dlaczego?

Bo price jest drugą kolumną w indeksie.

Pierwszą kolumną jest category.

Indeksy wielokolumnowe są potężne.

Ale trzeba je tworzyć na podstawie prawdziwych zapytań.

Nie na podstawie vibe’u.

Bazy danych nie optymalizują vibe’u.

Niestety.

Indeksy nie są darmowe

Indeksy przyspieszają niektóre odczyty.

Ale mają koszty.

Indeks:

• używa miejsca na dysku;
• musi być aktualizowany, kiedy dane się zmieniają;
• może spowalniać INSERT;
• może spowalniać UPDATE;
• może spowalniać DELETE;
• dodaje złożoność.

Kiedy wstawiasz wiersz, PostgreSQL musi wstawić dane do tabeli.

Ale musi też zaktualizować indeksy.

Jeśli tabela ma wiele indeksów, zapis staje się cięższy.

Dlatego nie twórz indeksów wszędzie.

Tabela z nadmiarem indeksów jest jak człowiek z dziesięcioma plecakami.

Może przygotowany.

Na pewno wolniejszy.

Kiedy tworzyć indeks?

Twórz indeks, kiedy:

• często szukasz po kolumnie;
• często filtrujesz po kolumnie przez WHERE;
• często sortujesz po kolumnie przez ORDER BY;
• często robisz JOIN przez kolumnę;
• tabela jest wystarczająco duża, żeby wydajność miała znaczenie;
• zapytanie zwraca małą część tabeli;
• EXPLAIN ANALYZE pokazuje wolne skanowanie.

Przykłady:

WHERE email = 'anna@example.com'

WHERE customer_id = 10

WHERE created_at >= '2026-01-01'

ORDER BY created_at DESC LIMIT 10

JOIN orders ON orders.customer_id = customers.id

To typowe miejsca, w których indeksy mogą pomóc.

Ale nie zgaduj na ślepo.

Zmierz.

Potem stwórz indeks.

Potem zmierz ponownie.

To dorosły sposób.

Trochę nudny.

Bardzo skuteczny.

Kiedy indeks może nie pomóc

Indeks może nie pomóc, kiedy:

• tabela jest bardzo mała;
• zapytanie zwraca prawie całą tabelę;
• kolumna ma bardzo mało różnych wartości;
• używasz kolumny w sposób, który uniemożliwia użycie indeksu;
• indeks nie pasuje do zapytania;
• dane bardzo często się zmieniają, a odczyty są rzadkie.

Przykład:

WHERE category = 'Electronics'

Jeśli istnieją tylko cztery kategorie i każda kategoria ma dużo wierszy, indeks może nie pomóc zbyt mocno.

Inny przykład:

WHERE quantity > 0

Jeśli prawie każdy produkt ma quantity > 0, PostgreSQL może wybrać skanowanie sekwencyjne.

Bo prawie wszystko pasuje.

Indeks jest przydatny, kiedy może uniknąć pracy.

Jeśli zapytanie potrzebuje prawie wszystkiego, nie ma zbyt wiele pracy do uniknięcia.

Smutne, ale logiczne.

Funkcje mogą ukrywać indeksy

Uważaj na funkcje w WHERE.

Załóżmy, że masz indeks na email.

CREATE INDEX idx_users_email
ON users(email);

To zapytanie może użyć indeksu:

SELECT *
FROM users
WHERE email = 'anna@example.com';

Ale to zapytanie może nie użyć zwykłego indeksu:

SELECT *
FROM users
WHERE LOWER(email) = 'anna@example.com';

Dlaczego?

Bo PostgreSQL nie szuka surowej wartości email.

Szuka wyniku LOWER(email).

To coś innego.

Dla takiego zapytania może być potrzebny indeks wyrażeniowy:

CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

Teraz PostgreSQL może użyć indeksu dla:

SELECT *
FROM users
WHERE LOWER(email) = 'anna@example.com';

To trochę bardziej zaawansowane.

Ale ważne.

Jeśli zapytanie przekształca kolumnę, zwykły indeks może nie pomóc.

Baza danych jest mądra.

Ale nie czyta w myślach.

Na szczęście.

LIKE i indeksy

Indeksy czasem mogą pomóc z LIKE.

Przykład:

SELECT *
FROM products
WHERE name LIKE 'Product 12%';

To szuka nazw zaczynających się od Product 12.

W niektórych sytuacjach może użyć indeksu.

Ale to zapytanie jest trudniejsze:

SELECT *
FROM products
WHERE name LIKE '%12';

Dlaczego?

Bo wzorzec zaczyna się od wildcard.

PostgreSQL nie może łatwo przeskoczyć do początku wartości.

Musi szukać więcej.

Prosta zasada:

LIKE 'abc%' może przyjaźnić się z indeksami.
LIKE '%abc' zwykle nie przyjaźni się z indeksami.
LIKE '%abc%' zwykle nie przyjaźni się z indeksami.

Istnieją specjalne typy indeksów do zaawansowanego wyszukiwania tekstowego.

Ale na razie zapamiętaj:

Szukanie od początku jest łatwiejsze.

Szukanie ze środka jest trudniejsze.

Jak znalezienie tytułu książki, kiedy pamiętasz tylko jedno słowo ze środka.

Powodzenia, dzielny bibliotekarzu.

Usuwanie indeksu

Czasem tworzysz indeks, a potem rozumiesz, że nie jest przydatny.

Możesz go usunąć.

Przykład:

DROP INDEX IF EXISTS idx_products_price;

To usuwa indeks.

Nie usuwa tabeli.

Nie usuwa danych.

Tylko indeks.

Ale w prawdziwych projektach bądź ostrożny.

Indeksy mogą być używane przez ważne zapytania.

Usunięcie złego indeksu może spowolnić wszystko.

A wtedy serwer zaczyna swoją dramatyczną operę.

Typowe błędy

Tworzenie indeksów na każdej kolumnie

Zły pomysł:

Każda kolumna dostaje indeks!

Nie.

Stop.

To nie choinka.

Indeksy mają koszty.

Twórz indeksy na podstawie prawdziwych zapytań.

Nieużywanie EXPLAIN ANALYZE

Zgadywanie to nie praca nad wydajnością.

Używaj:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Mierz przed i po.

Bez mierzenia uprawiasz astrologię baz danych.

Oczekiwanie, że indeksy naprawią złe zapytania

Indeksy pomagają.

Ale nie naprawiają wszystkiego.

Fatalne zapytanie może pozostać fatalne nawet z indeksem.

Jeśli wybierasz za dużo danych, źle robisz dużo JOIN, albo dziwnie filtrujesz, indeksy mogą cię nie uratować.

Indeksy to narzędzia.

Nie cuda.

Zapominanie, że indeksy spowalniają zapisy

Jeśli tabela dostaje wiele wstawek i aktualizacji, zbyt wiele indeksów może pogorszyć wydajność.

Odczyty mogą stać się szybsze.

Zapisy mogą stać się wolniejsze.

Zawsze jest kompromis.

Baza danych daje.

Baza danych zabiera.

Bardzo filozoficzne.

Ignorowanie indeksów na kluczach obcych

Klucze obce są ważne dla relacji.

Ale jeśli często robisz JOIN albo filtrujesz po kolumnie klucza obcego, pomyśl o indeksie.

Przykład:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

To częste w prawdziwych aplikacjach.

Nie zawsze konieczne.

Ale bardzo często przydatne.

Praktyka

Stwórz indeks na category:

CREATE INDEX idx_products_category
ON products(category);

Sprawdź zapytanie:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Electronics';

Stwórz indeks na price:

CREATE INDEX idx_products_price
ON products(price);

Sprawdź zapytanie po cenie:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE price BETWEEN 10.00 AND 20.00;

Sprawdź sortowanie:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY price ASC
LIMIT 10;

Stwórz indeks na created_at:

CREATE INDEX idx_products_created_at
ON products(created_at);

Sprawdź najnowsze produkty:

EXPLAIN ANALYZE
SELECT *
FROM products
ORDER BY created_at DESC
LIMIT 10;

Stwórz indeks na kilka kolumn:

CREATE INDEX idx_products_category_price
ON products(category, price);

Sprawdź to zapytanie:

EXPLAIN ANALYZE
SELECT *
FROM products
WHERE category = 'Office'
ORDER BY price ASC
LIMIT 10;

Uruchamiaj te zapytania.

Patrz na wynik.

Nie martw się, jeśli plan różni się od przykładów.

PostgreSQL wybiera plany na podstawie twoich danych, indeksów, ustawień i komputera.

Celem nie jest zapamiętanie każdego planu.

Celem jest zrozumienie, co PostgreSQL próbuje zrobić.

Na razie to wystarczy.

Mini wyzwanie

Utwórz dwie tabele:

customers
orders

Zasady:

• jeden klient może mieć wiele zamówień;
• każde zamówienie należy do jednego klienta;
• email klienta musi być unikalny;
• zamówienia mają wartość całkowitą;
• zamówienia mają datę utworzenia.

Utwórz tabele:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total NUMERIC(10, 2) CHECK (total >= 0),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Dodaj klientów:

INSERT INTO customers (email, name)
VALUES
  ('anna@example.com', 'Anna'),
  ('marco@example.com', 'Marco'),
  ('sofia@example.com', 'Sofia');

Dodaj zamówienia:

INSERT INTO orders (customer_id, total)
SELECT
  (number % 3) + 1,
  (number % 200) + 10
FROM generate_series(1, 50000) AS number;

Teraz stwórz przydatne indeksy:

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

CREATE INDEX idx_orders_total
ON orders(total);

Przetestuj te zapytania przez EXPLAIN ANALYZE:

EXPLAIN ANALYZE
SELECT *
FROM customers
WHERE email = 'anna@example.com';

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1;

EXPLAIN ANALYZE
SELECT *
FROM orders
ORDER BY created_at DESC
LIMIT 10;

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE total > 150.00;

Potem zapytaj siebie:

Które indeksy zostały użyte?
Które zapytania nadal używały skanowania sekwencyjnego?
Dlaczego?
Zapytanie zwróciło dużo wierszy czy mało wierszy?

Tak zaczyna się prawdziwa praca z wydajnością.

Nie zgadywaniem.

Obserwowaniem.

PostgreSQL zostawia wskazówki.

Trzeba tylko je przeczytać.

Jak detektyw.

Ale z większą liczbą średników.

Podsumowanie

Dzisiaj nauczyłeś się:

• zapytania mogą stawać się wolne, kiedy tabele rosną;
• skanowanie sekwencyjne czyta wiersze jeden po drugim;
• indeks pomaga PostgreSQL szybciej znajdować wiersze;
• klucze główne automatycznie tworzą indeksy;
• EXPLAIN pokazuje plan zapytania;
• EXPLAIN ANALYZE wykonuje zapytanie i pokazuje realne szczegóły wykonania;
• indeksy mogą pomagać z WHERE;
• indeksy mogą pomagać z ORDER BY;
• indeksy mogą pomagać z JOIN;
• indeksy unikalne zabraniają duplikatów;
• indeksy na kilka kolumn zależą od kolejności kolumn;
• indeksy używają miejsca na dysku;
• indeksy mogą spowalniać wstawianie, aktualizowanie i usuwanie;
• nie każda kolumna potrzebuje indeksu;
• wydajność trzeba mierzyć, a nie zgadywać.

To bardzo ważny krok.

Nie tylko piszesz już SQL.

Zaczynasz myśleć o tym, jak PostgreSQL wykonuje SQL.

To inny poziom.

Lepszy poziom.

Poziom, na którym baza danych nadal trochę przeraża.

Ale teraz masz narzędzia.

Następna lekcja

W następnej lekcji zbudujemy mały praktyczny projekt PostgreSQL.

Zaprojektujemy tabele.

Stworzymy relacje.

Dodamy dane.

Wykonamy zapytania.

Użyjemy JOIN.

Użyjemy funkcji agregujących.

I pomyślimy o indeksach.

Innymi słowy, połączymy części kursu w całość.

Jak składanie mebli.

Ale z mniejszą liczbą brakujących śrubek.

Mam nadzieję.