Dlaczego indeksowałbyś text_pattern_ops w kolumnie tekstowej?

Dzisiaj Siedem baz danych w siedmiu tygodniach zapoznało mnie z indeksami poszczególnych operatorów.

Możesz indeksować ciągi znaków dla wzorca pasującego do poprzednich zapytań, tworząc text_pattern_opsindeks klasy operatora, o ile wartości są indeksowane małymi literami.

CREATE INDEX moves_title_pattern ON movies (
    (lower(title) text_pattern_ops);

Użyliśmy, text_pattern_opsponieważ tytuł jest typu tekstowego. Jeśli trzeba indeks varchars, znaków lub nazw, użyj pokrewnych ops: varchar_pattern_ops, bpchar_pattern_ops, i name_pattern_ops.

Uważam ten przykład za bardzo mylący. Dlaczego warto to zrobić?

Jeśli kolumna zawiera tekst, to czy inne typy (varchar, char, name) nie będą rzutowane na tekst przed użyciem jako wartości wyszukiwania?

Jak ten indeks zachowuje się inaczej niż indeks używający domyślnego operatora?

CREATE INDEX moves_title_pattern ON movies (lower(title));

Dokumentacja często daje odpowiedź na takie pytania. Podobnie jak w tym przypadku :

Klasy operatora text_pattern_ops, varchar_pattern_ops i bpchar_pattern_ops obsługują indeksy B-drzewa odpowiednio dla typów text, varchar i char. Różnica w stosunku do domyślnych klas operatorów polega na tym, że wartości są porównywane ściśle znak po znaku, a nie zgodnie z lokalnymi regułami zestawiania. To sprawia, że te klasy operatorów są odpowiednie do stosowania w zapytaniach zawierających wyrażenia pasujące do wzorca (wyrażenia regularne LIKE lub POSIX), gdy baza danych nie używa standardowych ustawień regionalnych „C”. Na przykład możesz zaindeksować kolumnę varchar w następujący sposób:

CREATE INDEX test_index ON test_table (col varchar_pattern_ops);

Pamiętaj, że powinieneś również utworzyć indeks z domyślną klasą operatora, jeśli chcesz, aby zapytania obejmujące zwykłe porównania <, <=,> lub> = korzystały z indeksu. Takie zapytania nie mogą wykorzystywać klas operatora xxx_pattern_ops . (Jednak zwykłe porównania równości mogą korzystać z tych klas operatorów.) Możliwe jest utworzenie wielu indeksów w tej samej kolumnie z różnymi klasami operatorów.

Dokumentacja mówi dalej:

Jeśli używasz ustawień regionalnych C, nie potrzebujesz klas operatora xxx_pattern_ops, ponieważ indeks z domyślną klasą operatora nadaje się do zapytań dopasowujących wzorce w ustawieniach regionalnych C.

Możesz sprawdzić swoje ustawienia regionalne w następujący sposób (prawdopodobnie jest to UTF8 zamiast „C”):

postgres=> show lc_collate;
 lc_collate
-------------
 en_GB.UTF-8