Wybór treści artykułu może wykluczyć skanowanie wyłącznie indeksu: PostgreSQL musi pobrać kolumnę, której indeks nie zawiera. Ten poradnik pokazuje, jak zdiagnozować tę zmianę na komputerze Mac i zmierzyć jej wpływ w Node.js. Wymagania dotyczące skanowania wyłącznie indeksu.

Dlaczego zmiana wybranych kolumn zmienia plan?

Rozważmy tabelę artykułów z kolumnami id, category i body oraz indeksem B-tree na (category, id). Zapytanie wybierające identyfikatory według kategorii może pobrać z tego indeksu zarówno wartość używaną do filtrowania, jak i wynik. Dodanie body sprawia, że indeks nie wystarcza do skanowania wyłącznie indeksu. Indeksy PostgreSQL są oddzielone od głównego magazynu tabeli, nazywanego stertą; zwykłe skanowanie indeksu pobiera również dane tabeli. Skanowanie wyłącznie indeksu i indeksy pokrywające.

Planista porównuje teraz sposoby pobrania wymaganych wierszy tabeli. Może zachować Index Scan, połączyć Bitmap Index Scan z Bitmap Heap Scan albo wybrać Seq Scan. Skanowanie bitmapowe grupuje dostęp do sterty według fizycznego położenia. Gdy pobieranych jest wiele rozproszonych wierszy, skanowanie tabeli może być tańsze niż pobieranie ich osobno. Plan bitmapowy nadal używa indeksu, mimo że jego najwyższy węzeł skanowania wspomina o stercie. Korzystanie z EXPLAIN.

Uważnie sprawdź też pierwotny plan. Index Only Scan może wykazywać niezerową wartość Heap Fetches: PostgreSQL czasami musi odwiedzić wiersze tabeli, aby ustalić ich widoczność. Unikanie tych odwiedzin zależy od mapy widoczności tabeli, a także od tego, czy indeks zawiera wymagane kolumny. Sprawdzanie widoczności przy skanowaniu wyłącznie indeksu.

Odtwórz porównanie na komputerze Mac

Użyj działającej lokalnej bazy danych PostgreSQL oraz zainstalowanej, obecnie wspieranej wersji Node.js z npm. Przykład podstawowy wykorzystuje funkcje dostępne w PostgreSQL 14–18; aktualna pobrana instrukcja PostgreSQL podaje, że dotyczy wersji 18. Dodatkowa diagnostyka serializacji poniżej wymaga PostgreSQL 17 lub nowszego. Dokumentacja node-postgres opisuje zgodność z wersjami Node.js i polecenie instalacji. Dokumentacja PostgreSQL, Instalacja i zgodność node-postgres.

Otwórz Terminal i przejdź do nowego folderu. Te polecenia utworzą projekt i zainstalują sterownik bazy danych:

mkdir article-plan-lab
cd article-plan-lab
npm init -y
npm install pg

Program korzysta z istniejących lokalnych ustawień połączenia przez PGHOST, PGPORT, PGDATABASE i PGUSER. Skonfiguruj je dla bazy danych, z którą możesz się już połączyć. node-postgres odczytuje te zmienne środowiskowe podczas tworzenia obiektu Client bez jawnych opcji połączenia. Łączenie za pomocą zmiennych środowiskowych.

Zapisz poniższy kompletny program jako article-plan.cjs. Tworzy on syntetyczne wiersze w tabeli tymczasowej, buduje indeks, wyświetla oba plany wykonania, a następnie mierzy czas obu zwykłych zapytań. W obu porównaniach używane są to samo połączenie i ta sama wartość kategorii. Konfiguracja zapytania przekazuje SQL przez text, a parametr przez values, zgodnie z udokumentowanym API node-postgres. Konfiguracja zapytania.

const { Client } = require('pg');
const { performance } = require('node:perf_hooks');

async function main() {
  const client = new Client();
  try {
    await client.connect();
    const version = await client.query('SHOW server_version');
    console.log(version.rows[0]);

    await client.query(`CREATE TEMP TABLE article_plan_demo (
      id integer PRIMARY KEY,
      category integer NOT NULL,
      body text NOT NULL
    )`);
    await client.query(`INSERT INTO article_plan_demo
      SELECT g, g % 10, repeat('Synthetic article paragraph. ', 40)
      FROM generate_series(1, 50000) AS s(g)`);
    await client.query(`CREATE INDEX article_plan_demo_category_id
      ON article_plan_demo (category, id)`);
    await client.query('VACUUM (ANALYZE) article_plan_demo');

    const queries = [
      ['IDs', 'SELECT id FROM article_plan_demo WHERE category = $1'],
      ['Bodies', 'SELECT id, body FROM article_plan_demo WHERE category = $1']
    ];

    for (const [label, text] of queries) {
      const plan = await client.query({
        text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE) ${text}`,
        values: [3]
      });
      console.log(label);
      console.log(plan.rows.map(row => row['QUERY PLAN']).join('\n'));

      const start = performance.now();
      const result = await client.query({ text, values: [3] });
      const elapsed = performance.now() - start;
      const bodyBytes = result.rows.reduce((sum, row) =>
        sum + (row.body === undefined ? 0 : Buffer.byteLength(row.body)), 0);
      console.log({
        rows: result.rows.length,
        elapsedMs: elapsed.toFixed(2),
        bodyBytes
      });
    }
  } finally {
    await client.end();
  }
}

main().catch(error => {
  console.error(error.message);
  process.exitCode = 1;
});

Uruchom program z tego samego folderu:

node article-plan.cjs

W konfiguracji polecenie VACUUM (ANALYZE) jest wysyłane osobno. Wykonuje ono próżniowanie, a następnie zbieranie statystyk; nie można go uruchomić wewnątrz bloku transakcji. Pozostaw je poza każdym opakowaniem transakcyjnym, które dodasz do przykładu. VACUUM.

Składnia JavaScriptu przeszła kontrolę, a osobne deterministyczne obliczenie potwierdziło liczbę wierszy i łączną liczbę bajtów treści w zestawie danych testowych. W środowisku badawczym PostgreSQL był niedostępny, dlatego zapytania do bazy zostały sprawdzone, ale nie wykonane. Żaden konkretny plan ani czas działania nie jest przedstawiony jako zaobserwowany wynik.

Jak analizować plany w przydatnej kolejności

Zacznij od nazw operacji skanowania, a następnie porównaj szacowaną i rzeczywistą liczbę wierszy. Potem sprawdź dostęp do tabeli i aktywność buforów. Taka kolejność pozwala odróżnić zmianę dostępnych metod dostępu od niedokładnych szacunków lub kosztownego wyniku. VERBOSE udostępnia również kolumny wyjściowe każdego węzła. Korzystanie z EXPLAIN, Opcje EXPLAIN.

Obserwacja Interpretacja Co sprawdzić dalej
Identyfikatory używają Index Only Scan; treści używają Index Scan Zapytanie o treść wymaga danych z tabeli Porównaj bufory i czas zwykłego zapytania
Treści używają Bitmap Heap Scan Sprawdź, czy podrzędny węzeł korzysta z indeksu Porównaj bloki sterty i zwróconą liczbę wierszy
Treści używają Seq Scan Planista preferuje skanowanie tabeli Porównaj szacowaną i rzeczywistą liczbę pasujących wierszy
Identyfikatory wykazują znaczną wartość Heap Fetches Skanowanie wyłącznie indeksu nadal odwiedza stertę Sprawdź, czy wynika to z niedawnych zmian w tabeli
Szacunki znacznie różnią się od rzeczywistej liczby wierszy Statystyki mogą niedokładnie opisywać dane Zbierz statystyki i ponów porównanie

ANALYZE zbiera statystyki wykorzystywane przez planistę do wyboru planów. Jeśli zmienisz rozkład syntetycznych kategorii, ponownie zbierz statystyki, zanim zinterpretujesz kolejne porównanie. Ze względu na próbkowanie szacunki mogą nieznacznie różnić się między uruchomieniami. ANALYZE.

Traktuj width jako szacowaną liczbę bajtów wyjściowych na wiersz, a nie pomiar przesłanych bajtów. Podobnie koszty planisty są wyrażone w jednostkach względnych, a nie w milisekundach. Trafienia w bufor oznaczają bloki znalezione w pamięci podręcznej PostgreSQL; nie oznaczają, że nie wykonano żadnej pracy. Szacunki planu, Raportowanie buforów.

W tym zestawie testowym oba zapytania powinny zwrócić 5 000 wierszy. Każda syntetyczna treść ma 1 160 bajtów ASCII, więc zapytanie o treść zwraca 5 800 000 bajtów treści, czyli około 5,53 MiB, bez identyfikatorów i narzutu protokołu. To obliczenie na podstawie danych wejściowych przykładu, a nie pomiar wydajności bazy danych. Pole bodyBytes w programie pozwala zweryfikować tę różnicę.

Zwykłe zapytanie jest wykonywane po pomiarze jego planu, więc jego czas odzwierciedla wykonanie w rozgrzanej kolejności. Przed wyciąganiem wniosków z niewielkich różnic w czasie ponów porównania i zmieniaj kolejność. Pojedyncze uruchomienie przydaje się do diagnozy, ale nie pozwala ustalić wiarygodnego stosunku wydajności.

Dlaczego pobieranie treści może trwać dłużej, niż sugeruje EXPLAIN?

Duże wartości tekstowe mogą być skompresowane lub przechowywane poza główną tabelą za pomocą mechanizmu TOAST PostgreSQL. Pobranie głównego wpisu wiersza i przekazanie klientowi pełnej treści może więc wymagać różnego nakładu pracy. Krótki, powtarzalny zestaw testowy powyżej ma pokazać pokrycie kolumn; nie reprezentuje wszystkich sposobów przechowywania treści. Przechowywanie TOAST.

Domyślnie EXPLAIN ANALYZE odrzuca wyniki zapytania bez ich konwersji na potrzeby przekazania. W PostgreSQL 17 i nowszych dodaj SERIALIZE TEXT, aby zbadać konwersję danych wyjściowych, w tym pobieranie wartości przechowywanych poza główną tabelą, gdy jest to potrzebne. Ta opcja pojawiła się w PostgreSQL 17. Serializacja w EXPLAIN, Informacje o wydaniu PostgreSQL 17.

Aby to wypróbować, zastąp istniejący wiersz szablonu EXPLAIN w programie poniższym fragmentem i ponownie uruchom cały program:

text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SERIALIZE TEXT) ${text}`,

W PostgreSQL 14–16 pozostaw oryginalny wiersz i użyj czasu zwykłego zapytania do zmierzenia odbierania wyniku. SERIALIZE nadal nie wysyła danych wynikowych do klienta, więc nie mierzy transmisji ani przetwarzania w JavaScript. Ograniczenia EXPLAIN.

node-postgres domyślnie gromadzi wiersze wynikowe w obiektach JavaScript. Opcja rowMode: 'array' zmienia reprezentację wyniku; nie usuwa wybranych wartości treści z wyniku SQL. Dlatego nie może przywrócić pokrycia kolumn utraconego po dodaniu body do tego zapytania. Tryb wierszy.

Wybierz zmianę pasującą do zadania z artykułami

Jeśli zadanie na komputerze wymaga tylko listy identyfikatorów, ogranicz liczbę wybieranych kolumn. Jeśli wymaga pełnego tekstu artykułów, oceń rzeczywisty czas działania zapytania o treści i rozmiar wyniku. Celem diagnozy jest wskazanie zbędnej pracy przy zachowaniu wymaganego wyniku.

Jeśli ekran wyświetla tylko pierwsze 50 artykułów w kategorii, ograniczenie żądanego wyniku jest sensowną alternatywą. W tablicy queries programu zastąp ciąg zapytania o treść poniższym SQL:

SELECT id, body
FROM article_plan_demo
WHERE category = $1
ORDER BY id
LIMIT 50

To celowo zmienia zadanie z pobierania wszystkich pasujących artykułów na pobieranie jednej uporządkowanej strony. Przy ustalonej kategorii istniejący indeks (category, id) stanowi możliwą ścieżkę dostępu w kolejności identyfikatorów. Odpowiedni indeks B-tree może usprawnić ORDER BY wraz z LIMIT, ponieważ wykonanie może zakończyć się po znalezieniu wystarczającej liczby wierszy. Wybrany plan nadal należy sprawdzić. Indeksy i ORDER BY.

Nie traktuj INCLUDE (body) jako automatycznej naprawy. Dołączane kolumny powielają dane tabeli, zwiększają rozmiar indeksu i mogą uniemożliwić wstawianie, jeśli krotka indeksu przekroczy limit rozmiaru. Małe kolumny z danymi mogą być dobrym przedmiotem eksperymentów, których wyniki się mierzy. INCLUDE wprowadzono w PostgreSQL 11; nie jest dostępne we wcześniejszych wersjach. Ograniczenia indeksów pokrywających, Informacje o wydaniu PostgreSQL 11.

Zakończ analizę trzema konkretnymi ustaleniami: czy wybór treści pozbawił zapytanie możliwości skanowania wyłącznie indeksu, czy nowe skanowanie działa wystarczająco dobrze oraz ile czasu zajmuje dostarczenie pełnego wyniku do Node.js. Te ustalenia podpowiedzą, czy zachować plan, żądać mniejszej liczby artykułów, czy przetestować inny indeks dla rzeczywistego zapytania.

Źródła