Postgres i indeksy

Mam tabelę, która zawiera ok. 150 tysięcy rekordów.
Jej struktura to ID, kolumna z jakimś numerem seryjnym i do tego 2-3 kolumny z jakimiś tekstami/opisami (niezbyt długimi - po kilkadziesiąt znaków).

Ważne - tabela jest stworzona, zapełniona i potem tylko odpytywana. Odpytywanie będzie miało miejsce jedynie po numerze seryjnym - czyli użytkownik wprowadza ten numer, a SQL ma zwrócić stringi do niego przypisane. Dlatego (wprawdzie 150 tys to nie jest jakaś kosmiczna ilość) aż się prosi o założenie indeksu na kolumnie z kodem.

I teraz moje pytanie - jak uważacie, czy lepiej zrobić tą kolumnę jako varchar czy int? Kody mają 6 albo 7 znaków (jedynie cyfry - dlatego można to zapisać w postaci liczbowej), poza znalezieniem i wyrzuceniem do raportu, nic z tym się nie dzieje, więc z punktu widzenia aplikacji, typ danych kolumny z kodami nie ma żadnego znaczenia. Tutaj chodzi jedynie o to, żeby maksymalnie szybko działało wyszukiwanie.

Wydaje mi się, że lepszą opcją będzie zrobienie tego w postaci numerycznej niż tekstowej, ale chciałem poprosić Was o potwierdzenie, albo wyprowadzenie z błędu. Poszukałem w necie i w oparciu o te informacje także mam wrażenie, że int jest słuszniejszy, ale to może być tak jak z samochodem - kupujesz Focusa i nagle wszędzie widzisz Fordy na ulicach ;)

jedynie cyfry - dlatego można to zapisać w postaci liczbowej

Z ciekawości: pesel zapisałbyś jako bigint czy varchar? :-)

Zależy od potrzeb i innych czynników - czy np. będzie on jakoś walidowany, czy jedynie zapisany w bazie, co będzie się z nim potem działo, w jaki sposób będzie przetwarzany itp., aczkolwiek ponieważ ten PESEL, wprawdzie jest cyfrowy, ale nie będzie traktowany jako liczba, a do tego ma stałą długość, raczej bym się skłaniał do zapisu jako char(11).

Tylko różnica jest taka, że w moim przypadku zależy mi jedynie na tym, żeby szukanie działało jak najsprawniej i żeby najłatwiej było bazie przeszukiwać indeks. Dlatego to może być zapisane w dowolnej postaci, z mojego punktu widzenia nie ma to znaczenia, ale (jak pisałem) z tego co znalazłem, to łatwiej jest przelecieć indeks b-tree na wartościach liczbowych.

wprawdzie jest cyfrowy, ale nie będzie przetwarzany jako liczba

Czy Twoje numery seryjne będą przetwarzane jako liczba? Ponieważ z postu wynika, że nie.

w moim przypadku zależy mi jedynie na tym, żeby szukanie działało jak najsprawniej i żeby najłatwiej było bazie przeszukiwać indeks

Liczby będą minimalnie szybsze, lecz wchodzimy tutaj w teren premature optimization.

Nie, to chodzi jedynie o znalezienie odpowiedniej wartości w tabeli i pobranie przypisanych do nich wartości. Przyjmijmy, że mamy tabelę z kolumnami numer katalogowy, producent, model, opis. Użytkownik podaje numer katalogowy, a SQL zwraca producenta, model i opis. Nic się z tym dziać nie będzie, jak napisałem - tabela zostanie jednorazowo zapełniona danymi, a potem będzie tylko odczytywana. I powtarzam - to, czy ten numer seryjny będzie tekstem czy liczbowo dla mnie nie ma żadnego znaczenia, jedynie chodzi o to, żeby założony na tej kolumnie indeks działał jak najsprawniej.

jedynie chodzi o to, żeby założony na tej kolumnie indeks działał jak najsprawniej

W takim razie załóż indeks liczbowy.

Jeśli to są numery zamówień, to upewnij się że użytkownicy nie mogą sobie nawzajem podglądać nieswoich zamówień.
Bo takie niebezpieczeństwo występuje gdy zamówienie / fv generujesz na podstawie numeru sekwencyjnego.
Można się przed tym zabezpieczyć (np. sprawdzając zawsze też nr klienta pytającego o dane).

Nie, to nie są żadne poufne dane, z grubsza chodzi o to, co napisałem 2 posty wyżej. Taki katalog, który na podstawie oznaczenia ma wczytać informacje o przedmiocie. Każdy korzystający z aplikacji ma mieć dostęp do tego bez jakichkolwiek ograniczeń.

znacznie więcej zyskasz jeśli zamiast indeksu liczbowego założysz indeks na wszystkie kolumny po których będziesz szukał + kolumny, które będziesz wyciągał. W Twoim przypadku to będzie indeks na numer katalogowy, producent, model, opis. Wtedy silnik w ogóle nie będzie musiał odwoływać się do tabeli a skoro insertów i tak nie będzie to nie będzie to miało znaczenia dla wydajności dodawania danych (poza pierwszym)

No ale wydaje mi się, że jak już baza ustali ID, dla którego ma pobrać dane, to później już ich odczyt z tabeli to formalność. Największym problemem jest przeszukanie tych 100 tysięcy wpisów - bez indeksów to trzeba przelecieć przez wszystkie po kolei i tego chciałem uniknąć. Pytanie - czy indeksy na wszystkich kolumnach mają sens? Jeśli tak, to nie ma problemu i mogę tak zrobić, tylko jakoś nie jestem przekonany, czy to coś da.

Nie do końca taka formalność: jeśli indeks w całości pokrywa spis kolumn, o które pytasz w klauzuli select (czyli jest to tzw. covering index), silnik bazy nie musi męczyć się z wczytywaniem danych z rzeczywistej tabeli (w przypadku Postgresa nazywanej heap) i opiera całe zapytanie wyłącznie o dane z indeksu.

Tutaj masz coś o tych "covering index": https://www.postgresql.org/docs/11/indexes-index-only-scans.html
i https://paquier.xyz/postgresql-2/postgres-11-covering-indexes/
i https://info.crunchydata.com/blog/why-covering-indexes-are-incredibly-helpful

(przyda się fraza INCLUDE).

Skoro to tylko read-only, to możesz zrobić sobie challenge na nowy rok i zrobić szybką armatę jako rozszerzenie postgresa -> funkcja w C.
https://www.postgresql.org/docs/12/xfunc-c.html

Prosta hashmapa inicjalizowana tymi 150k wpisami. Nie jest to proste (użycie i utrzymanie) ani szybkie (implementacji), ale wywołania powinny być mega szybkie (odpadną operacje I/O na czytanie indeksu/tabeli, czy locki mające zapewnić spójność danych w ramach transakcji).

Propozycja w ramach pobudzenia wyobraźni ;-)

@yarel: pomysł może ciekawy, ale totalnie nie mam czasu (ani ochoty i być może umiejętności) na wdrażanie tego w życie. Nie musi to być prędkość szukania rzędu 0,0000023 sekundy, po prostu mam tabelę jedynie do odczytu i chcę ten odczyt maksymalnie przyspieszyć/zoptymalizować. ale nikt z powodu pół sekundy dłuższego czekania nie będzie robić tragedii, nie przesadzajmy :D

No a poza tym trochę nieskromne i nieprawdziwe by było twierdzenie, że ja sobie sam napiszę coś lepiej, niż grupa ekspertów, która opracowała mechanizmy tworzenia i obsługi indeksów w Postgresie.

@cerrato: rozumiem brak czasu i sam często wybieram pragmatyczne rozwiązania, zamiast poeksperymentować i zepsuć parę rzeczy ;)

Z innych pomysłów, to można przeorganizować tabelkę względem indeksu:

• zrobić indeks na kluczu wyszukiwania
• zrobić klastrowanie po tym indeksie (-> https://www.postgresql.org/docs/current/sql-cluster.html )

Pomysły i dobre, ale tutaj wychodzimy z przysłowiową armatą na komara. Po pierwsze - dla Postgresa 150k rekordów to nie jest żadna kosmiczna ilość. A po drugie - nie jest to aplikacja dla szpitala, która musi działać w czasie rzeczywistym, więc pól sekundy mniej czy więcej czekania nie robi różnicy. Po prostu - skoro można niewielkim nakładem pracy dołożyć indeks i przyspieszyć pracę, więc czemu z tego nie skorzystać? Ale jakbym miał poświęcić na to więcej czasu to bez przesady, nie ma co przeginać.

yarel napisał(a):

Skoro to tylko read-only, to możesz zrobić sobie challenge na nowy rok i zrobić szybką armatę jako rozszerzenie postgresa -> funkcja w C.
https://www.postgresql.org/docs/12/xfunc-c.html

Prosta hashmapa inicjalizowana tymi 150k wpisami. Nie jest to proste (użycie i utrzymanie) ani szybkie (implementacji), ale wywołania powinny być mega szybkie (odpadną operacje I/O na czytanie indeksu/tabeli, czy locki mające zapewnić spójność danych w ramach transakcji).

Propozycja w ramach pobudzenia wyobraźni ;-)

Chciałbym to zobaczyć czy rzeczywiście udałoby się to zrobić szybciej, w postgressie jest out of the box MVCC czyli locków na czytanie danych nie ma, a jak jest wystarczająco RAMu to i tak tabela cały czas będzie trzymana w pamięci i nie będzie odczytów z dysku. Na dodatek w postgresie mamy również dostępny indeks oparty na hashowaniu jako alternatywa dla btree, także pobicie tego co postgres oferuje może nie być takie proste.

Także indeks pokrywający albo indeks klastrowy z kolumną typu int to najlepsze rozwiązanie.

Będziesz szukał po tych numerach seryjnych czy nie? Jeśli tak to lepiej string i B-Tree, jeśli nie to albo indeks liczbowy (jeśli wiodące 0 nie mają znaczenia) albo string i hash-index.

Poza tym w PSQL bardziej idiomatyczne jest używanie TEXT zamiast VARCHAR.

@hauleth: tak, jak napisałem kilka postów wyżej - https://4programmers.net/Forum/1644477, każdy rekord składa się z numeru seryjnego oraz kilku stringów z opisem przypisanych do tego numeru. Tabela jest jedynie do odczytu, jedyna forma szukania to po numerze seryjnym. Żeby usprawnić wyszukiwanie, na kolumnie będącej kluczem szukania chcę założyć indeks (plus może jak inni radzili - także na kolumnach zawierających zwracane dane).
Dla mnie nie ma znaczenia, czy kolumna z numerem seryjnym będzie miała postać tekstową czy liczbową, chodzi jedynie o to, jaką łatwiej będzie bazie przeszukać w odpowiedzi na mój SELECT FROM.

jedyna forma szukania to po numerze seryjnym

Chodziło mi o to czy jeśli masz nr seryjny 12345 i ktoś wpisze 123 to ma mu się również wyświetlić ten wpis. Jeśli tak, to tekstowa się lepiej sprawdzi, bo łatwiej zrobić zapytanie ILIKE.

Nie, nie będzie żadnego LIKE ani dopasowywania. Aplikacja prześle do SQL gotowy i kompletny kod, który albo zostanie znaleziony, albo nie ;) Dlatego (moim zdaniem) sprawniej przejdzie szukanie po liczbach.

Wtedy tak. Po prawdzie jeśli będzie tam hash indeks to czasy powinny być praktycznie identyczne, aczkolwiek jeśli wiodące zera nie są problemem, masz pewność, że nie pojawią się litery, oraz, że nie będzie trzeba szukać cząstkowo, to liczby będą lepsze.

Wiodące zera sobie dodam już po otrzymaniu odpowiedzi z SQL, na etapie wypluwania treści do raportu.
W takim razie chyba sprawa ogarnięta, dzięki za pomoc :)

P.S. a co @hauleth i @Marcin.Miga sądzicie o pomyśle podanym wcześniej, żeby nie zakładać indeksu tylko na kolumnie z numerem seryjnym (po której będę wyszukiwać), ale żeby także indeksować kolumny zawierające zwracane treści?

Indeksować bez sensu IMHO. Jak już to zrób indeks zawierający te pola. Masz INCLUDE przy tworzeniu indeksu, który dokładnie do tego służy. Możesz też zaktualizować statystyki planera zaraz jak wciśniesz wszystkie dane, co może pozwolić planerowi trochę jeszcze przyspieszyć zapytania.

Tylko pytanie, czy dodanie do tego indeksu dodatkowych pól nie spowoduje, że taki indeks będzie się wolniej przeszukiwać, niż jakby zawierał jedynie kolumnę z numerem seryjnym i nic poza tym?

Po to wlasnie jest ten INCLUDE zeby wyszukiwanie nie bylo wolniejsze. A jak jest w rzeczywistosci to musialbys przetestowac.

Na SQL się specjalnie nie znam, ale 150 tysięcy rekrodów to dla Postgresa chyba śmieszna baza...

=> select count(*) from mysecretdata;
  count  
---------
 9051215
(1 row)

explain analyze select * from mysecretdata where id = 'XYZ-20191111-074004-638-12-whatever-sth-xy';
                                                             QUERY PLAN                                                             
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using mysecretdata_pkey on mysecretdata  (cost=0.56..8.58 rows=1 width=235) (actual time=0.482..0.484 rows=1 loops=1)
   Index Cond: (id = 'XYZ-20191111-074004-638-12-whatever-sth-xy'::text)
 Planning Time: 0.134 ms
 Execution Time: 0.559 ms
(4 rows)

Owszem, jak na "porządnego" SQL to 150 tysięcy nie jest jakąś zawrotną ilością, ale skoro można praktycznie zerowym nakładem to przyspieszyć, to głupio by było nie skorzystać ;)

i taka uwaga - to nie jest cała baza, a jedynie jedna z tabel w tej bazie. Cała baza będzie znacznie większa, ponadto będą na niej cały czas przeprowadzane operacje zapisu.