CREATE INDEX - przyspieszenie zapytań
CREATE INDEX - przyspieszenie zapytań
Pora sobie odpowiedzieć na pytanie kiedy i w jaki sposób warto zastosować index by przyspieszyć nasze zapytania.
Mamy kilka typów indeksów, ale używać będziemy domyślnego, czyli B-tree. Nie jest to moje widzi misie, ale te pozostałe są po prostu specjalistyczne. Nie chciałbym tutaj przepisywać dokumentacji, toteż wspomnę tylko, że B-tree służy nam gdy używamy operatorów: <, <=, =, >=, >, BETWEEN, IN, IS NULL, a także po spełnieniu pewnych warunków (patrz dokumentacja) LIKE i ILIKE.
Na różnych forach, artykułach można przeczytać, że w przypadku małej ilości danych w tabeli nie warto zakładać indeksów z uwagi na to, że mogą one zamiast przyspieszyć spowolnić otrzymanie wyników. Kolejną rzeczą, którą chce pokazać to jak diametralnie można przyspieszyć zapytania.
Stworzyłem 6 tabel, które wypełniłem odpowiednią ilością rekordów: 20, 200, 2 000, 20 000, 200 000 i 2 000 000. Z początku miałem 4 kolumny: varchar(32) (unikalne wartości), varchar(32), int (unikalne wartości), int. Okazało się jednak, że typ oraz to czy są w tabeli unikalne wartości nie ma znaczenia. Do testów użyłem zwykłego zapytania z warunkiem WHERE = 'jakaś wartość'.
| rekordów | bez INDEX | z INDEX | ||
|---|---|---|---|---|
| 20 | 0.05ms | (cost=0.00..1.25 rows=1 width=71) | 0.05ms | (cost=0.00..1.25 rows=1 width=71) |
| 200 | 0.05ms | (cost=0.00..5.50 rows=1 width=71) | 0.05ms | (cost=0.00..5.50 rows=1 width=71) |
| 2 000 | 0.6ms | (cost=0.00..76.00 rows=1 width=71) | 0.05ms | (cost=0.00..8.27 rows=1 width=71) |
| 20 000 | 7ms | (cost=0.00..502.00 rows=1 width=71) | 0.06ms | (cost=0.00..8.27 rows=1 width=71) |
| 200 000 | 80ms | (cost=0.00..5013.00 rows=1 width=71) | 0.06ms | (cost=0.00..8.34 rows=1 width=71) |
| 2 000 000 | 700ms | (cost=0.00..50130.00 rows=1 width=71) | 0.3ms | (cost=0.00..8.80 rows=1 width=71) |
Dodanie indeksu bezapelacyjnie poprawiło czas wykonywania zapytania w przypadku dużej liczby rekordów. W przypadku 20 i 200 rekordów silnik bazy nie skorzystał z indeksów z uwagi na zbyt małą liczbę rekordów w bazie i wynik był taki jakby indeksu nie było.
Słowa kluczowe: PostgreSQL, Techblog, btree, create, index, sql, artykuł, blog
Komentarze i opinie
W porównaniach powinno się patrzeć na wartość kosztu zapytania, a nie jego czasu trwania. Koszt zapytania odzwierciedla ilość cykli procesora potrzebnych na jego wykonanie, zaś czas wykonania zależy od tego co aktualnie Twój procesor robił innego w międzyczasie.
Zrób jeszcze test dla 200 i 20 rekordów - wtedy wyciągniesz poprawniejsze wnioski. Dla aktualnie podanych wartości ilości rekordów w tabeli tego typu zapytania, które testowałeś powinny być wykonywane z użyciem indeksu.
Wiadomo, że indeksy *zwykle* przyśpieszają działanie - ale nie za każdym razem można ich użyć. Jasne - działają do porownań artmetycznych i jeżeli ktoś nie używa ich w tym wypadku to sam prosi się o problem.
Nieco gorzej jest z LIKE, o którym sam wspomniałeś, że "nie zawsze" można wykorzystać tam indeks, a tego typu wyszukania, obok złączeń, stanowią większość pracy bazy (* stwierdzenie nie podparte żadnymi badaniami innymi niż "na logikę"). Szczególnie gdy przeszukujesz duże pola VARCHAR albo już w ogóle pola binarne / TEXT.
@Draakhan: Hm, rzeczywiście przetestowałem jeszcze raz przy 2000 rekordów i teraz wyszło, że jest tak jak piszesz, że coś procesor wtedy dodatkowo musiał robić.
Muszę poprawić teraz wpis ;)
BTM, osobiście nie widzę większego zastosowania dla LIKE. LIKE w ogóle jako takie stoi w sprzeczności z pierwszą postacią normalną (niepodzielność wartości). Jeżeli chcesz wyszukiwać w polu tekstu czy też w polu binarnym to do tego stosuje się inne mechanizmy -- konkretnie tworzy się indeks wyrazów i dowiązanie wyraz->krotka.
Interesuje mnie natomiast jak dokładnie wyglądały testy, bo zauważyłem coś nie do końca dla mnie zrozumiałego -- wspomniane było o unikalnych wartościach i jakoby nie miały one znaczenia. Otóż dziwi mnie to, bo stworzenie kolumn z unikalnymi wartościami z automatu dodaje indeks.
@mina86: tak tworzy, dlatego nie dodawałem UNIQUE przy tworzeniu kolumn
> W przypadku 20 i 200 rekordów silnik bazy nie skorzystał z indeksów
Skąd wiesz, że nie korzystał? Jeśli nie korzystał dla 200 rekordów to od jakiej ilości rekordów będzie korzystał? Ot tak sobie baza powiedziała, że mam indeks ale go nie użyje?
Indeksy warto również zakładać na polach których używa się do sortowania i w klauzulach group by.
@Seban: EXPLAIN chyba mówi, czy użyto czy nie indeksów
Wiem Bartoszu, ale mnie ciekawi jak to się dzieje, że raz ich używa raz nie. :)
Jest mądra.
Tak jak mina86 mówi ;-)
Optymalizator zapewne patrzy czy się opłaca, czy opłaca się w danym stanie zużycia maszyny etc.
Użycie indeksów może też spowolnić działanie bazy danych. Trzeba wiedzieć czy aby na pewno jest nam ten indeks potrzebny i czy będziemy go często używać, bo jak nie to na cholerę?
1) Indeksy na polu korzystającym z Like do szukania tekstów tak ale tylko w konstrukcji LIKE 'slowo%' lecz już nie dla LIKE '%slowo%' , dla swobodbego wyszukiwania ciągów wyrazowych polecam Full Text Search
Dla jeszcze ciekawszej optymalizacji na polu na którym jest index robimy cosik takiego:
alter table tabela order by pole_z_indexem
czyli mając tabelę users z polami username i password mało tego że możemy dać index(username,password) to jeszcze możemy dać alter table users order by username, password asc
Nowy komentarz