Buforowanie indeksu PostgreSQL

Mam trudności ze znalezieniem „świeckich” wyjaśnień dotyczących sposobu buforowania indeksów w PostgreSQL, dlatego chciałbym sprawdzić rzeczywistość jednego lub wszystkich z tych założeń:

1. Indeksy PostgreSQL, takie jak wiersze, znajdują się na dysku, ale mogą być buforowane.
2. Indeks może znajdować się całkowicie w pamięci podręcznej lub wcale.
3. To, czy jest buforowane, zależy od tego, jak często jest używany (zgodnie z definicją w narzędziu do planowania zapytań).
4. Z tego powodu większość „rozsądnych” indeksów będzie cały czas w pamięci podręcznej.
5. Indeksy żyją w tej samej pamięci podręcznej ( buffer cache?) Co wiersze, dlatego przestrzeń pamięci podręcznej używana przez indeks nie jest dostępna dla wierszy.

Przed podjęciem tego chciałbym wyjaśnić, że zastosowanie częściowego indeksu daje dwie zalety:

1. Zmniejszamy rozmiar indeksu w pamięci podręcznej, zwalniając więcej miejsca na same wiersze w pamięci podręcznej.
2. Zmniejszamy rozmiar B-drzewa, co skutkuje szybszą odpowiedzią na zapytanie.

Grając trochę z pg_buffercache , mogę uzyskać odpowiedzi na niektóre z twoich pytań.

1. Jest to dość oczywiste, ale wyniki dla (5) pokazują również, że odpowiedź brzmi TAK
2. Mam jeszcze ustanowić dobry przykład tego, na razie jest to bardziej tak niż nie :) (Zobacz moją edycję poniżej, odpowiedź brzmi NIE ).
3. Ponieważ planista decyduje, czy użyć indeksu, czy nie, możemy powiedzieć TAK , decyduje o buforowaniu (ale jest to bardziej skomplikowane)
4. Dokładne szczegóły buforowania mogą pochodzić z kodu źródłowego, nie mogłem znaleźć zbyt wiele na ten temat, z wyjątkiem tego (zobacz też odpowiedź autora ). Jestem jednak całkiem pewien, że to znowu jest o wiele bardziej skomplikowane niż zwykłe tak lub nie. (Ponownie, z mojej edycji można uzyskać pewien pomysł - ponieważ rozmiar pamięci podręcznej jest ograniczony, te „rozsądne” indeksy konkurują o dostępną przestrzeń. Jeśli będzie ich zbyt wiele, wyrzucą się z pamięci podręcznej - więc odpowiedź jest raczej NIE . )
5. Jako proste zapytanie z pg_buffercacheprogramami odpowiedź jest ostateczna TAK . Warto zauważyć, że dane tabeli tymczasowej nie są tutaj buforowane.

EDYTOWAĆ

Znalazłem wspaniały artykuł Jeremiasza Peschki na temat przechowywania tabel i indeksów. Mając stamtąd informacje, mógłbym również odpowiedzieć (2) . Przygotowałem mały test, abyś mógł sam je sprawdzić.

-- we will need two extensions
CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pageinspect;


-- a very simple test table
CREATE TABLE index_cache_test (
      id serial
    , blah text
);


-- I am a bit megalomaniac here, but I will use this for other purposes as well
INSERT INTO index_cache_test
SELECT i, i::text || 'a'
FROM generate_series(1, 1000000) a(i);


-- let's create the index to be cached
CREATE INDEX idx_cache_test ON index_cache_test (id);


-- now we can have a look at what is cached
SELECT c.relname,count(*) AS buffers
FROM 
    pg_class c 
    INNER JOIN pg_buffercache b ON b.relfilenode = c.relfilenode 
    INNER JOIN pg_database d ON (b.reldatabase = d.oid AND d.datname = current_database())
GROUP BY c.relname
ORDER BY 2 DESC LIMIT 10;

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 pg_statistic_relid_att_inh_index |       4
 pg_operator_oprname_l_r_n_index  |       4
... (others are all pg_something, which are not interesting now)

-- this shows that the whole table is cached and our index is not in use yet

-- now we can check which row is where in our index
-- in the ctid column, the first number shows the page, so 
-- all rows starting with the same number are stored in the same page
SELECT * FROM bt_page_items('idx_cache_test', 1);

 itemoffset |  ctid   | itemlen | nulls | vars |          data
------------+---------+---------+-------+------+-------------------------
          1 | (1,164) |      16 | f     | f    | 6f 01 00 00 00 00 00 00
          2 | (0,1)   |      16 | f     | f    | 01 00 00 00 00 00 00 00
          3 | (0,2)   |      16 | f     | f    | 02 00 00 00 00 00 00 00
          4 | (0,3)   |      16 | f     | f    | 03 00 00 00 00 00 00 00
          5 | (0,4)   |      16 | f     | f    | 04 00 00 00 00 00 00 00
          6 | (0,5)   |      16 | f     | f    | 05 00 00 00 00 00 00 00
...
         64 | (0,63)  |      16 | f     | f    | 3f 00 00 00 00 00 00 00
         65 | (0,64)  |      16 | f     | f    | 40 00 00 00 00 00 00 00

-- with the information obtained, we can write a query which is supposed to
-- touch only a single page of the index
EXPLAIN (ANALYZE, BUFFERS) 
    SELECT id 
    FROM index_cache_test 
    WHERE id BETWEEN 10 AND 20 ORDER BY id
;

 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..8.54 rows=9 width=4) (actual time=0.031..0.042 rows=11 loops=1)
   Index Cond: ((id >= 10) AND (id <= 20))
   Buffers: shared hit=4
 Total runtime: 0.094 ms
(4 rows)

-- let's have a look at the cache again (the query remains the same as above)
             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2747
 idx_test_cache                   |       4
...

-- and compare it to a bigger index scan:
EXPLAIN (ANALYZE, BUFFERS) 
SELECT id 
    FROM index_cache_test 
    WHERE id <= 20000 ORDER BY id
;


 Index Scan using idx_test_cache on index_cache_test  (cost=0.00..666.43 rows=19490 width=4) (actual time=0.072..19.921 rows=20000 loops=1)
   Index Cond: (id <= 20000)
   Buffers: shared hit=4 read=162
 Total runtime: 24.967 ms
(4 rows)

-- this already shows that something was in the cache and further pages were read from disk
-- but to be sure, a final glance at cache contents:

             relname              | buffers
----------------------------------+---------
 index_cache_test                 |    2691
 idx_test_cache                   |      58

-- note that some of the table pages are disappeared
-- but, more importantly, a bigger part of our index is now cached

Podsumowując, pokazuje to, że indeksy i tabele mogą być buforowane strona po stronie, dlatego odpowiedź na (2) brzmi NIE .

I ostatni, aby zilustrować tymczasowe tabele niebuforowane tutaj:

CREATE TEMPORARY TABLE tmp_cache_test AS 
SELECT * FROM index_cache_test ORDER BY id FETCH FIRST 20000 ROWS ONLY;

EXPLAIN (ANALYZE, BUFFERS) SELECT id FROM tmp_cache_test ORDER BY id;

-- checking the buffer cache now shows no sign of the temp table

Strony indeksu są pobierane, gdy zapytanie zadecyduje, że przydadzą się one ograniczyć ilość danych w tabeli potrzebnych do odpowiedzi na zapytanie. Tylko bloki indeksu, do których nawigowano, zostały wczytane. Tak, przechodzą do tej samej puli buforów współdzielonych, w której przechowywane są dane tabeli. Oba są również wspierane przez pamięć podręczną systemu operacyjnego jako drugą warstwę buforowania.

Możesz łatwo mieć 0,1% indeksu w pamięci lub 100% tego. Pomysł, że większość „rozsądnych” indeksów będzie cały czas znajdowała się w pamięci podręcznej, upada mocno, gdy masz zapytania, które dotyczą tylko podzbioru tabeli. Typowym przykładem jest posiadanie danych zorientowanych czasowo. Często ci nawigują się po ostatnim końcu tabeli, rzadko oglądając starą historię. Tam możesz znaleźć wszystkie bloki indeksu potrzebne do nawigacji do ostatniego końca pamięci i wokół niego, podczas gdy bardzo niewiele potrzebowało nawigacji po wcześniejszych rekordach.

Skomplikowane części implementacji nie polegają na tym, jak bloki dostają się do bufora bufora. To zasady, kiedy wychodzą. Moje przemówienie w pamięci podręcznej bufora PostgreSQL i zawarte w nim przykładowe zapytania mogą pomóc ci zrozumieć, co się tam dzieje i zobaczyć, co naprawdę gromadzi się na serwerze produkcyjnym. To może być zaskakujące. W mojej książce o wysokiej wydajności PostgreSQL 9.0 jest o wiele więcej na wszystkie te tematy .

Częściowe indeksy mogą być pomocne, ponieważ zmniejszają rozmiar indeksu, a zatem są szybsze w nawigacji i pozostawiają więcej pamięci RAM do buforowania innych rzeczy. Jeśli nawigacja w indeksie jest taka, że ​​dotykane części są zawsze w pamięci RAM, to i tak może nie kupić prawdziwej poprawy.