EXPLAIN ANALYZE w PostgreSQL – praktyczny przykład

Optymalizacja zapytań w PostgreSQL bez EXPLAIN ANALYZE jest bardzo trudna. Poniżej zaprezentuję jak czytać EXPLAIN ANALYZE na prawdziwym przykładzie przykładzie.. na co zwracać uwagę i jakie można wyciągnąć wnioski w praktyce.

Czym jest EXPLAIN ANALYZE?

Polecenie:

EXPLAIN ANALYZE
SELECT ...

robi dwie rzeczy jednocześnie:EXPLAIN – pokazuje plan wykonania, czyli jak PostgreSQL planuje wykonać zapytanieANALYZE – realnie wykonuje zapytanie i pokazuje:– rzeczywiste czasy– liczbę przetworzonych wierszy– ile razy wykonano dany krokDzięki temu widzimy różnicę między planem a rzeczywistością.

Przykładowa tabela:

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT,
    status TEXT,
    created_at TIMESTAMP,
    amount NUMERIC
);

Przykładowa Tabela zawiera kilka milionów rekordów.

Problemowe zapytanie:

Zamierzamy pobrać ostatnie zamówienia klienta:

SELECT *
FROM orders
WHERE customer_id = 126
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

OK, zapytanie wykonuje się …. ale bardzo wolno.

EXPLAIN ANALYZE – wynik

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 126
  AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 10;

Fragment wyniku:

Limit  (cost=120000.00..120000.02 rows=10 width=72)
  ->  Sort  (cost=120000.00..120250.00 rows=100000 width=72)
        Sort Key: created_at DESC
        Sort Method: quicksort  Memory: 25MB
        ->  Seq Scan on orders
              (cost=0.00..80000.00 rows=100000 width=72)
              Filter: ((customer_id = 126) AND (status = 'PAID'))
              Rows Removed by Filter: 4900000
Planning Time: 0.2 ms
Execution Time: 950 ms

Interpretacja wyniku:

1. Seq Scan – sygnał alarmowy

Seq Scan on orders
Rows Removed by Filter: 4900000

Powyższy wynik oznacza, że PostgreSQL:– przeskanował całą tabelę– odrzucił prawie 5 milionów wierszyTo prawie zawsze oznacza brak odpowiedniego indeksu!

2.Sortowanie dużej liczby danych

Sort Method: quicksort  Memory: 25MB

Silnik najpierw zbiera ogromną liczbę rekordów -> potem je sortuje -> dopiero na końcu stosuje LIMITAle LIMIT nie przyspiesza, jeśli wcześniej trzeba przetworzyć wszystko.

3. Rzeczywisty czas wykonania

Execution Time: 950 ms

Prawie sekunda na proste zapytanie – szczerze to raczej nieakceptowalne w systemie produkcyjnym.

4. Rozwiązanie – właściwy indeks

Dodajemy indeks dopasowany do zapytania:

CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Po wprowadzeniu poprawek:

Limit  (cost=0.43..8.45 rows=10 width=72)
  ->  Index Scan using idx_orders_customer_status_created on orders
        (cost=0.43..850.00 rows=1000 width=72)
        Index Cond: ((customer_id = 126) AND (status = 'PAID'))
Planning Time: 0.3 ms
Execution Time: 2.1 ms

Co się zmieniło?

1. Index Scan zamiast Seq ScanPostgreSQL:– używa indeksu– czyta tylko potrzebne rekordy

2. Brak sortowania:Kolejność created_at DESC jest już w indeksie.

3. Czas wykonania? Ponad 400× szybciej:

950 ms → 2 ms

Najczęstsze błędy przy optymalizacji:

1. Patrzenie tylko na cost, a ignorowanie actual time
2. Brak ANALYZE (sam EXPLAIN to za mało)
3. Niezwracanie uwagi na:– Rows Removed by Filter– różnice między rows a actual rows
4. Tworzenie nieprzemyślanych indeksów

Dokumentacja PostgreSQL

Szkolenia z zakresu PostreSQL