PostgreSQL może użyć indeksu dla id < 11 i skanowania sekwencyjnego dla id > 11, ponieważ pierwszy warunek pasuje do bardzo małej liczby wierszy, a drugi do niemal całej tabeli. Oba operatory obsługują indeksy B-tree; planer porównuje ich szacowane koszty.

Ten poradnik pomaga programiście pracującemu na Macu odtworzyć ten wybór na syntetycznych wierszach artykułów, odczytać plany i sprawdzić, czy różnica wymaga uwagi. Opiera się na pobranej, aktualnej dokumentacji PostgreSQL 18 oraz aktualnej dokumentacji node-postgres. Przed porównaniem szczegółów planu sprawdź wersję serwera; przykłady nie gwarantują identycznych planów w różnych wersjach ani na różnych komputerach. PostgreSQL: Using EXPLAIN

Dlaczego ten sam indeks może prowadzić do różnych wyborów skanowania?

Indeks B-tree obsługuje zarówno porównania mniejsze niż, jak i większe niż. Zmiana kierunku porównania nie powoduje, że indeks przestaje być użyteczny. Zmienia się zakres wartości, a więc również liczba wierszy, które według przewidywań planera trzeba pobrać. PostgreSQL: Index Types

Rozważmy przykładową tabelę zawierającą dokładnie 100 000 wierszy artykułów z kolejnymi całkowitymi identyfikatorami od 1 do 100 000:

Warunek Pasujące wiersze Udział w tabeli
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

Te liczby wynikają z syntetycznych danych wejściowych, a nie z pomiarów istniejącej bazy danych. Żaden z zakresów nie obejmuje identyfikatora 11. Indeks może sprawnie odnaleźć dziesięć pasujących identyfikatorów w pierwszym zakresie. W drugim zakresie wskazywałby i tak niemal każdy wiersz.

Zwykłe skanowanie indeksu w PostgreSQL pobiera wpisy z indeksu oraz odpowiadające im wiersze z głównego magazynu tabeli, zwanego stertą. Te wiersze mogą być rozproszone na wielu stronach. Gdy potrzebna jest niemal każda strona, bezpośredni odczyt tabeli może kosztować mniej niż przechodzenie przez indeks i pobieranie wskazanych przez niego wierszy. PostgreSQL: Index-Only Scans and Covering Indexes

Liczba 11 nie ma więc dla planera żadnego szczególnego znaczenia. Przy innych danych warunek większy niż mógłby wyznaczać wąski zakres. PostgreSQL szacuje odsetek pasujących wierszy, nazywany selektywnością, na podstawie statystyk tabeli. Sama wartość graniczna nie określa tego odsetka. PostgreSQL: Statistics Used by the Planner

Jak odtworzyć to porównanie na Macu?

Potrzebujesz istniejącego połączenia z PostgreSQL oraz polecenia psql dostępnego w aplikacji Terminal na Macu. Instalacja nie wchodzi w zakres tego ćwiczenia. Otwórz zwykle używane połączenie z bazą danych za pomocą psql; narzędzie przyjmuje ustawienia połączenia i udostępnia interaktywny wiersz poleceń SQL. Uruchom poniższe bloki w jednej nowej sesji. PostgreSQL: psql

Konfiguracja tworzy tabelę tymczasową zawierającą wyłącznie syntetyczny tekst artykułów. Tabele tymczasowe znikają po zakończeniu sesji. Zwykły klucz główny typu integer automatycznie tworzy unikatowy indeks B-tree, więc drugi indeks dla id jest zbędny. PostgreSQL: CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

Funkcja PostgreSQL generate_series dla liczb całkowitych domyślnie uwzględnia oba końce zakresu i używa kroku równego jeden. Zapytanie zliczające powinno więc zwrócić łącznie 100 000 wierszy, 10 poniżej 11 i 99 989 powyżej 11. PostgreSQL: Set Returning Functions

Po wczytaniu wierszy uruchom ANALYZE, aby planer uzyskał statystyki zapełnionej tabeli. To polecenie zbiera statystyki; pełni inną funkcję niż opcja pomiaru wykonania w EXPLAIN ANALYZE. PostgreSQL: ANALYZE

Następnie porównaj dwa zapytania pobierające całe wiersze:

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

Polecenia te wykonują każde zapytanie i wyświetlają jego plan oraz rzeczywistą liczbę wierszy, nie zwracając wszystkich wybranych wierszy artykułów. BUFFERS dodaje informacje o aktywności bloków; TIMING OFF wyłącza pomiar czasu poszczególnych węzłów. To przydatne do analizy wykonania, ale nie mierzy czasu potrzebnego na przesłanie wszystkich pasujących danych artykułów do aplikacji na komputerze. PostgreSQL: EXPLAIN

Przy zwykłych ustawieniach dla wąskiego zakresu spodziewaj się planu opartego na indeksie, a dla szerokiego zakresu skanowania sekwencyjnego. Traktuj je jako przewidywania: na podstawie swoich oszacowań PostgreSQL może wybrać plan bitmapowy albo inny odpowiedni plan. Kod SQL z tego przykładu został sprawdzony, ale podczas przygotowywania tekstu nie było dostępnego serwera PostgreSQL, by go uruchomić; obliczenia liczby wierszy sprawdzono osobno.

Które elementy planu wyjaśniają ten wybór?

Zacznij od węzła skanowania, jego szacunkowej liczby rows i rzeczywistej liczby rows. W przypadku pierwszego zapytania szukaj Index Scan z warunkiem Index Cond albo Bitmap Index Scan pod Bitmap Heap Scan. W przypadku drugiego szukaj Seq Scan z warunkiem Filter. Plan bitmapowy również korzysta z indeksu; porządkuje lokalizacje pasujących wierszy przed pobraniem stron sterty. PostgreSQL: Using EXPLAIN

Następnie sprawdź następujące kwestie:

Obserwacja w tym przykładzie Co sprawdzić
Szacunki są zbliżone do 10 i 99,989 rzeczywistych wierszy Różnica w liczbie pasujących wierszy wyjaśnia różne wybory.
Szacowana liczba wierszy znacznie różni się od rzeczywistej Odśwież statystyki, zanim zmienisz ustawienia planera.
Oba warunki korzystają ze skanowania sekwencyjnego na znacznie mniejszej próbce Rozmiar tabeli może sprawiać, że bezpośrednie skanowanie jest tańsze.
Pojawia się plan bitmapowy Jako inną alternatywę opartą na indeksie porównaj jego szacunki liczby wierszy i pracę wykonywaną na stercie.

Koszty planera są szacunkami w umownych jednostkach, a nie milisekundami. Analizuj je w ramach modelu PostgreSQL, zamiast interpretować koszt równy 100 jako czas trwania. Ponieważ przykład używa tabel tymczasowych, aktywność buforów jest widoczna w sekcji bloków lokalnych; bufory zwykłych tabel i indeksów pojawiają się w sekcji bloków współdzielonych. PostgreSQL: EXPLAIN

Jeśli szacunki wydają się zaskakujące, w tej samej sesji uruchom poniższe zapytanie diagnostyczne:

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

Histogram opisuje rozkład wartości używany do szacowania zakresu. Korelacja pokazuje, jak ściśle logiczny porządek wartości kolumny odpowiada fizycznemu porządkowi wierszy; wartości bliskie skrajności mogą sprawić, że dostęp do sterty przez indeks będzie wyglądał na mniej kosztowny. Żadne z tych pól nie wyznacza uniwersalnego odsetka, przy którym PostgreSQL musi zmienić sposób skanowania. PostgreSQL: pg_stats

Jak porównać zapytania przez node-postgres?

node-postgres wysyła tekst zapytania i wartości parametrów do PostgreSQL, gdzie odbywa się planowanie. Jego API zapytań przyjmuje SQL, a następnie tablicę wartości, więc w obu porównaniach można użyć tej samej wartości granicznej: 11. node-postgres: Queries

Aby odtworzyć przykład niezależnie w Node.js, użyj działającej instalacji Node.js/npm i ustawień istniejącego połączenia z PostgreSQL. W nowym katalogu projektu uruchom npm init -y, a następnie npm install pg. Zapisz cały poniższy program jako compare-scans.mjs i uruchom node compare-scans.mjs. Klient korzysta ze zmiennych środowiskowych PostgreSQL, takich jak PGHOST, PGPORT i PGDATABASE; użyj ustawień swojej istniejącej lokalnej bazy danych. node-postgres: Connecting

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

Program tworzy własną tabelę przykładową i wykonuje wszystkie operacje w ramach jednego połączenia. Wyświetla oba plany, a następnie zamyka połączenie. Składnię JavaScript sprawdzono za pomocą Node.js; podczas przygotowywania tekstu nie uruchomiono programu z bazą danych. Jeśli konfiguracja połączenia się nie powiedzie, rozwiąż ten problem, zanim zaczniesz interpretować zachowanie skanowania.

Czy nazwane instrukcje przygotowane mogą zmienić porównanie?

Dodanie name do konfiguracji zapytania node-postgres tworzy na każdym połączeniu nazwaną instrukcję przygotowaną. Wprowadza to kolejne rozróżnienie: PostgreSQL może użyć planu niestandardowego opartego na wartościach parametrów albo planu ogólnego współdzielonego przez wiele wykonań. node-postgres: Queries, PostgreSQL: PREPARE

Przy domyślnej automatycznej polityce PostgreSQL 18 pierwszych pięć wykonań korzysta z planów niestandardowych. Następnie PostgreSQL porównuje szacowany koszt planu ogólnego ze średnim kosztem planów niestandardowych; nie przełącza się automatycznie na plan ogólny przy szóstym wykonaniu. PostgreSQL: PREPARE

Jeśli nazwana instrukcja w aplikacji zachowuje się inaczej niż test z dosłownym zapytaniem SQL, sprawdź tę instrukcję na tym samym połączeniu za pomocą EXPLAIN EXECUTE. Plan ogólny wyświetla symbole parametrów, a plan niestandardowy podstawia przekazane wartości. Zachowaj oba operatory porównania w oddzielnych nazwanych instrukcjach, ponieważ ich tekst SQL jest różny. PostgreSQL: PREPARE

Kiedy należy zmienić zapytanie lub ustawienia planera?

Najpierw zachowaj rzeczywiste wymagania zadania. Zastąpienie SELECT * przez SELECT id zmienia zakres danych, które PostgreSQL musi pobrać. Jeśli wszystkie wymagane kolumny są dostępne w odpowiednim indeksie, możliwe staje się skanowanie wyłącznie indeksu. Uniknięcie pobierania danych ze sterty zależy też od mapy widoczności, więc plan skanowania wyłącznie indeksu nie gwarantuje całkowitego braku dostępu do sterty. PostgreSQL: Index-Only Scans and Covering Indexes

Jeśli zadanie wymaga wszystkich pasujących artykułów, zachowaj to wymaganie podczas porównywania planów. Jeśli potrzebny jest tylko niewielki, uporządkowany podzbiór, przetestuj rzeczywiste zapytanie ORDER BY i LIMIT: sortowanie i wcześniejsze zakończenie mogą zmienić preferowany plan. PostgreSQL: Using EXPLAIN

Po sprawdzeniu statystyk możesz porównać alternatywny plan w istniejącej sesji SQL:

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

To ustawienie zniechęca do skanowania sekwencyjnego, ale nie może całkowicie go wyłączyć. Wykorzystaj wynik do diagnostycznego porównania. PostgreSQL modeluje też sekwencyjny i niesekwencyjny dostęp do stron za pomocą seq_page_cost oraz random_page_cost. Zmiana ich wartości względnych może wpłynąć na wybór planu, ale dokumentacja zaleca ocenianie ustawień kosztów w odniesieniu do całego obciążenia zapytaniami, a nie kilku eksperymentów. PostgreSQL: Query Planning

W tej syntetycznej tabeli decydujące jest porównanie dziesięciu pasujących wierszy z 99 989. Sprawdź dokładne zapytanie, odśwież statystyki, porównaj liczbę szacowanych i rzeczywistych wierszy, a gdy to istotne, zbadaj zachowanie planów przygotowanych. Te kroki pozwalają ustalić, czy skanowanie sekwencyjne odpowiada wymaganej pracy, i dostarczają podstaw do dalszych zmian.

Źródła