2-kolumnowy klucz główny i prosty select, Nie korzysta z klucza? Opcje

[phpion]

Witam,
mam tabelę z kolumnami:
ip_from, ip_to, country_code
kolumny ip_from oraz ip_to (obie bigint) tworzą klucz główny (w takiej kolejności). Wykonanie zapytania:

[SQL] pobierz, plaintext 
SELECT country_code FROM geolocalization WHERE ip_from<=3259185434 AND ip_to>=3259185434 LIMIT 1  ;
[SQL] pobierz, plaintext 

nie powoduje użycia indeksu :| tabela przeglądana jest sekwencyjnie. Czy tak powinno być? Nie powinien przypadkiem zostać użyty klucz główny?
Prosiłbym o informacje.
Pozdrawia,
pion

Ten post edytował phpion 4.12.2008, 22:19:56

[Sedziwoj]

Proste pytanie, ile masz tam danych?

[phpion]

Niecałe 80 000 (dokładnie 79 011 rekordów). Chyba przy takiej liczbie powinien być wykorzystany index...

[prond]

Zależy jaki to rodzaj indexu. Hash jest wykorzystywany wyłącznie w warunkach równościowych, a Ty stosujesz operatory <= i >=.
Dlatego zmień index na B-tree.
Więcej znajdziesz tutaj http://www.postgresql.org/docs/8.1/static/indexes-types.html

Ten post edytował prond 11.12.2008, 09:42:52

[phpion]

Dzięki za linka. Niestety utworzenie indeksu ("zwykły" lub z określeniem jako "btree") również nie powoduje jego użycia :| Ogólnie rzecz biorąc sądziłem, że nie będzie trzeba tworzyć indeksu, że zostanie użyty klucz główny. Ciągle tabela przeszukiwana jest sekwencyjnie z użyciem filtra:

"Limit (cost=0.00..0.09 rows=1 width=3)"
" -> Seq Scan on geolocalization (cost=0.00..1689.17 rows=19742 width=3)"
" Filter: ((ip_from <= 3259185434::bigint) AND (ip_to >= 3259185434::bigint))"

Jakieś pomysły? Dodam, że samo wyszukiwanie trwa chwilę (od 16 do 47ms).

[michalg]

Dzięki za linka. Niestety utworzenie indeksu ("zwykły" lub z określeniem jako "btree") również nie powoduje jego użycia :|

Po utworzeniu indeksu nie zaszkodzi jeszcze użycie polecenia: ANALYZE geolocalization;

Próbowałeś utworzyć dwa indeksy jednokolumnowe?

Możesz ewentualnie spróbować wyłączyć skanowanie:
SET enable_seqscan=false;
EXPLAIN ANALYZE ....

Być może wtedy planner zamiast skanowania użyje indeksu. Tylko nie zdziw się, jeżeli okaże się, że czas wykonywanie się wydłuży - po prostu nie zawsze warto korzystać z indeksów - w takich sytuacjach planner wybiera inne rozwiązania - np skanowanie.

[nevt]

spróbuj założyć indywidualne indeksy na obu kolumnach ip_ i wypróbuj zapytanie:

[SQL] pobierz, plaintext 
SELECT country_code FROM (SELECT country_code, ip_to FROM geolocalization WHERE ip_from<=3259185434) AS tmp WHERE ip_to>=3259185434 LIMIT 1
[SQL] pobierz, plaintext 

u mnie na tabeli testowej ok 100k rekordów wersja z podzapytaniem i pojedynczymi indeksami jest o 30% szybsza niż wersja z AND i dwupolowym indeksem...

[michalg]

u mnie na tabeli testowej ok 100k rekordów wersja z podzapytaniem i pojedynczymi indeksami jest o 30% szybsza niż wersja z AND i dwupolowym indeksem...

A bez podzapytania ale na pojedynczych indeksach? Używa wtedy indeksu?

[phpion]

Dzięki serdeczne wszystkim za odpowiedzi. Pomysł z podzapytaniem nie zmienił nic - uzyskiwane czasy były bardzo podobne. Jeśli natomiast chodzi o utworzenie dwóch osobnych indeksów (w sumie chciałem tego uniknąć) to zapytanie wykonuje się wręcz dłużej :|. Zostawie na razie tak jak jest. Zobaczymy czy w przyszłości nie wyjdą jakieś niespodzianki.
Jeszcze raz dzięki za zainteresowanie.
pion

[nevt]

ale na pewno sprawdziłeś wariant który opisałem, czyli podzapytanie z oddzielnymi indeksami? bo z treści twojego postu wynika raczej, że testowałeś podzapytanie na dwupolowym indeksie, a pojedyncze indeksy tylko z wersją warunkiem AND ...

[phpion]

Teraz sprawdziłem jeszcze raz dodając oba indeksy (jako osobne). Pokazywany czas wykonania zapytania jest mniej więcej taki sam ale w EXPLAINie widzę, że używany jest indeks założony na kolumnie ip_from. Nie wiem tylko czy go pozostawić czy nie. Teoretycznie czasy są takie same więc indeks wydaje się zbędny; z drugiej strony jednak patrząc nie powinien zaszkodzić bo na tabeli nie będą wykonywane żadne inne operacje poza SELECTami. Nie bardzo wiem co będzie lepszym wyjściem.

[michalg]

Jeżeli nie musisz oszczędzać każdego bajta miejsca na bazę danych, to myślę że spokojnie możesz zostawić indeks. Być może przy select'ie różnice czsowe są niewielkie, ale indeks może się przydać przy innych selectach z tej tabeli, albo łączących się z tą tabelą.

[phpion]

Być może przy select'ie różnice czsowe są niewielkie, ale indeks może się przydać przy innych selectach z tej tabeli, albo łączących się z tą tabelą.

No właśnie ta tabela będzie przeszukiwania tylko i wyłącznie tym zapytaniem, które podałem w 1 poście. To będzie w zasadzie jedyne zapytanie do niej kierowane hehe.

[nevt]

heh, proste wyjaśnienie... nie używa indeksu ip_from , bo wynik podzapytania nie ma już takiego indeksu... przyznaję - to mój błąd w rozumowaniu...

[michalg]

A tak z innej beczki - jak to IP w bazie jest zapisywane? Jako integer?

Pytam, bo zastanawiam się, czy jakikolwiek wpływ na wyszukiwanie miało by użycie w postgresie typu dla adresów internetowych. Chociaż podejrzewam, że żadnego albo minimalny.

[phpion]

A tak z innej beczki - jak to IP w bazie jest zapisywane? Jako integer?

Tak, dokładniej to jako BIGINT. Myślałem nad zastosowaniem typu INET ale jednak wolałem zostać przy BIGINT w celu łatwego wykorzystania bazy w MySQL. Poza tym chyba wyszukiwanie po adresach IP byłoby wolniejsze niż po zwyklych liczbach.

Ten post edytował phpion 11.12.2008, 20:46:20