Porównaj plany PostgreSQL, zestawiając powtarzane czasy wykonania z licznikami buforów w węźle głównym. W tym przewodniku po Terminalu na Macu użyto syntetycznych wierszy artykułów, aby sprawdzić, czy indeks zmniejsza obciążenie bazy danych, z uwzględnieniem wpływu pamięci podręcznej, który może sprawić, że pojedynczy przebieg będzie mylący.

Co należy mierzyć łącznie?

Twoim zadaniem jest porównanie tego samego zapytania o listę artykułów przed dodaniem indeksu i po nim. Nie zmieniaj wybieranych kolumn, kategorii, sortowania, limitu ani danych. Zapisz zarówno upływający czas wykonania, jak i aktywność buforów potrzebną do uzyskania wyniku.

Użyj EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON). Opcja ANALYZE wykonuje zapytanie; TIMING OFF wyłącza pomiary czasu poszczególnych węzłów, zachowując czas wykonania całej instrukcji. Format czytelny maszynowo ułatwia zbieranie powtarzanych pomiarów. PostgreSQL: EXPLAIN

Odczytuj te pomiary łącznie:

Pomiar Co pokazuje
Execution Time Czas wykonania po stronie serwera, bez planowania
Planning Time Czas poświęcony na wygenerowanie i optymalizację planu
Shared Hit Blocks Dostępy do buforów obsłużone z pamięci podręcznej współdzielonej przez PostgreSQL
Shared Read Blocks Bloki wczytane do buforów współdzielonych
Współdzielone trafienia plus odczyty Przydatne porównanie liczby dostępów do buforów współdzielonych
Temp Read Blocks i Temp Written Blocks Aktywność tymczasowych plików roboczych, na przykład sortowanie wykraczające poza pamięć

Bloki współdzielone należą do zwykłych tabel i indeksów. Bloki lokalne należą do tabel i indeksów tymczasowych; bloki tymczasowych plików roboczych stanowią osobną kategorię. Węzeł nadrzędny uwzględnia aktywność buforów swoich węzłów potomnych, dlatego do odczytu łącznych wartości wykonania używaj węzła głównego, a węzły potomne sprawdzaj, by zlokalizować wykonywaną pracę. Nigdy nie sumuj liczników ze wszystkich węzłów. PostgreSQL: EXPLAIN

Liczby buforów oznaczają dostępy, które nie są unikatowe: ponowne odwołanie do bloku może zostać policzone ponownie. Koszty planera są też wyrażone w umownych jednostkach, a nie w milisekundach. Porównuj rzeczywiste czasy wykonania z rzeczywistymi liczbami buforów; szacowane koszty traktuj jako wskazówkę przy analizie wyboru planera. PostgreSQL: Using EXPLAIN

Odczyt współdzielony nie dowodzi, że urządzenie pamięci masowej Maca wykonało fizyczny odczyt. Statystyki wejścia-wyjścia PostgreSQL nie pozwalają odróżnić danych pobranych z pamięci masowej od danych dostępnych już w pamięci podręcznej stron systemu operacyjnego. Opisuj pomiar jako odczyty z buforów współdzielonych, chyba że dysponujesz osobnymi danymi o pamięci masowej. PostgreSQL: The Cumulative Statistics System

Przygotuj eksperyment na Macu i sprawdź różnice między wersjami

Zacznij od uruchomionej lokalnej bazy PostgreSQL oraz Node.js z dostępnym npm w aplikacji Terminal. Ten przewodnik dotyczy PostgreSQL 18 i używa Node.js 22 lub 24. Dokumentacja node-postgres wymienia te wersje Node.js jako zgodne i opisuje instalację poleceniem npm install pg. node-postgres

Polecenie porównujące występuje też w dokumentacji PostgreSQL 16 i 17. PostgreSQL 18 automatycznie uwzględnia dane o buforach w EXPLAIN ANALYZE; jawne podanie BUFFERS pozwala jasno określić zamiar w tych wersjach. Nie zakładaj, że nowsze opcjonalne ustawienia są dostępne na starszych serwerach. PostgreSQL 16: EXPLAIN, PostgreSQL 17: EXPLAIN, informacje o wydaniu PostgreSQL 18

W nowym katalogu roboczym zainstaluj sterownik:

mkdir pg-plan-comparison
cd pg-plan-comparison
npm init -y
npm install pg

Użyj istniejącej lokalnej bazy testowej, którą możesz otworzyć za pomocą zwykłego połączenia PostgreSQL. Skrypt używa new Client(), który odczytuje te same zmienne środowiskowe połączenia co psql. Jeśli na przykład baza ma nazwę plan_lab, uruchom skrypt poleceniem PGDATABASE=plan_lab. W razie potrzeby dodaj istniejące ustawienia PGHOST lub PGPORT. node-postgres: Connecting

Program tworzy 100 000 syntetycznych artykułów w ramach transakcji, porównuje dwa stany schematu, a następnie wycofuje transakcję. Używa zwykłej tabeli, aby skan był widoczny w buforach współdzielonych. Dokumentacja PostgreSQL wyjaśnia, że ROLLBACK odrzuca aktualizacje wykonane w transakcji. Jeśli tabela demonstracyjna o tej nazwie już istnieje, jej tworzenie zakończy się błędem zamiast jej zastąpienia. PostgreSQL: ROLLBACK

Wykonaj pełne porównanie przed dodaniem indeksu i po nim

Zapisz poniższy kompletny program jako compare-plans.mjs. Generuje on własne dane wejściowe, wykonuje jeden nieraportowany przebieg rozgrzewający w każdej fazie, zachowuje siedem mierzonych przebiegów i wyświetla pierwszy mierzony plan z każdej fazy. Generator wierszy używa udokumentowanej przez PostgreSQL funkcji generate_series. PostgreSQL: Set Returning Functions

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const query = `
  SELECT article_id, title, published_at
  FROM plan_compare_articles_demo
  WHERE category_id = $1
  ORDER BY published_at DESC, article_id DESC
  LIMIT $2
`;
const values = [7, 50];
const explain = `
  EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
  ${query}
`;
const median = values =>
  [...values].sort((a, b) => a - b)[Math.floor(values.length / 2)];

async function measure(label) {
  await client.query(explain, values);
  const runs = [];
  let firstPlan;
  for (let run = 1; run <= 7; run++) {
    const result = await client.query(explain, values);
    const document = result.rows[0]['QUERY PLAN'][0];
    const root = document.Plan;
    firstPlan ??= document;
    const hits = root['Shared Hit Blocks'] ?? 0;
    const reads = root['Shared Read Blocks'] ?? 0;
    runs.push({
      phase: label,
      run,
      execution_ms: document['Execution Time'],
      planning_ms: document['Planning Time'],
      shared_hit: hits,
      shared_read: reads,
      shared_accesses: hits + reads,
      temp_read: root['Temp Read Blocks'] ?? 0,
      temp_written: root['Temp Written Blocks'] ?? 0
    });
  }
  console.table(runs);
  console.log(label, 'median execution ms:',
    median(runs.map(run => run.execution_ms)));
  console.dir(firstPlan, { depth: null });
}

try {
  await client.connect();
  console.log((await client.query('SHOW server_version')).rows[0]);
  await client.query('BEGIN');
  await client.query(`
    CREATE TABLE plan_compare_articles_demo (
      article_id integer PRIMARY KEY,
      category_id integer NOT NULL,
      title text NOT NULL,
      published_at timestamp NOT NULL,
      body text NOT NULL
    )
  `);
  await client.query(`
    INSERT INTO plan_compare_articles_demo
      (article_id, category_id, title, published_at, body)
    SELECT g, g % 20, 'Article ' || g,
           timestamp '2025-01-01' + g * interval '1 minute',
           repeat('Synthetic article text. ', 12)
    FROM generate_series(1, 100000) AS series(g)
  `);
  await client.query('ANALYZE plan_compare_articles_demo');
  const before = await client.query(query, values);
  await measure('before index');

  await client.query(`
    CREATE INDEX plan_compare_articles_demo_list_idx
    ON plan_compare_articles_demo
      (category_id, published_at DESC, article_id DESC)
  `);
  const after = await client.query(query, values);
  if (JSON.stringify(before.rows) !== JSON.stringify(after.rows)) {
    throw new Error('Result mismatch between schema states');
  }
  await measure('after index');
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  try {
    await client.query('ROLLBACK');
  } finally {
    await client.end();
  }
}

Uruchom program z tego samego katalogu, podając rzeczywistą nazwę swojej bazy testowej:

PGDATABASE=plan_lab node compare-plans.mjs

Program przesyła zapytania SQL i wartości parametrów osobno przez client.query(text, values). Odczytuje plan z kolumny wynikowej o nazwie QUERY PLAN; node-postgres mapuje kolumny wyników na właściwości obiektu i analizuje JSON z PostgreSQL do obiektów JavaScript. node-postgres: Queries, node-postgres: pg.Result, node-postgres: Data Types

Samodzielne polecenie ANALYZE zbiera statystyki tabeli dla planera. Różni się od opcji ANALYZE służącej do mierzenia wykonania, używanej wewnątrz EXPLAIN. Zebranie statystyk po załadowaniu syntetycznych wierszy sprawia, że obie fazy zaczynają z tymi samymi informacjami o tabeli. PostgreSQL: ANALYZE

Obliczenia mediany i sum buforów w JavaScript sprawdzono na deterministycznych danych syntetycznych. Pełnego eksperymentu z bazą danych nie wykonano podczas przygotowania, ponieważ w środowisku badawczym nie było PostgreSQL; artykuł nie przedstawia zmierzonych wyników testów wydajnościowych.

Co powinno się zmienić w obu planach?

Proponowany indeks umieszcza kategorię filtrowaną przez równość przed żądanymi kolumnami sortowania. Dokumentacja PostgreSQL wyjaśnia, że warunki równości na wiodących kolumnach indeksu B-tree ograniczają skanowany zakres indeksu. Dzięki temu indeks ten jest uzasadnionym kandydatem dla tego zapytania. PostgreSQL: Multicolumn Indexes

Przed indeksowaniem spodziewaj się skanowania i sortowania, a po nim — skanowania indeksu w kolejności pod limitem. To oczekiwania, które należy sprawdzić w wynikach, a nie gwarantowany kształt planu. Dopasowany indeks B-tree może bezpośrednio dostarczać wiersze w żądanej kolejności, a połączenie sortowania z limitem może pozwolić pominąć przetwarzanie pozostałych pasujących wierszy. PostgreSQL: Indexes and ORDER BY

Sprawdź trzy rzeczy w wyświetlonych planach:

  1. Potwierdź, że węzeł główny zwraca 50 wierszy w obu fazach i że program nie zgłasza rozbieżności wyników.
  2. Znajdź węzły odpowiedzialne za skanowanie i sortowanie. Porównaj szacunki liczby wierszy z rzeczywistą liczbą wierszy i sprawdź, gdzie występuje aktywność buforów.
  3. Porównaj dostępy współdzielone w węźle głównym oraz powtarzane czasy wykonania. Sama zmiana nazw węzłów nie dowodzi użytecznej poprawy.

To porównanie przebiega etapami przy rozgrzanej pamięci podręcznej. Wstawianie wierszy, zapytania sprawdzające poprawność, przebiegi rozgrzewające i tworzenie indeksu wpływają na stan pamięci podręcznej. W obu fazach zachowaj takie samo obciążenie Maca i konfigurację bazy danych. Zachowaj wszystkie siedem pojedynczych pomiarów, by nietypowo wolny przebieg pozostał widoczny; sama mediana może ukrywać zmienność.

Aby przeprowadzić dokładniejszą analizę, powtórz cały eksperyment i przetestuj inne kategorie oraz limity. Jeśli sprawdzasz większy limit, opisz go jako osobne obciążenie. Pierwotne porównanie odpowiada na pytanie, czy ten indeks pomaga przy konkretnym żądaniu 50 artykułów w zaobserwowanych lokalnych warunkach.

Jak odróżnić mniejszy nakład pracy od cieplejszych buforów?

Poniższe liczby to przykładowe dane wejściowe, a nie zarejestrowane wyniki. Pokazują, dlaczego należy łącznie analizować odczyty, trafienia i czas:

Przykładowy przebieg Wykonanie Trafienia współdzielone Odczyty współdzielone Trafienia + odczyty
A, początkowy pomiar 12 ms 900 100 1 000
A, powtórzony pomiar 10 ms 1 000 0 1 000
B, plan kandydujący 3 ms 80 0 80

Między dwoma pomiarami A odczyty znikają, a łączna liczba dostępów współdzielonych pozostaje równa 1 000. Jest to zgodne ze zmianą stanu pamięci podręcznej, ale nie dowodzi zmniejszenia liczby dostępów do buforów współdzielonych. B łączy krótszy czas z mniejszą liczbą dostępów. Jeśli ten wzorzec powtarza się przy równoważnych wynikach, uzasadnia węższy wniosek, że B wykonuje mniej pracy na buforach współdzielonych dla tego zadania.

Skorzystaj z poniższej tabeli, jeśli własne wyniki wyglądają inaczej:

Zaobserwowany wzorzec Interpretacja i następne działanie
Krótszy czas i mniej dostępów współdzielonych Dowód poprawy dla testowanego obciążenia; powtórz pomiar, by sprawdzić spójność
Krótszy czas, mniej odczytów, podobna łączna liczba dostępów Zmiana może wynikać ze stanu pamięci podręcznej; porównaj powtórzenia po rozgrzaniu
Mniej dostępów, ale podobny lub dłuższy czas Przed wyborem sprawdź sortowanie, wyrażenia i zmienność pomiarów czasu
Zero odczytów, ale wiele trafień Wykonanie z danych w pamięci podręcznej nadal wymaga dostępów do buforów; sprawdź węzły o dużej aktywności
Pojawiają się odczyty lub zapisy tymczasowe Zanim zmienisz ustawienia, sprawdź operację roboczą, która je spowodowała

Nie uznawaj odsetka trafień w pamięci podręcznej za najważniejszy wskaźnik. W przykładzie zarówno rozgrzane A, jak i B osiągają 100% trafień w buforach współdzielonych, a mimo to liczby dostępów znacznie się różnią. Obok czasów zachowaj surowe liczniki i rozmiar wyniku.

Jaki jest związek tych pomiarów z szybkością aplikacji?

Execution Time po stronie serwera nie obejmuje planowania, a zwykłe EXPLAIN ANALYZE pomija również przesyłanie wyników oraz domyślnie konwersję danych wyjściowych. Sama instrumentacja wprowadza dodatkowy narzut. Dlatego skrypt porównuje instrumentowane wykonanie zapytania w bazie, a nie mierzy całkowitego czasu potrzebnego na wyświetlenie listy artykułów. PostgreSQL: Using EXPLAIN

Aby osobno zbadać konwersję danych wyjściowych w PostgreSQL 17 lub 18, dodaj SERIALIZE TEXT do tych samych opcji wyjaśniania. PostgreSQL 16 nie wymienia tej opcji. Serializacja nadal nie przesyła wierszy wyniku zapytania, więc nie mierzy dostarczania danych przez sieć. Zachowaj identyczne ustawienia serializacji w porównywanych przebiegach. PostgreSQL 17: EXPLAIN, PostgreSQL 16: EXPLAIN

Aby zmierzyć działanie aplikacji, mierz zwykłe zapytanie o artykuły SELECT aż do zakończenia wywołania sterownika z await, przy wcześniej nawiązanym połączeniu. Zapisz ten pomiar osobno jako czas upływający po stronie klienta. Zachowaj rzeczywistą konfigurację zapytania w aplikacji: dodanie właściwości name do zapytania node-postgres włącza zachowanie przygotowanych instrukcji dla połączenia i zmienia warunki porównania. node-postgres: Queries

Wybierz kandydujący indeks, gdy równoważne wyniki, powtarzane czasy i liczby buforów tworzą spójny obraz dla obsługiwanego żądania artykułów. Zachowaj pełne plany, pojedyncze pomiary, wersję serwera i parametry zapytania, aby wniosek pozostał konkretny i możliwy do odtworzenia.

Źródła