Czy PostgreSQL może używać wartości null w swoich indeksach?

Czytałem tę książkę, która tak mówi

Baza danych zakłada, że ​​Indexed_Col IS NOT NULL obejmuje zbyt duży zakres, aby był użyteczny, więc baza danych nie będzie prowadzić do indeksu z tego warunku.

Rozumiem, że książka ma ponad 10 lat, ale okazała się już całkiem przydatna - używając instrukcji zebranych z jej stron przyspieszyłem zapytanie dziesięciokrotnie.

Ponadto, w prowadzeniu EXPLAIN ANALYZEna SELECTzapytania, znalazłem, że żaden z moich indeksy są używane, nawet kiedy przez wszystkich praw, powinny one być.

Zatem moje pytanie brzmi:

Przypuśćmy, że istnieje tabela z kolumną, której definicja kolumny zawiera „NOT NULL” i że istnieje indeks obejmujący tę kolumnę, czy ten indeks mógłby być użyty w zapytaniu tej tabeli, w której kolumny są częścią zapytania?

Lubić:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;

PostgreSQL z pewnością może używać indeksu dla IS NOT NULL. Nie widzę też żadnych założeń planisty zapytań dotyczących tego warunku.

Jeśli ułamek zerowy dla kolumny ( pg_statistic.stanullfrac) jest wystarczająco niski, aby sugerować, że indeks jest użyteczny selektywnie dla zapytania, PostgreSQL użyje indeksu.

Nie mogę zrozumieć, co próbujesz powiedzieć:

Jeśli jest to poprawne, czy rozumiem, że indeks w kolumnie zdefiniowanej jako „NOT NULL” nie może być używany w zapytaniu korzystającym z tej kolumny?

Z pewnością indeks nie zostanie użyty dla IS NOT NULLwarunku w NOT NULLkolumnie. Zawsze pasowałoby do 100% wierszy, więc seqscan prawie zawsze będzie znacznie szybszy.

PostgreSQL nie użyje indeksu, jeśli indeks nie odfiltruje dużej części wierszy dla zapytania. Jedynym prawdopodobnym wyjątkiem jest prośba o zestaw kolumn objętych jednym indeksem, w kolejności zgodnej z kolejnością indeksu. PostgreSQL może wtedy wykonać skanowanie tylko do indeksu. Np. Jeśli jest włączony indeks, t(a, b, c)a ty:

select a, b FROM t ORDER BY a, b, c;

PostgreSQL może korzystać z Twojego indeksu, nawet jeśli nie są odfiltrowywane żadne wiersze, ponieważ musi on tylko odczytać indeks i może pominąć czytanie sterty, unikać sortowania itp.

Oprócz dokładnej odpowiedzi Craiga chciałem dodać, że na okładce książki, do której się odwołujesz, napisano:

Obejmuje Oracle, DB2 i SQL Server

Dlatego nie ufałbym, że jest to świetne źródło porad w szczególności na temat PostgreSQL. Każdy RDBMS może być zaskakująco inny!

Jestem trochę zdezorientowany co do twojego pierwotnego pytania, ale oto przykład pokazujący, że ta część książki nie jest w 100% poprawna. Aby uniknąć dalszych nieporozumień, oto cały odpowiedni akapit, który można zobaczyć w Google Book Search .

Baza danych zakłada, że ​​Indexed_Col IS NOT NULL obejmuje zbyt duży zakres, aby był użyteczny, więc baza danych nie będzie prowadzić do indeksu z tego warunku. W rzadkich przypadkach posiadanie wartości różnej od wartości zerowej jest tak rzadkie, że korzystne jest skanowanie zakresu indeksu dla wszystkich możliwych wartości niepustych. W takich przypadkach, jeśli uda się ustalić bezpieczną dolną lub górną granicę zakresu wszystkich możliwych wartości, można włączyć skanowanie zakresu z warunkiem, takim jak kolumna_pozycyjna_dodatkowa> -1 lub kolumna_data> TO_DATE ('0001/01/01' , „RRRR / MM / DD”).

Postgres może faktycznie (w poniższym wymyślonym przypadku) użyć indeksu do zaspokojenia IS NOT NULLzapytań bez dodawania bloków skanowania zakresu, jak sugerowano Positive_ID_Column > -1. Zobacz komentarze do pytań Craiga, aby dowiedzieć się, dlaczego Postgres wybiera ten indeks w tym konkretnym przypadku, oraz uwagę na temat używania indeksów częściowych.

CREATE TABLE bar (a int);
INSERT INTO bar (a) SELECT NULL FROM generate_series(1,1000000);
INSERT INTO bar (a) VALUES (1);
CREATE INDEX bar_idx ON bar (a);

EXPLAIN ANALYZE SELECT * FROM bar WHERE a IS NOT NULL;
                                                QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Index Only Scan using bar_idx on bar  (cost=0.42..8.44 rows=1 width=4) (actual time=0.094..0.095 rows=1 loops=1)
   Index Cond: (a IS NOT NULL)
   Heap Fetches: 1
 Total runtime: 0.126 ms
(4 rows)

Nawiasem mówiąc, jest to Postgres 9.3, ale uważam, że wyniki byłyby w przybliżeniu podobne do wersji 9.1, chociaż nie użyłby „Skanowania samego indeksu”.

Edycja: Widzę, że wyjaśniłeś swoje pierwotne pytanie i najwyraźniej zastanawiasz się, dlaczego Postgres nie używa indeksu w prostym przykładzie, takim jak:

CREATE TABLE my_table(
a varchar NOT NULL
);

CREATE INDEX ix_my_table ON my_table(a);

SELECT a from my_table;

Prawdopodobnie dlatego, że nie masz żadnych wierszy w tabeli. Dodaj więc dane testowe i ANALYZE my_table;.

Nie opublikowałeś zapytania ani przykładowych danych. Ale najczęstsze przyczyny, dla których indeksy nie są używane, dotyczą objętości.

Indeksy są jak książka telefoniczna, która tłumaczy kolumnę na lokalizację wiersza. Jeśli szukasz tylko kilku wierszy, warto spojrzeć na każdy wiersz w książce telefonicznej, a następnie na wiersz w głównej tabeli.

Ale w przypadku kilku wierszy taniej jest pomijać książkę telefoniczną i powtarzać wszystkie wiersze w głównej tabeli. Z mojego doświadczenia wynika, że ​​punkt krytyczny wynosi około 100 rzędów.