Różnice w wydajności zapytań LIKE PostgreSQL

Widziałem dość duże różnice w czasach odpowiedzi dotyczących LIKEzapytań do określonej tabeli w mojej bazie danych. Czasami uzyskam wyniki w ciągu 200-400 ms (bardzo akceptowalne), ale innym razem może to zająć nawet 30 sekund, aby uzyskać wyniki.

Rozumiem, że LIKEzapytania wymagają dużej ilości zasobów, ale po prostu nie rozumiem, dlaczego byłaby tak duża różnica w czasach odpowiedzi. Zbudowałem indeks btree na owner1polu, ale nie sądzę, że pomaga to w LIKEzapytaniach. Czy ktoś ma jakieś pomysły?

Przykładowy SQL:

SELECT gid, owner1 FORM parcels
WHERE owner1 ILIKE '%someones name%' LIMIT 10

Próbowałem też:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%') LIMIT 10

I:

SELECT gid, owner1 FROM parcels
WHERE lower(owner1) LIKE lower('someones name%') LIMIT 10

Z podobnymi wynikami.
Liczba wierszy tabeli: około 95 000.

FTS nie obsługuje LIKE

Uprzednio zaakceptowane odpowiedź była błędna. Wyszukiwanie pełnotekstowe z indeksami pełnotekstowymi nie jest w ogóle dla LIKEoperatora, ma własne operatory i nie działa dla dowolnych ciągów znaków. Opiera się na słowach opartych na słownikach i podstawach. To robi support dopasowanie prefiksu dla słów , ale nie z LIKEoperatorem:

• Uzyskaj częściowe dopasowanie z kolumny TSVECTOR indeksowanej przez GIN

Indeksy Trigram dla LIKE

Zainstaluj dodatkowy moduł, pg_trgmktóry zapewnia klasy operatorów dla indeksów trygramowych GIN i GiST do obsługi wszystkich wzorców LIKEiILIKE , a nie tylko zakotwiczonych w lewo:

Przykładowy indeks:

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

Lub:

CREATE INDEX tbl_col_gist_trgm_idx ON tbl USING gist (col gist_trgm_ops);

• Różnica między GiST a indeksem GIN

Przykładowe zapytanie:

SELECT * FROM tbl WHERE col LIKE '%foo%';   -- leading wildcard
SELECT * FROM tbl WHERE col ILIKE '%foo%';  -- works case insensitively as well

Trygramy? A co z krótszymi strunami?

Słowa z mniej niż 3 literami w indeksowanych wartościach nadal działają. Instrukcja:

Uważa się, że każde słowo ma dwie spacje z przedrostkiem i jedną spację z sufiksem podczas określania zestawu trygramów zawartych w ciągu.

A wzorce wyszukiwania zawierające mniej niż 3 litery? Instrukcja:

W przypadku wyszukiwania zarówno przy LIKEużyciu wyrażeń regularnych, jak i przy wyszukiwaniu z użyciem wyrażeń regularnych należy pamiętać, że wzorzec bez trygramów, które można wyodrębnić, zdegeneruje się do pełnego skanowania indeksu.

Oznacza to, że skanowanie indeksów indeksów / bitmap nadal działa (plany zapytań dla przygotowanej instrukcji nie zepsują się), po prostu nie da ci to lepszej wydajności. Zwykle nie ma dużej straty, ponieważ 1- lub 2-literowe łańcuchy nie są selektywne (więcej niż kilka procent dopasowań w tabeli bazowej), a obsługa indeksu nie poprawiłaby wydajności na początku, ponieważ pełne skanowanie tabeli jest szybsze.

text_pattern_ops do dopasowywania prefiksów

W przypadku wzorców zakotwiczonych w lewo (bez wiodących symboli wieloznacznych) uzyskuje się optimum z odpowiednią klasą operatora dla indeksu btree: text_pattern_opslubvarchar_pattern_ops . Obie wbudowane funkcje standardowego Postgres, nie są potrzebne żadne dodatkowe moduły. Podobna wydajność, ale znacznie mniejszy indeks.

Przykładowy indeks:

CREATE INDEX tbl_col_text_pattern_ops_idx ON tbl(col text_pattern_ops);

Przykładowe zapytanie:

SELECT * FROM tbl WHERE col LIKE 'foo%';  -- no leading wildcard

Lub , jeśli powinieneś uruchomić swoją bazę danych z ustawieniem regionalnym `` C '' (efektywnie nie ustawień regionalnych), to wszystko i tak jest sortowane według kolejności bajtów, a zwykły indeks btree z domyślną klasą operatora załatwia sprawę.

Więcej szczegółów, wyjaśnienia, przykłady i linki w tych powiązanych odpowiedziach na dba.SE:

• Dopasowywanie wzorców za pomocą LIKE, SIMILAR TO lub wyrażeń regularnych w PostgreSQL
• Jak wdrażane jest LIKE?
• Szybkie wyszukiwanie podobnych ciągów w PostgreSQL

Możliwe, że te szybkie to zakotwiczone wzorce z rozróżnianiem wielkości liter, które mogą używać indeksów. tj. nie ma symbolu wieloznacznego na początku łańcucha dopasowania, więc executor może użyć skanowania zakresu indeksu. ( odpowiedni komentarz w dokumentacji jest tutaj ) Lower i ilike również utracą możliwość korzystania z indeksu, chyba że specjalnie utworzysz indeks w tym celu (zobacz indeksy funkcjonalne ).

Jeśli chcesz wyszukać ciąg w środku pola, powinieneś przejrzeć indeksy pełnotekstowe lub trygramowe . Pierwsza z nich jest w rdzeniu Postgres, druga jest dostępna w modułach Contrib.

Możesz zainstalować Wildspeed , inny typ indeksu w PostgreSQL. Wildspeed działa z symbolami wieloznacznymi% word%, nie ma problemu. Wadą jest rozmiar indeksu, który może być duży, bardzo duży.

Proszę wykonać poniższe zapytanie, aby poprawić wydajność zapytania LIKE w postgresql. utwórz taki indeks dla większych tabel:

CREATE INDEX <indexname> ON <tablename> USING btree (<fieldname> text_pattern_ops)

co jest warte, Django ORM zwykle używa UPPER(text)dla wszystkich LIKEzapytań, aby nie rozróżniać wielkości liter,

Dodanie indeksu UPPER(column::text)znacznie przyspieszyło mój system, w przeciwieństwie do innych rzeczy.

Jeśli chodzi o wiodące%, tak, to nie będzie używać indeksu. Zobacz ten blog, aby uzyskać świetne wyjaśnienie:

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

Niedawno miałem podobny problem z tabelą zawierającą 200000 rekordów i muszę powtarzać zapytania LIKE. W moim przypadku szukany ciąg został naprawiony. Inne dziedziny były zróżnicowane. Dzięki temu mogłem przepisać:

SELECT owner1 FROM parcels
WHERE lower(owner1) LIKE lower('%someones name%');

tak jak

CREATE INDEX ix_parcels ON parcels(position(lower('someones name') in lower(owner1)));

SELECT owner1 FROM parcels
WHERE position(lower('someones name') in lower(owner1)) > 0;

Byłem zachwycony, gdy zapytania wróciły szybko i zweryfikowałem, że indeks jest używany z EXPLAIN ANALYZE:

 Bitmap Heap Scan on parcels  (cost=7.66..25.59 rows=453 width=32) (actual time=0.006..0.006 rows=0 loops=1)
   Recheck Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
   ->  Bitmap Index Scan on ix_parcels  (cost=0.00..7.55 rows=453 width=0) (actual time=0.004..0.004 rows=0 loops=1)
         Index Cond: ("position"(lower(owner1), 'someones name'::text) > 0)
 Planning time: 0.075 ms
 Execution time: 0.025 ms

Twoje podobne zapytania prawdopodobnie nie mogą korzystać z utworzonych indeksów, ponieważ:

1) Twoje kryteria LIKE zaczynają się od symbolu wieloznacznego.

2) użyłeś funkcji z kryteriami LIKE.

Kiedykolwiek używasz klauzuli w kolumnie z funkcjami, np. LIKE, ILIKE, upper, lower itp. Postgres nie weźmie pod uwagę twojego normalnego indeksu. Wykonuje pełne skanowanie tabeli przechodząc przez każdy wiersz i dlatego będzie działać wolno.

Prawidłowym sposobem byłoby utworzenie nowego indeksu zgodnie z zapytaniem. Na przykład, jeśli chcę dopasować kolumnę bez rozróżniania wielkości liter, a moja kolumna to varchar. Wtedy możesz to zrobić w ten sposób.

create index ix_tblname_col_upper on tblname (UPPER(col) varchar_pattern_ops);

Podobnie, jeśli twoja kolumna jest tekstem, robisz coś takiego

create index ix_tblname_col_upper on tblname (UPPER(col) text_pattern_ops);

Podobnie możesz zmienić górną funkcję na dowolną inną, którą chcesz.