Mamy pytanie dotyczące sensu stawiana bazy na silniku postgre danych w przypadku gdy :

- baza zawiera ok. 400 milionów rekordów. Rozmiar rekordu od 120 do 950 bajtów.
- nie można tworzyć kluczy primary key bo dane w kolumnach nie są unikalne.
- baza ma pracować online wystawiona w intenrnecie z średnio 4,5 userami online.
- zapytania do bazy danych odwołują się do 3-6 tabel jednocześnie.

Baza zawiera wszystkie części samochodowe świata.

Czy jedynym wyjściem jest tutaj oracle?

W tym momencie zapytanie o wyświetelenie 30 rekordów w pgadminie do jednej z głównych tabel trwa około 4 minut. (tabela przeindeksowana i klastrowana)

Może ktoś borykał się z takimi bazami.

będę wdzięczny za każdą odpowiedź.

"dane w kolumnach nie są unikalne"
To na jakiej postawie są rozróżniane?
Pytanie o index'y czy aby na pewno są dobrze założone, tzn. czy zapytania korzystają z nich. To samo klastrów się tyczy.
Miałem tabelę testową coś 30milionów rekordów i z prawidłowymi index'ami wyszukiwanie trwało milisekundy, dlatego mam te pytania.

no właśnie to jest dziwne że tam gdzie ma być primary key dane dla tej kolumny się powtarzają i wywala błąd więc primary key nie ma są tylko indexy.

Mam pytanie gdybym dodał sam dodatkową kolumnę której dałbym primary_key i autoincrement a następnie włączył do indexu to coś to da?
Tak naprawdę rozchodzi się o główna tabelę gdzie dane z innych tabel są łączone wygląda ona w uproszczeniu tak:


gdzie :

artnr - kod części
genartnr - kod w drzewie części
krintnr - rodzaje grup pojazdów (2-osobowe, 16-ciężarówy, 5 -silniki, 7-hamulce itd)
kritwert - kod samochodu
sortnr - sortowanie wyników dla tego artnr
lfdnr - kod części z które wchodzą w skład części główne artnr

i tak jest zrobiony index btree na artnr,genartnr,kritwert

ta tabela zawiera trochę ponad 100 milionów rekordów i zapytanie, takie jak poniżej, trwa z 150 sekund

[SQL] pobierz, plaintext 
SELECT a.ArtNr, a.DLNr, a.GenArtNr, a.LfdNr,  d.Bez AS opis FROM tbl_400 a 
LEFT OUTER JOIN tbl_320 b ON (a.GenArtNr = b.GenArtNr)
LEFT OUTER JOIN tbl_323 c ON (b.NartNr = c.NartNr) 
LEFT OUTER JOIN tbl_030 d ON (c.BezNr = d.BezNr)
LEFT OUTER JOIN tbl_325 e ON (b.VerwNr = e.VerwNr)
WHERE a.genartnr="34" AND a.krintnr="2" AND a.kritwert="12344" AND d.SprachNr='19' ORDER BY a.sortnr ASC
[SQL] pobierz, plaintext 

Napewno. Nawet jezeli to id ci sie nigdy do niczego ine przyda to dla bazy to duzy skok wydajnosci.
I nie auto_increment Auto increment jest w mysql w pg są sekwencję Tworzysz kolumne, dajesz jej typ "serial" i to wszystko

Może to głupie pytanie sprawdzić czy zrobione indexy są w użyciu
bo wydajność I/) dla tabel mam nieciekawe - fakt że tabela dopiero powstała

Sprawdzanie co robi zapytanie:

[SQL] pobierz, plaintext 
EXPLAIN SELECT...
[SQL] pobierz, plaintext 

To ze powiedziales że nie ma unikanlej wartości mowi o tym ze nie do konca ta tablica jest odpowiedno znormalizowana i może udałoby sie cos utargowac jakims rozbiciem, ale z tego co podajesz pozniej wyglada na to ze jest chyba ok.
Przy takiej ilosci rekordow pamietaj o problemie z powtarzalnoscia danych..

Nie znam sie na postgresie ale btree to chyba najprostsza forma indeksu nie ma tam czegos bardziej zmyslnego zeby operowal na wiekszych wycinkach wartosci kolumny? Takie drzewo to zamulacz i predzej trafisz skanujac tablice za pelna wartoscia jak chodzac po drzewie.

Muszisz krok po kroku robic sobie te zapytania i analizowac EXPLAIN oraz czas wykonania zapytania.
Na poczatku pytaj po prostu jedna kolumne bez zadnych ORDER itp,bez indeksow itp i analizuj sobie jak to dziala.
Nie daj sie oszukac buforowi bazy bo moze Ci cos zapamietac (zobacz jaka jest komenda na wyczyszczanie).

Na koniec jesli bedziesz wykonywac takie zapytania jak np
tab.kategoria=KLOCKI
tab.marka=VOLVO
tab.rok between X and Y

To mozesz utworzyc sobie indeksy na wielu kolumnach. Przeczytaj dokumentacje bo one dzialaja w momencie kiedy pytasz o 3 kolumny a jak juz zapytasz o 1 to ten indeks nie dziala (jakos tak).

Optymalizatory sa dziwne i trzeba patrzec co robia.

Np mysql nie radzi sobie z GROUP BY podczas laczenia 3 tablic a mogloby sie wydawac ze takie przypadki juz bedzie w stanie sobie rozpracowac.

Na koniec mozesz sie zastanowic nad cachem takich zapytan do talic pomocniczych oraz nad silnikiem wyszukiwania po frazie tez na tablicy pomocniczej.

PS. Tez jestem z Czestochowy wiec jak bedziesz potrzebowal wiecej pomocy to wal

Hej no witam zioma

Znalazłem sposób na ograniczenie rekordów w tabeli głównej z 120 mln to 37 mln poprzez zawężania do kraju
więc jestem już jakby w domu dorobiłem jedną kolumnę która na 100 będzie unkalne (serial) klucz główny na podstawowe kolumny tabeli
oraz index btree na kolumny najczęściej używane w zapytaniach.

Teraz poczekam z dzionek na sparsowanie nowej tabelki i oby sie nic nie wykrzaczyło

Z tego zapytania co pisałeś wynika (nie wnikam w jego sens czy też sposób gromadzenia danych), że potrzebujesz takie index'y na:
GenArtNr
NartNr
BezNr
VerwNr
genartnr,krintnr,kritwert,SprachNr (index 4 kolumnowy)
sortnr

To tylko te indexy z których powinna wykorzystać baza danych w czasie wykonywania tej kwerendy, ogólnie moim zdaniem problem wymaga lepszego przebadania, ale nie mam czasu aby tego zrobić, zresztą nie mi za to płacą.
Pamiętaj że klastry opłaca się budować jeśli jedno zapytanie prawie zawsze trafia do jednego z klastrów. Co do klucza głównego, to pamiętaj że może być wielokolumnowy i taki powinieneś założyć, dodanie jednej kolumny typu serial nic nie da, a nawet będzie tylko dodatkowy narzut.

Nie zgodzę się. Prawda jest taka, że pierwsza kolumna z indexu wielokolumnowego determinuje użycie danego indeksu. Jeżeli natomiast w warunku zostanie użyta np. druga kolumna to indeks również będzie pomocny ale jego "skuteczność" będzie niższa niż rozpoczęcie listy warunków od pierwszej kolumny indeksu. Nie można jednak powiedzieć, że indeks w ogóle nie zostanie użyty.

Po pierwsze nie bój się sztucznego primary key czyli tego co w MySql nazywa się polem auto_increment a w Postgresie sekwencją. Indeksy ustaw tylko na polach po których wykonuje się wyszukiwania lub złaczenia - nadmiar indeksów to też nie dobrze bo przecież serwer bazy danych musi je aktualizować przy każdej zmianie danych. Rozumiem, że ciężko zmieniać strukturę bazy w sytuacji kiedy istnieją w niej już dane, ale często ten wysiłek się opłaca. Więc jeśli to możliwe zoptymalizuj schemat. Nadaj kolumnom właściwe typy i np. długości, wyeliminuj redundancje wykonaj normalizację. Jak masz nie taką strukturę to i Oracle Ci nie pomoże.

Tez nie do konca piszesz prawde. Chodzi o roznice w zasadzie dzialania indeksu. W przypadku Postgre i uzyciu B-tree ten indeks zostanie uzyty tak jak napisales ale przy uzyciu gist juz jest uwarunkowany uzyciem pierwszej kolumny w warunkach.
MySQL tez nie uzyje indeksu przy uzyciu tylko 2 kolumny z indeksu musi miec pierwsza.

@calebos
Pierwsza, druga? a gdzie optymalizator, coś kręcisz.

@bigZbig
"nie bój się sztucznego primary key" po co kolumna która nie jest używana, to można dodać tysiące, one nic nie pomogą a tylko będą przeszkadzać, do tego w każdej tabeli jest jakiś klucz główny, inaczej nie można było by rozróżnić krotek, czyli nie było można ich rozróżniać, czyli powielanie danych.
Co do normalizacji, to się nie zgodzę, tu raczej właśnie stawiał bym na denormalizację w celu przyspieszenia działania. A raczej bym stworzył podwójne dane, jedne znormalizowane do edycji i drugie zdenormalizowane do wyszukiwania. (jeśli jest jakaś edycja tych danych)

Sedziwoj,

Jak dasz indeks na 2 kolumnach ( nie btree) to optymalizator go nie uzyje w przypadku kiedy podajesz warunek tylko dla drugiej kolumny ktora jest w indeksie.

Nic nie krece tylko cytuje dokumentacje.

@calebos
Dlatego jak występuje czasem tylko druga kolumna to dodajemy index na nią. (a swoją drogą, teraz wyjaśniłeś się jasno i nawet nie musiałeś wspomnieć że tak piesze w dokumentacji bo to wiem)
Kłopotem jest jak jest dużo OR w kwerendzie, wtedy to już jest na prawdę problem.

Dzięki za cenne informacje

W dokumentacji mam napisane że w skład klucza primary wchodzą kolumny



problem w tym że jak próbuje utworzyć primary dla tych kolumn to wywala mi że dane nie są unikalne - czyli coś mogło pójść nie tak przy parsowaniu danych do tabeli
i teraz pytanie jak znaleźć z duplikowane wartości które przeszkadzają w utworzeniu prmiary key ? (tabela ma 27 mln rekordów)

[SQL] pobierz, plaintext 
SELECT artnr, sortnr, lfdnr, genartnr, count(*) FROM nazwa_tabeli GROUP BY artnr, sortnr, lfdnr, genartnr HAVING count(*) > 1
[SQL] pobierz, plaintext 

Nie wiem czy najszybsza, ale na pewno skuteczna.

Dzięki zaraz zapuszczę

Udało mi się zrobić index w ten sposób że do kolumn artnr,genartnr,lfdnr,sortnr dodałem pole typu serial - na ile to będzie efektywne sie zaraz przekonam

Daj potem znać które rady okazały się skuteczne. I jak ostatecznie to wyszło.

Cały czas się z tym męcze ale mam już spore postępy
- ograniczyłem liczbę rekordów w głównej tabeli aplikacji do 118 milionów
- z parsowałem ją od nowa (4 dni ) dodając kolumnę table_id BIGSERIAL
- utworzyłem index główny z kolumn które będę używał w zapytaniach + kolumna z "sztucznym" indexem BIGSERIAL
- potem index btree na kolumny uzwane w zapytaniach ale już bez table_id (BIGSERIAL)
- nie klastrowałem indexów (nie jestem pewien czy mi to pomoże czy zaszkodzi)

[SQL] pobierz, plaintext 
EXPLAIN ANALYZE SELECT count(1) FROM tecdoc_400b WHERE genartnr='8' AND artnr='1152'
[SQL] pobierz, plaintext 

daje



serwer: procek INTEL CORE 2 DUO E8400 2x3.0Ghz, ram 2 gb

- trochę gorzej w połączeniu z tabelami dołączanym joinem, nie stosuje mi w nich indexów, (a jak odwołuję się tylko do nich stosuje indexy), więc tu się jeszcze męcze,
- całościowe pobranie wszystkich danych dla aplikacji www w najdłuższym zapytaniu trwa od 10-27 sekund, ale docelowo aplikacja będzie stał na bardziej wydajnym serwerze.

[SQL] pobierz, plaintext 
SELECT a.ArtNr, a.DLNr, a.GenArtNr, a.LfdNr, d.Bez AS opis FROM tbl_400 a
LEFT OUTER JOIN tbl_320 b ON (a.GenArtNr = b.GenArtNr)
LEFT OUTER JOIN tbl_323 c ON (b.NartNr = c.NartNr)
LEFT OUTER JOIN tbl_030 d ON (c.BezNr = d.BezNr)
LEFT OUTER JOIN tbl_325 e ON (b.VerwNr = e.VerwNr)
WHERE a.genartnr="34" AND a.krintnr="2" AND a.kritwert="12344" AND d.SprachNr='19' ORDER BY a.sortnr ASC
[SQL] pobierz, plaintext 

co do tego zapytanie ktore podaleś to mam 3 uwagi:

- dlaczego łączysz się z tabelą: tbl_325 skoro ona nie jest Ci na nic potrzebna ?
- dlaczego nie stosujesz INNER JOIN zamiast LEFT OUTER JOIN, przecież z zapytania jasno wynika, iż tak właśnie powinno być.
- czy pola typu: genartnr, krintnr, kritwert, SprachNr maja ustawiony typ tekstowy czy liczbowy ?, bo z tego zapytania wynika ze tekstowe

jeszcze jedna sprawa, sprawdz czy kolumny po ktorych laczysz tabele maja taki sam typ

Popieram Skobi co do typów kolumn, jak są tylko liczby to używaj liczbowego itd. czyli stosuj najprostszy typ to zmniejszy wielkość tabeli a to może przyspieszyć. Tak samo ze złączeniami, jeśli muszą być dopasowane dane, to rób INNER JOIN (czyli po prostu JOIN).
Dodam jedno od siebie, jeśli ten system ma działać na prawdę szybko, wyeliminuj wszystkie JOINy, robisz to w miarę prosto, tworzysz tabelę z wszystkimi kolumnami tak jakbyś zrobił JOINy, oczywiście to spowoduje wielokrotnie wystąpienie tych samych danych, ale jak na takiej tabeli założysz odpowiednie indeksy to będzie śmigać. Sprawa rozbija się o aktualizację danych, bo przy każdym zmodyfikowaniu tabel pierwotnych zachodzi niespójność danych z tą sztuczną tabelą, ale jeśli dobrze sądzę takie aktualizację danych nie występują, a jeśli jednak są to można to rozwiązać na parę sposobów.

Co do indeksów to jak masz pozakładane różne, potem przetestuj różne zapytania, i sprawdź z których na prawdę korzysta, resztę usuń. Jeśli gdzieś używa sekwencyjnego skanowania to sprawdź czy nie da się założyć indeksu, tak samo jeśli sortujesz po jakiejś kolumnie to użyj indeksu btree na tej kolumnie.

Co do usuwania Joinow to się z tym nie mogę zgodzić, dlatego iż model relacyjny nie został stworzony dlatego, żeby wszystko trzymać w jednej tabeli. Wszystkie dane muszą być odpowiednio znormalizowane.

Jeżeli bardzo proste zapytanie chodzi wolno, a są prawidłowo utworzone indexy to problem musi tkwić w innej sprawie.

Proponuję jeszcze wykonać polecenie:

[SQL] pobierz, plaintext 
VACUUM FULL ANALYZE
[SQL] pobierz, plaintext 

Można jeszcze pokombinować z optymalizacją zapytania, aby odpowiednie tabele łączyły się w kolejności w jaki chcemy, a nie w taki jaki chce optymalizator, być może to by rozwiązało problem.

@Skobi
Proste pytanie, ile razy zajmowałeś się bazami z dużą ilością danych i obciążeniem?

nie mam zamiaru wchodzić z Tobą w polemikę, bo wydaje mi się ona zbędna. Ale ok odpowiem na pytanie: miałem do czynienia z dużą bazą danych, a do tego sam z własnej inicjatywy interesowałem się optymalizacją zapytań.

Ale patrząc Twoim tokiem myślenia można by stwierdzić, iż wszystkie relacyjne bazy danych banków przetrzymujące miliardy rekordów składają się z jednej tabeli z nadmiarowymi danymi. Przecież to by był jakiś absurd.

Uważam z własnego doświadczenia, że jeżeli prawidłowo zaprojektowana baza z odpowiednio ułożonymi indeksami nie jest w stanie odpalić w odpowiednim czasie zapytania, to problem tkwi gdzie indziej, możliwe, że to sprawa konfiguracji samej bazy, bądź sprzęt jest nie wystarczający.
Natomiast uciekanie się do pakowania danych do jednej tabeli uważam za błędne podejście i nawet jeżeli rozwiązujące dany problem to w krótkim czasie dokładający nowych problemów z którymi osoba odpowiadająca za bazę danych będzie jej poświęcała coraz to więcej czasu.

Tylko nie bierzesz jednej bardzo ważnej rzeczy pod uwagę, a mianowicie infrastruktury. Bo nie powiesz mi że w bankach masz jeden dedykowany serwer gdzie siedzi baza ;]
Musisz pamiętać, że sposób rozwiązania zmienia się w zależności od wielu czynników, czasami trzeba denormalizować aby działało przyzwoicie.
Jestem osobą która raczej dąży do pełnej normalizacji, do tego tak oprogramowanej bazy, że zawsze jest zachowana logiczna poprawność danych (sprawdzenia/automatyczne aktualizacje/...). Ale nie zawsze da się tak to rozwiązać.
Więc powiem tak ogólnie zgadzam się z Tobą, ale tu mamy jeden składnik który zmienia postać rzeczy. Do tego w bankach nie używają PG tylko bardziej zaawansowanych baz danych, gdzie masz więcej możliwości konfiguracji, w PG nie możesz zmienić wielkości strony... (a chociaż 8.2 ja nie znalazłem o tym informacji, choć przyznam dokładnie nie szukałem)

Biorę pod uwagę to iż w bankach stoją dziesiątki serwerów, natomiast uważam, że jeżeli baza nie daje rade to trzeba zainwestować w dodatkowy serwer niż się posuwać do usuwania problemów drogą garażową, która w przyszłości okaże się drogą przez mękę.

pozdrawiam

zamiast left zostawiłem samo join i rzeczywiście w tym zapytaniu okazało się to kluczowe - bałem się ograniczenia wyników przez zastosowanie samego join ale produkcyjnie może to przejść, administracyjnie nie ale tam można chwilkę poczekać.
Zlikwidowałem jeden Seq Scan i mam same index scany.
Zapytanie trwa około 3 sec.

struktura zapytania podstawowego.

[SQL] pobierz, plaintext 
SELECT a.artnr, c.index1 AS dostawca_index,  d.nazwa, e.name AS suppname  FROM tecdoc_400c a 
JOIN dostawca_towary_pow c ON (a.artnr=c.artnr)
JOIN dostawca_towary d ON (c.index1=d.index1)
JOIN ec_suppilers e ON (a.dlnr=e.dlnr)
WHERE a.genartnr='7' AND a.kritnr='2' AND a.kritwert='1157' 
GROUP BY a.artnr, c.index1, d.nazwa, e.name, a.dlnr 
ORDER BY a.dlnr ASC
[SQL] pobierz, plaintext 

kluczowe pole artnr musi być string-iem niestety.

zakładam właśnie index-y dla kolumn gdzie stosuje order - może też coś zyskam.

- niestety niem mam wpływu na strukturę tabel więc nie mogę sobie robić tabel gdzie łącze dane, tym bardziej że cała struktura jest aktualizowana co dzień (stany magazynowe) i co 3 miesiące (cała struktura)




A jednak coś jest nie tak.

Pierwsze zapytanie trwa 90 sekund.
potem kolejne już mniej. Masakra.

Jeśli ktoś z Was jest chętny mi pomóc komercyjnie (odpłatnie) to zapraszam pod gg: 3427750