Co jest pobierane z dysku podczas zapytania?

Dość proste pytanie, prawdopodobnie gdzieś na nie udzielono odpowiedzi, ale nie wydaje mi się, aby tworzyło właściwe pytanie wyszukiwania dla Google ...

Czy liczba kolumn w określonej tabeli wpływa na wydajność zapytania, gdy jest ono wysyłane do podzbioru tej tabeli?

Na przykład, jeśli tabela Foo ma 20 kolumn, ale moje zapytanie wybiera tylko 5 z tych kolumn, to czy posiadanie 20 (powiedzmy 10) kolumn wpływa na wydajność zapytania? Załóżmy dla uproszczenia, że ​​wszystko w klauzuli WHERE znajduje się w tych 5 kolumnach.

Niepokoi mnie użycie bufora Postgres oprócz bufora dysku systemu operacyjnego. Bardzo nie rozumiem projektu fizycznego magazynu Postgres. Tabele są przechowywane na kilku stronach (domyślnie rozmiar 8k na stronę), ale nie do końca rozumiem, w jaki sposób są rozmieszczone krotki. Czy PG jest wystarczająco inteligentny, aby pobierać z dysku tylko dane zawierające te 5 kolumn?

Fizyczne miejsce przechowywania wierszy jest opisane w dokumentacji w Układzie strony bazy danych . Zawartość kolumny dla tego samego wiersza jest przechowywana na tej samej stronie dysku, z godnym uwagi wyjątkiem treści edytowanej w TOAST (zbyt duża, aby zmieściła się na stronie). Zawartość jest wyodrębniana sekwencyjnie w każdym rzędzie, jak wyjaśniono:

Aby odczytać dane, należy kolejno zbadać każdy atrybut. Najpierw sprawdź, czy pole ma wartość NULL zgodnie z pustą bitmapą. Jeśli tak, przejdź do następnego. Następnie upewnij się, że masz odpowiednie wyrównanie. Jeśli pole jest polem o stałej szerokości, wszystkie bajty są po prostu umieszczane.

W najprostszym przypadku (bez kolumn TOAST), postgres pobierze cały wiersz, nawet jeśli potrzeba kilku kolumn. Tak więc w tym przypadku odpowiedź brzmi tak, ponieważ posiadanie większej liczby kolumn może mieć wyraźny niekorzystny wpływ na pamięć podręczną bufora niszczącego, szczególnie jeśli zawartość kolumny jest duża, a jednocześnie znajduje się poniżej progu TOAST.

Teraz przypadek TOAST: gdy pojedyncze pole przekracza ~ 2kB, silnik zapisuje zawartość pola w osobnej tabeli fizycznej. Wchodzi również w grę, gdy cały wiersz nie mieści się na stronie (domyślnie 8kB): niektóre pola są przenoszone do pamięci TOAST. Doc mówi:

Jeśli jest to pole o zmiennej długości (attlen = -1), to jest nieco bardziej skomplikowane. Wszystkie typy danych o zmiennej długości dzielą wspólną strukturę struktury nagłówka varlena, która obejmuje całkowitą długość przechowywanej wartości i niektóre bity flagi. W zależności od flag dane mogą być wbudowane lub w tabeli TOAST; może być również skompresowany

Treści TOAST nie są pobierane, gdy nie są jawnie potrzebne, więc ich wpływ na całkowitą liczbę stron do pobrania jest niewielki (kilka bajtów na kolumnę). To wyjaśnia wyniki w odpowiedzi @ dezso.

Jeśli chodzi o zapisy, każdy wiersz ze wszystkimi kolumnami jest całkowicie przepisywany przy każdej aktualizacji, bez względu na to, które kolumny są zmieniane. Posiadanie większej liczby kolumn jest oczywiście bardziej kosztowne dla operacji zapisu.

Odpowiedź Daniela koncentruje się na koszcie czytania poszczególnych wierszy. W tym kontekście: umieszczenie NOT NULLkolumn o stałym rozmiarze na pierwszym miejscu w tabeli trochę pomaga. Umieszczenie odpowiednich kolumn na pierwszym miejscu (tych, o które pytasz) trochę pomaga. Minimalizacja wypełniania (z powodu wyrównywania danych) poprzez granie w tetris z kolumnami może trochę pomóc. Ale najważniejszy efekt nie został jeszcze wspomniany, szczególnie w przypadku dużych stołów.

Dodatkowe kolumny oczywiście powodują, że wiersz zajmuje więcej miejsca na dysku, dzięki czemu mniej wierszy mieści się na jednej stronie danych (domyślnie 8 kB). Poszczególne wiersze są rozmieszczone na większej liczbie stron. Aparat bazy danych generalnie musi pobierać całe strony, a nie pojedyncze wiersze . Nie ma znaczenia, czy poszczególne wiersze są nieco mniejsze, czy większe - o ile trzeba odczytać tę samą liczbę stron.

Jeśli zapytanie pobiera (względnie) niewielką część dużej tabeli, w której wiersze są rozmieszczone mniej więcej losowo w całej tabeli, obsługiwane przez indeks, spowoduje to mniej więcej taką samą liczbę odczytów strony, przy niewielkim uwzględnieniu do wielkości wiersza. Nieistotne kolumny nie spowalniają cię znacznie w takim (rzadkim) przypadku.

Zazwyczaj pobierane są łaty lub klastry wierszy wprowadzonych po kolei lub w pobliżu i udostępniane są strony danych. Te wiersze są rozłożone z powodu bałaganu, aby przeczytać więcej stron dysku, aby spełnić twoje zapytanie. Konieczność przeczytania większej liczby stron jest zazwyczaj najważniejszym powodem spowolnienia zapytania. I to jest najważniejszy czynnik, dlaczego nieistotne kolumny spowalniają twoje zapytania.

W dużych bazach danych zazwyczaj nie ma wystarczającej ilości pamięci RAM, aby utrzymać ją w pamięci podręcznej. Większe wiersze zajmują więcej pamięci podręcznej, więcej rywalizacji, mniej trafień w pamięci podręcznej, więcej operacji we / wy dysku. Odczyty dysku są zwykle znacznie droższe. Mniej w przypadku dysków SSD, ale pozostaje znaczna różnica. To dodaje do powyższego punktu dotyczącego odczytów strony.

To może, ale nie ma znaczenia, jeśli są nieistotne kolumny TOAST-ed. Odpowiednie kolumny mogą być również edytowane TOAST, przywracając wiele tego samego efektu.

Mały test:

CREATE TABLE test2 (
    id serial PRIMARY KEY,
    num integer,
    short_text varchar(32),
    longer_text varchar(1000),
    long_long_text text
);

INSERT INTO test2 (num, short_text, longer_text, long_long_text)
SELECT i, lpad('', 32, 'abcdefeghji'), lpad('', 1000, 'abcdefeghji'), lpad('', (random() * 10000)::integer, 'abcdefeghji')
FROM generate_series(1, 10000) a(i);

ANALYZE test2;

SELECT * FROM test2;
[...]
Time: 1091.331 ms

SELECT num FROM test2;
[...]
Time: 21.310 ms

Ograniczenie zapytania do pierwszych 250 wierszy ( WHERE num <= 250) daje odpowiednio 34,539 ms i 8,433 ms. Wybranie wszystkich oprócz long_long_texttego ograniczonego zestawu powoduje 18,432 ms. To pokazuje, że Twoim zdaniem PG jest wystarczająco mądry.