Założenie indeksu oraz LIKE

Dla własnych testów, chciałem poprawić wydajność operacji like na kolumnie, wyczytałem że możemy uzyskać to gdy założymy index o typie GIN

Więc utworzyłem index:

CREATE INDEX index_name_trigram
    ON city
        USING gin (name gin_trgm_ops);

lecz gdy robie explain analyse, czas wykonania jest raczej bez zmian:

explain analyse select * from city where name like '%a%' and name like '%o%' and population < (select max(c.population) from country c where surface_area > 1000);

Seq Scan on city  (cost=8.46..111.84 rows=381 width=49) (actual time=0.099..1.696 rows=1027 loops=1)
  Filter: ((name ~~ '%a%'::text) AND (name ~~ '%o%'::text) AND (population < $0))
  Rows Removed by Filter: 3052
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=8.45..8.46 rows=1 width=4) (actual time=0.086..0.087 rows=1 loops=1)
          ->  Seq Scan on country c  (cost=0.00..7.99 rows=185 width=4) (actual time=0.005..0.058 rows=185 loops=1)
                Filter: (surface_area > '1000'::double precision)
                Rows Removed by Filter: 54
Planning Time: 0.215 ms
Execution Time: 1.818 ms

Dodam, że tabela city ma 4k wpisów

Poprosze o wskazówki :)

Wg mnie trzeba trzeba to zrobić na "zwykłym" indeksie tylko z opcją text_pattern_ops.

CREATE INDEX index_name_trigram  ON city (name text_pattern_ops);

mariusz00 napisał(a):

Dodam, że tabela city ma 4k wpisów

Poprosze o wskazówki :)

4k to jeszcze żadna tabela, to ino pyk do RAM-u ...

AnyKtokolwiek napisał(a):

mariusz00 napisał(a):

Dodam, że tabela city ma 4k wpisów

Poprosze o wskazówki :)

4k to jeszcze żadna tabela, to ino pyk do RAM-u ...

Tak, bardziej chodziło mi o to, że jest tam więcej niż 10 wpisów

I jeszcze jedno, baze postawiłem na dockerze, ale to bardziej informacyjnie.

Niestety ale utworzenie indeksu za pomocą text_pattern_ops nie przyniosło efektów. Chyba, że źle odczytuje dane z explain analyse ale nie zauważyłem różnicy

Przy tak małej tabeli Pg może dochodzić do wniosku, że przeszukanie tabeli jest szybsze niż patrzenie w indeks - spróbuj wrzucić tam milion wierszy, odpal analyze i porównaj; ale na cele praktyczne zakładanie indeksu dla tak małej tabeli nie ma sensu - nie każdy full table scan to śmierć aplikacji :-P

Patryk27 napisał(a):

Przy tak małej tabeli Pg może dochodzić do wniosku, że przeszukanie tabeli jest szybsze niż patrzenie w indeks - spróbuj wrzucić tam milion wierszy, odpal analyze i porównaj; ale na cele praktyczne zakładanie indeksu dla tak małej tabeli nie ma sensu - nie każdy full table scan to śmierć aplikacji :-P

Hmm, możliwe, ale jednak gdy zapytanie się wykonuje 1,8 sekundy, a gdybyśmy mieli obniżyć czas wykonania przykładowo do 0,3-0,4 to jest to dość znaczące - w sensie powinien zareagować założony index. Specjalnie dałem 2-3 warunki żeby troche obciążyć zapytanie

To zapytanie trwa 1.8 sekundy? Niemożliwe, chyba że odpalasz je na jakimś jednordzeniowym mikrokontrolerze 200 MHz

Tak pewnie :D
Bazke postawiłem na dockerze, a w sumie pobrałem jakiś sample z danymi żeby się nie bawić: https://hub.docker.com/r/ghusta/postgres-world-db
W pierwszym poście wkleiłem wynik explain. Czas wykonania ~ 1.8s

W pierwszym poście wkleiłem wynik explain. Czas wykonania ~ 1.8s

Tam jest napisane 1.8 milisekundy, nie?

W sumie teraz się zgubiłem w tym kliencie :D
Czy 1.818 ms oznacza 1ms + jakaś resztka, czy 1800 milisekund :D

Btw, baza decyduje kiedy skorzystać w wyszukiwaniu przez założony index, a kiedy tego nie robić? Mówie w kontekście gdy mamy mało wpisów tak jak tutaj, a gdybyśmy mieli milion

Czy 1.818 ms oznacza 1ms + jakaś resztka, czy 1800 milisekund :D

A czy 1.8 l oznacza 1800 litrów? :-P

baza decyduje kiedy skorzystać w wyszukiwaniu przez założony index, a kiedy tego nie robić?

Tak; brane pod uwagę są takie wartości jak np. cardinality określające liczbę unikalnych wpisów w indeksie (tj. indeksy zawierające dużą liczbę powtarzających się elementów są mniej użyteczne); generalnie query planning jest NP-złożony, więc bazy wykorzystują sporo rożnych przybliżeń oraz sztuczek (Pg w szczególności potrafi nawet wykorzystywać algorytmy genetyczne do generowania planów, patrz: https://www.postgresql.org/docs/current/geqo-pg-intro.html).

mariusz00 napisał(a):

lecz gdy robie explain analyse, czas wykonania jest raczej bez zmian:

explain analyse select * from city where name like '%a%' and name like '%o%' and population < (select max(c.population) from country c where surface_area > 1000);

Wyrażenia %a% i %o% są za krótkie. Ten typ indeksu obsługuje tylko %TutajCoNajmniej3Znaki%.

valdemar napisał(a):

mariusz00 napisał(a):

lecz gdy robie explain analyse, czas wykonania jest raczej bez zmian:

explain analyse select * from city where name like '%a%' and name like '%o%' and population < (select max(c.population) from country c where surface_area > 1000);

Wyrażenia %a% i %o% są za krótkie. Ten typ indeksu obsługuje tylko %TutajCoNajmniej3Znaki%.

Noo i to ja rozumiem! To było to! Gdzie podesłać piwo?

Dla podsumowania:
zapytanie:

explain analyse
    select * from city where city.district like '%forn%' or city.district like '%glan%'

Przed założeniem indeksu wykonywało się średnio 1.8ms, po założeniu indeksu ~ 0.15ms

Dodatkowo, tak jak się spodziewałem typ indeksu text_pattern_ops nie zadziałał tutaj. Musiałem skorzystać z typu gin gin_trgm_ops

Wynik:

Bitmap Heap Scan on city  (cost=41.11..75.20 rows=138 width=49) (actual time=0.044..0.092 rows=144 loops=1)
  Recheck Cond: ((district ~~ '%forn%'::text) OR (district ~~ '%glan%'::text))
  Heap Blocks: exact=7
  ->  BitmapOr  (cost=41.11..41.11 rows=139 width=0) (actual time=0.035..0.036 rows=0 loops=1)
        ->  Bitmap Index Scan on index_district_trigram  (cost=0.00..20.51 rows=68 width=0) (actual time=0.021..0.021 rows=73 loops=1)
              Index Cond: (district ~~ '%forn%'::text)
        ->  Bitmap Index Scan on index_district_trigram  (cost=0.00..20.53 rows=71 width=0) (actual time=0.014..0.014 rows=71 loops=1)
              Index Cond: (district ~~ '%glan%'::text)
Planning Time: 0.112 ms
Execution Time: 0.121 ms