Wyszukiwanie pełnotekstowe Postgres z wieloma kolumnami, dlaczego konkatowanie w indeksie, a nie w czasie wykonywania?

W ciągu ostatnich kilku dni natknąłem się na wyszukiwanie pełnotekstowe w postgresie i jestem trochę zdezorientowany co do indeksowania podczas przeszukiwania wielu kolumn.

Postgres'a docs mówić o tworzeniu ts_vectorindeksu łączonych kolumn, tak jak poniżej:

CREATE INDEX pgweb_idx ON pgweb 
    USING gin(to_tsvector('english', title || ' ' || body));

które mogę wyszukać tak:

... WHERE 
      (to_tsvector('english', title||' '||body) @@ to_tsquery('english', 'foo'))

Jednak gdybym chciał czasami wyszukać tylko tytuł, czasem tylko ciało, a czasem oba, potrzebowałbym 3 osobnych indeksów. A jeśli dodam w trzeciej kolumnie, może to być 6 indeksów i tak dalej.

Alternatywą, której nie widziałem w dokumentacji, jest po prostu oddzielne zaindeksowanie dwóch kolumn, a następnie użycie zwykłego WHERE...ORzapytania:

... WHERE
      (to_tsvector('english', title) @@ to_tsquery('english','foo'))
    OR
      (to_tsvector('english', body) @@ to_tsquery('english','foo'))

Benchmarking dwóch w ~ 1 milionach rzędów wydaje się zasadniczo nie mieć różnicy w wydajności.

Więc moje pytanie brzmi:

Dlaczego miałbym chcieć łączyć takie indeksy, a nie tylko pojedynczo indeksować kolumny? Jakie są zalety / wady obu?

Domyślam się, że gdybym wiedział wcześniej, chciałbym tylko przeszukiwać obie kolumny (nigdy pojedynczo), potrzebowałbym tylko jednego indeksu, łącząc te, które zużywają mniej pamięci.

Nie, nie potrzebujesz osobnych indeksów. Użyj funkcji wag. To tylko etykieta, o którą możesz zapytać. Możesz utworzyć maksymalnie cztery etykiety do zapytania (AD).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

Możesz połączyć tsvektory, aby osobno zastosować do nich wagi, a następnie złożyć je razem:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

W rzeczywistości alternatywą byłoby użycie gdzie z OR , a nie AND .

Jeśli masz indeks na tsvector (body + tytuł) i szukasz w nim, wyszukiwane słowa mogą znajdować się w tytule LUB w treści.

Ponadto - podczas testowania upewnij się, że masz rozsądną liczbę wierszy w tabeli.

Najprostszy przypadek, który powinien wykazać dobrą różnicę: znajdź dwa słowa - jedno z nich, które najprawdopodobniej będzie w tytule. a druga - jest bardzo prawdopodobne, że będzie w ciele. Ale upewnij się, że nie ma wielu wierszy spełniających oba kryteria. Na przykład - możesz mieć 30% słowa „depesz” w ciele. Masz również ~ 30% szansy na tytuł „mysql”. Ale posiadanie „depesz i mysql” w dowolnym polu w tym samym wierszu jest bardzo mało prawdopodobne. A następnie sprawdź wydajność za pomocą takich indeksów.