Jak utworzyć indeks, aby przyspieszyć zagregowane zapytanie LIKE dla wyrażenia?

Mogę zadawać złe pytanie w tytule. Oto fakty:

Pracownicy działu obsługi klienta narzekają na długi czas reakcji podczas wyszukiwania klientów w interfejsie administracyjnym naszej witryny opartej na Django.

Używamy Postgres 8.4.6. Zacząłem rejestrować powolne zapytania i odkryłem tego winowajcę:

SELECT COUNT(*) FROM "auth_user" WHERE UPPER("auth_user"."email"::text) LIKE UPPER(E'%deyk%')

Uruchomienie tego zapytania zajmuje ponad 32 sekundy. Oto plan zapytań dostarczony przez EXPLAIN:

QUERY PLAN
Aggregate  (cost=205171.71..205171.72 rows=1 width=0)
  ->  Seq Scan on auth_user  (cost=0.00..205166.46 rows=2096 width=0)
        Filter: (upper((email)::text) ~~ '%DEYK%'::text)

Ponieważ jest to zapytanie wygenerowane przez Django ORM z zestawu Django QuerySet wygenerowanego przez aplikację administracyjną Django, nie mam żadnej kontroli nad samym zapytaniem. Indeks wydaje się logicznym rozwiązaniem. Próbowałem utworzyć indeks, aby to przyspieszyć, ale to nie miało znaczenia:

CREATE INDEX auth_user_email_upper ON auth_user USING btree (upper(email::text))

Co ja robię źle? Jak mogę przyspieszyć to zapytanie?

Nie ma obsługi indeksu dla LIKE/ ILIKEw PostgreSQL 8.4 - z wyjątkiem szukanych terminów zakotwiczonych w lewo .

Od wersji PostgreSQL 9.1 dodatkowy moduł pg_trgmudostępnia klasy operatorów dla indeksów trygramowych GIN i GiST obsługujących LIKE/ ILIKElub wyrażeń regularnych (operatorzy ~i przyjaciele). Zainstaluj raz na bazę danych:

CREATE EXTENSION pg_trgm;

Przykładowy indeks GIN:

CREATE INDEX tbl_col_gin_trgm_idx ON tbl USING gin (col gin_trgm_ops);

Związane z:

• Jak wdraża się LIKE?
• Dopasowywanie wzorców z LIKE, SIMILAR TO lub wyrażeniami regularnymi w PostgreSQL

Ten indeks nie pomoże z powodu „%” na początku dopasowania - indeks BTREE może dopasować tylko prefiksy, a symbol wieloznaczny na początku zapytania oznacza, że ​​nie ma ustalonego prefiksu do wyszukania.

Dlatego wykonuje skanowanie tabeli i dopasowuje kolejno każdy rekord względem ciągu zapytania.

Prawdopodobnie musisz spojrzeć na użycie indeksu pełnotekstowego i operatorów dopasowywania tekstu zamiast na wyszukiwanie podciągów w LIKE, którym jesteś w tej chwili. Więcej informacji na temat wyszukiwania pełnotekstowego można znaleźć w dokumentacji:

http://www.postgresql.org/docs/8.4/static/textsearch-intro.html

W rzeczywistości zauważam z tej strony, że LIKE najwyraźniej nigdy nie używa indeksów, co wydaje mi się dziwne, ponieważ powinno być w stanie rozwiązać przedrostki z symbolami wieloznacznymi przy użyciu indeksu BTREE. Kilka szybkich testów sugeruje, że dokumentacja jest prawdopodobnie poprawna, w takim przypadku żadna ilość indeksowania nie pomoże, gdy używasz LIKE do rozwiązania zapytania.