Jak postgresql przechowuje tabele

0

Nie mogę wyszukać infomracji o tym, jak postgres przechowuje dane w tabelach. Gdzieś słyszałem że
tworzy z automatu tabele jednokolumnowe. Jeśi to jest prawda, to próba rozbicia tabeli 20-kolumnowej na
np. 2 po 10 kolumn w istotny sposób nie wpłynie na wydajność, no może tylko trochę spowolni. Ale nie
wiem czy to jest prawda. Czy warto wyciągać dane po których jest sortowanie i na które są nakładane
warunki do oddzielnej tabeli?

1

Nie, dla takich danych warto tworzyć indeksy. Ewentualnie do osobnej tabeli można wyrzucić dane które są pobierane bardzo rzadko.

2

Co to znaczy, że "tworzy z automatu tabele jednokolumnowe"? Co masz na myśli?

Strzelam, ale wydaje mi się że ten ktoś, słyszał coś o tzw. WAL (Write-Ahead Logging) w PgSQL:
https://www.postgresql.org/docs/9.1/static/wal-intro.html
i konkretne zdanie z dokumentacji: The first two fields track the most recent WAL entry related to this page.
Tylko, że to ma wpływ na wydajność potwierdzania transakcji, ale czy na odczyt? Nie sądzę.
Ale mogę coś chlapnąć - na PgSQL się nie znam. Po prostu. Ale co nieco wiem o bazach relacyjnych w ogóle i w szczególe też o niektórych z nich...

Tak naprawdę, to w sumie nieważne jak PgSQL przechowuje dane.
Ważne, jak będziesz odpytywał o te dane.

20 kolumn to jest przysłowiowy pryszcz.
Ale już 20 kolumn x 20 mln wierszy pryszczem nie jest, zwłaszcza jeśli czas odpowiedzi ma być krótki. A jeśli do tego dochodzą zapytania agregujące, to drogi Kolego nie tędy droga. Mieszanie elementów hurtowni danych z bazą transakcyjną (w znaczeniu OLTP) to strzał w kolano. Pewnie, że zadziała - tylko jak? Fatalnie, zwłaszcza przy większym obciążeniu po stronie transakcji OLTP.

Czy warto przenieść dane do osobnej tabeli?
A czy wszystkie wartości w tych 20 kolumnach jest uzupełniona zawsze? Czy może tylko czasem?
Skoro chcesz sortować i wyszukiwać, wnioskuję że wartości są uzupełnione zawsze. Zatem nie warto bawić się w osobne tabele.
Rozbijesz na osobne tabele, będziesz miał dodatkowy narzut na złączenia. Jasne, można to napisać sprytnie, ale... nie warto się z tym kopać.

Skoro niepotrzebne Ci wszystkie dane z tej tabeli, to ich po prostu nie pobieraj. Lepiej mieć dwa zapytania lub nawet dedykowane procedury po stronie bazy, niż bawić się w taką organizację danych.
Jeśli części danych nie potrzebujesz często, a aktualizowane są zawsze, to nie warto ich przerzucać do osobnej tabeli - bo i po co?
Co przez to mielibyśmy uzyskać, skoro podobne efekty uzyskamy przez modyfikację pól które wybieramy?
Jasne do się da zbadać i przetestować, sam jestem ciekawy jak przedstawiają się statystki disk IO przy organizacji danych jedna vs wiele tabel.
Ale...

Pewnie można pewnie uzyskać jakieś tam fantomowe przyrosty wydajności przez inną organizację danych.
Ale na moje oko, to Twój problem nie polega na tym że PgSQL działa tak czy inaczej. Osobiście szukałbym go pomiędzy klawiaturą a głową.
Innymi słowy, mam wrażenie że próbujesz zmusić bazę danych do takiego działania, do którego ona nie bardzo się nadaje - dla mnie szczytem głupoty jest distinct po wszystkich polach tabeli. To jest prawdziwy WTF.

Zatem, co dokładnie chcesz osiągnąć i jak kombinujesz? Z czym masz największe problemy i dlaczego uważasz, że to problem?
Zazwyczaj problemy wydajnościowe wynikają z niezrozumienia podstw działa baz relacyjnych.
Ale więcej problemów widziałem, przez fatalnie napisany kod po stronie klienta - nie bazy danych.
To taki kod, który pobiera za dużo danych, zbyt często, otwiera kursor po stronie serwera i mieli wszystko na kliencie, itd.

0
wloochacz napisał(a):

Co to znaczy, że "tworzy z automatu tabele jednokolumnowe"? Co masz na myśli?

Możemy mieć układ w tabeli w jednym pliku:
poel1
pole2
pole3
pole1
pole2
pole3

Albo w trzech plikach
1) pierszy plik
pole1
pole1
2) drugi plik
pole 2
pole 2
3) trzeci plik
pole 3
pole 3

Jest to stara jak świat technika optymalizacyjna. Stosuję się ją nie tylko w bazach danych, ale także w programach
działających na pamięci RAM też ona może dawać (i zwykle daje) zysk wydajnościowy. Gdy mamy w tabeli
przykładowo 3 pola, a zapytanie zazwyczaj wygląda tak:

select from table pole1 = coś;
select
from table pole2 = coś;
select * from table pole3 = coś;

To w każdym z tych trzech zapytań, w przypadku podziału na osobne pliki, program musi przewalić mniej danychy. Gdy pole1,
pole2, pole3 są typu int32 (czyli są równego rozmiaru) to program musi przewalić trzy razy mniej danych niż gdy dane są w
jednym pliku.

Co ciekawe, gdy zapytanie jest typu:
select * from table pole1 = cos and pole2 = cos and pole3 = cos;
to na wydajności tracimy bardzo niewiele.

Słyszałem że postgres z automatu dzieli tabelę na tabele jednokolumnowe, więc trzymanie w osobnych tabelach pól na
które najczęściej zakłada się warunki żadnego zysku nie przyniesie.

wloochacz napisał(a):

Strzelam, ale wydaje mi się że ten ktoś, słyszał coś o tzw. WAL (Write-Ahead Logging) w PgSQL:
https://www.postgresql.org/docs/9.1/static/wal-intro.html
i konkretne zdanie z dokumentacji: The first two fields track the most recent WAL entry related to this page.
Tylko, że to ma wpływ na wydajność potwierdzania transakcji, ale czy na odczyt?
Nie sądzę.
Ale mogę coś chlapnąć - na PgSQL się nie znam. Po prostu. Ale co nieco wiem o bazach relacyjnych w ogóle i w szczególe też o niektórych z nich...

Nie, nie chodziło o WAL. Chodziło o przyspieszenie odczytów.

wloochacz napisał(a):

Tak naprawdę, to w sumie nieważne jak PgSQL przechowuje dane.
Ważne, jak będziesz odpytywał o te dane.

Rozumiem że to jakieś przejęzyczenie i że w ogole dobrze wiesz jak istotna jest struktura danych dla wydajności.

wloochacz napisał(a):

20 kolumn to jest przysłowiowy pryszcz.
Ale już 20 kolumn x 20 mln wierszy pryszczem nie jest, zwłaszcza jeśli czas odpowiedzi ma być krótki.

Nie rozumiem dlaczego 20x2mln to nie jest pryszcz dla baz danych. Mój domowy komputer przetwarza może 5109
intów na sekundę. Gdy mam 20mln wierszy zbuforowanych w całości w pamięci RAM, gdy zapytanie zakładam np.
na trzy kolumny typu int, to mam 2010^6 3, więc zapytanie powinno zająć 10-20ms... przy metodzie full-scan!

wloochacz napisał(a):

A jeśli do tego dochodzą zapytania agregujące, to drogi Kolego nie tędy droga. Mieszanie elementów hurtowni danych z bazą transakcyjną (w znaczeniu OLTP) to strzał w kolano. Pewnie, że zadziała - tylko jak? Fatalnie, zwłaszcza przy większym obciążeniu po stronie transakcji OLTP.

Chcę zoptymalizować poniższe (przykładowe) zapytanie, żadnej agregacji:
select * from table where pole1=5 and pole2=12 and pole3 in ( tutaj zwykle 20-40 intów ) and pole4 < 1000000 order by pole4 desc limit 10;

Widzę że postgres pięknie korzysta z indesu założonego na pola: pole1, pole2, pole3, pole4.
Niestety robi pętlę dla warunku: pole3 in (...). Jedno zapytanie w pętli trwa pewnie kilkadziesiąt ms. Całe zapytanie trwa najkrócej
500ms, czasami nawet 10s. Moim zdaniem dla 20mln czas takiego zapytanie (gdy baza jest w buforze ram) powinien wynosić góra 10ms.

wloochacz napisał(a):

Czy warto przenieść dane do osobnej tabeli?
A czy wszystkie wartości w tych 20 kolumnach jest uzupełniona zawsze? Czy może tylko czasem?
Skoro chcesz sortować i wyszukiwać, wnioskuję że wartości są uzupełnione zawsze. Zatem nie warto bawić się w osobne tabele.
Rozbijesz na osobne tabele, będziesz miał dodatkowy narzut na złączenia. Jasne, można to napisać sprytnie, ale... nie warto się z tym kopać.

Uzupełniane byłyby prawie zawsze, ale chodzi o optymalizację odczytu.

wloochacz napisał(a):

Skoro niepotrzebne Ci wszystkie dane z tej tabeli, to ich po prostu nie pobieraj.

Dane w pozostałych kolumnach są bardzo potrzebne, ale tylko do odczytu i wyświetlania. Dane z pozostałych
kolumn nie biorą tylko udział w wyszukiwaniu.

wloochacz napisał(a):

Lepiej mieć dwa zapytania lub nawet dedykowane procedury po stronie bazy, niż bawić się w taką organizację danych.
Jeśli części danych nie potrzebujesz często, a aktualizowane są zawsze, to nie warto ich przerzucać do osobnej tabeli - bo i po co?
Co przez to mielibyśmy uzyskać, skoro podobne efekty uzyskamy przez modyfikację pól które wybieramy?
Jasne do się da zbadać i przetestować, sam jestem ciekawy jak przedstawiają się statystki disk IO przy organizacji
danych jedna vs wiele tabel.
Ale...

Na mojej bazie trudno eksperymentować, ale w trakcie pisania odpowiedzi wpadł mi do głowy pomysł na uproszczony test.
Zobaczymy.

wloochacz napisał(a):

Pewnie można pewnie uzyskać jakieś tam fantomowe przyrosty wydajności przez inną organizację danych.
Ale na moje oko, to Twój problem nie polega na tym że PgSQL działa tak czy inaczej. Osobiście szukałbym go pomiędzy klawiaturą a głową.
Innymi słowy, mam wrażenie że próbujesz zmusić bazę danych do takiego działania, do którego ona nie bardzo się nadaje - dla mnie szczytem głupoty jest distinct po wszystkich polach tabeli. To jest prawdziwy WTF.

Dla mnie też to jest szczyt głupoty, nie ja wtedy wymyśliłem to zapytanie z DISTINCT, ale teraz distinct to już przeszlość.
Przypomnę raz jeszcze, teraz chodzi o optymalizację takiego zapytaia:
select * from table where pole1=5 and pole2=12 and pole3 in ( tutaj zwykle 20-40 intów ) and pole4 < 1000000 order by pole4 desc limit 10;

wloochacz napisał(a):

Zatem, co dokładnie chcesz osiągnąć i jak kombinujesz? Z czym masz największe problemy i dlaczego uważasz, że to problem?

Konkretnie teraz to mam problem ze zoptymalizowaniem powyższego zapytania.

A generalnie w przypadku baz danych największy problem mam ze zrozumieniem, dlaczego tak wolno działają,
nawet gdy cała baza jest zbuforowana w pamięci RAM. Z porównania wydajności procesora i szybkości baz
danych wynika, że bazy gdzieś tracą 99.9% mocy obliczeniowej. No i nigdy nie wiem czy ja się nie znam na
bazach danych i nie umiem zoptymalizować, czy bazy danych są do d**y.

wloochacz napisał(a):

Zazwyczaj problemy wydajnościowe wynikają z niezrozumienia podstw działa baz relacyjnych.
Ale więcej problemów widziałem, przez fatalnie napisany kod po stronie klienta - nie bazy danych.
To taki kod, który pobiera za dużo danych, zbyt często, otwiera kursor po stronie serwera i mieli wszystko na kliencie, itd.

W tym momencie u mnie zapytanie za długo trwa. Proste i konkretne zapytanie. Pobiera 10 wierszy z bazy, te 10 wierszy to
jakieś 10kb danych. Transfer danych na pewno nie jest wąskim gardłem. Wąskim gardłem jest przetwarzanie danych przez
silnik bazodanowy. Nie otwieram żadnych kursorów. Mam założonych kilkanaście indeksów, jeden indeks do jednego
wariantu tego zapytania które wkleiłem, z explain wynika, że postgres w każdym wariancie używa indeksów. Zresztą tak
jak wyżej napisałem, 20mln rekordów w RAM to można na fullskanie przetworzyć w parę milisekund, a na indeksach to
powinno trwać microsekundy. Nie wiem czy ja czegoś nie rozumiem... Moim zdaniem bazy danych kiepsko radzą sobie z
indeksami i z jakiś powodów bardzo wolno przetwarzają dane - nie wiem z jakich powdów.

Pozdrawiam

0
  1. Skąd pomysł że cała baza jest w ramie?

No i nigdy nie wiem czy ja się nie znam na bazach danych i nie umiem zoptymalizować, czy bazy danych są do d**y.

Obawiam sie że jednak opcja numer 1 ;)

20mln rekordów w RAM to można na fullskanie przetworzyć w parę milisekund

Nie wiesz o czym mówisz. Procesor ma powiedzmy 3GHz więc może w ciagu sekundy wykonać 3 milardy cykli. Milisekunda to 10-3 więc w ciągu milisekundy procesor może wykonać 3 miliony cykli zegarowych. Jedna instrukcja procesora to zwykle przynajmniej kilka-kilkanaście cykli. Powiedzmy optymistycznie że wykonasz nie wiecej niż milion operacji asemblera, a gdzie tu jeszcze żeby wykonać jakieś sensowne porównanie indeksów bazodanowych - każda taka złożona operacja będzie wymagała sporo operacji na poziomie CPU, więc znów spadamy przynajmniej o rząd wielkości a ty przecież dla każdego rekordu masz kilka porównań więc znów kolejny rząd wielkosci mniej...
To wszystko już przy optymistycznym założeniu że sobie CPU ładnie wszystko będzie wczytywać przez read-ahead do cache, bo jak zacznie czytać z RAMu to kolejne rzędy wielkości wolniej...

Indeksy to też nie jest jakiś silver bullet i trzeba umieć ich używać. Może tak być że operacja z indeksem będzie działać wolniej niż bez niego, bo użycie indeksu będzie powodowało ciągłe cache miss przez skakanie po pamięci / doczytywanie danych z dysku. Dodatkowo warto rozumieć że masz takie coś jak indeks primary i secondary. Primary może być tylko jeden i jest zgodny z ułożeniem danych na dysku i jest generalnie dużo lepszy niż indeks secondary przy niektórych operacjach.

To że na koniec robisz limit 10 nie ma żadnego znaczenia bo baza i tak musi wszystkie te rekordy przetworzyć. Pytanie brzmi jak mocno te twoje warunki ogarniczają zbiór danych, bo ja przypuszczam że prawie wcale i robisz potem sortowanie (bardzo kosztowne!) gigantycznego zbioru danych. Pomyślałbym o indeksie primary na to twoje pole4 skoro chcesz po nim sortować miliony rekordów.

0
Shalom napisał(a):
  1. Skąd pomysł że cała baza jest w ramie?

No i nigdy nie wiem czy ja się nie znam na bazach danych i nie umiem zoptymalizować, czy bazy danych są do d**y.

Obawiam sie że jednak opcja numer 1 ;)

20mln rekordów w RAM to można na fullskanie przetworzyć w parę milisekund

Nie wiesz o czym mówisz. Procesor ma powiedzmy 3GHz więc może w ciagu sekundy wykonać 3 milardy cykli. Milisekunda to 10-3 więc w ciągu milisekundy procesor może wykonać 3 miliony cykli zegarowych. Jedna instrukcja procesora to zwykle przynajmniej kilka-kilkanaście cykli.

To już procesory nie potrafią wykonać kilku instrukcji w jednym cyklu zegarowym?

0
wloochacz napisał(a):

Jasne do się da zbadać i przetestować, sam jestem ciekawy jak przedstawiają się statystki disk IO przy organizacji danych jedna vs wiele tabel.

Jestem w trakcie testów. Z dodaniem do tabeli każdego pola wydajność minimalnie spada, pomimo że to pole całkowicie jest
nieużywane. Wydajność spada zarówno na zapytaniu z indeksem jak i bez. Wniosek z tego taki, że gdy ma się w rekordzie
dużo danych (przynajmniej 500-1000 bajtów) a filtruje/sortuje się tylko po kilku polach typu int lub krótki char, warto rozważyć
wyrzucenie części danych do osobnej tabeli. Niestety potem będzie trzeba wykonać dodatkowe zapytanie w celu dociągnięcia
danych z dodatkowej tabeli, więc trzeba sprawdzić czy w sumie to się opłaca.

0

To już procesory nie potrafią wykonać kilku instrukcji w jednym cyklu zegarowym?

Nie bo to fizycznie niemożliwe o_O Procesory są superskalarne i mają kilka jednostek wykonawczych a dodatkowo mogą wykonywać poszczególne kroki (fetch, decode, execute) w pipeline i w efekcie mogą teoretycznie "wykonać" kilka instrukcji w jednym cyklu, ale to jest szczególny przypadek kiedy akurat da się wszystko ładnie ułożyć.

Dodatnie nowego pola zwyczajnie powoduje zwiększenie rozmiaru rekordu na stronie co skutkuje szybszym zapychaniem cache, tzn mniej rekordów tam na raz wejdzie. Jak będziesz to potem joinował to się nie będzie specjalnie opłacać.

Zrób count na tym twoim zapytaniu i zobacz ile rekordów tam sortujesz.

0
Shalom napisał(a):
  1. Skąd pomysł że cała baza jest w ramie?

No i nigdy nie wiem czy ja się nie znam na bazach danych i nie umiem zoptymalizować, czy bazy danych są do d**y.

Obawiam sie że jednak opcja numer 1 ;)

20mln rekordów w RAM to można na fullskanie przetworzyć w parę milisekund

Nie wiesz o czym mówisz. Procesor ma powiedzmy 3GHz więc może w ciagu sekundy wykonać 3 milardy cykli. Milisekunda to 10-3 więc w ciągu milisekundy procesor może wykonać 3 miliony cykli zegarowych. Jedna instrukcja procesora to zwykle przynajmniej kilka-kilkanaście cykli. Powiedzmy optymistycznie że wykonasz nie wiecej niż milion operacji asemblera, a gdzie tu jeszcze żeby wykonać jakieś sensowne porównanie indeksów bazodanowych - każda taka złożona operacja będzie wymagała sporo operacji na poziomie CPU, więc znów spadamy przynajmniej o rząd wielkości

Nie mam pojęcia o jakich rzędach wielkości mówisz. Jeśli ktoś tutaj nie wie co mówi, to nie jestem to ja. Nie lubię być gołosłowny, więc
pomiar czasu w C++. Program w C++, odpowiedni zapytania na fulscanie, bez indeksów, 20mln rekordów:

select sum(value) from table where pole1 = 12 and pole2 = 14 and pole3 in ( tutaj 10 intów );

Wyniki: zapytanie trwa około 40ms. Powtarzam: na fulscan, bez indeksów. Z indeksami pewnie by trwało ze 100 razy szybciej.

Źródło, kompilacja i sposób pomiaru czasu:

// g++ -O3 main.cpp -o if3
// time ./if3
// sum = 96621642
//
// real    0m42.073s
// user    0m41.763s
// sys     0m0.324s
// 41.763s / 1000 zapytań = 41.763ms na jedno zapytanie przy fulscanie 20mln rekordów w ram.

#include <cstdio>
#include <cstdlib>

#define SIZE (20 * 1000000)

struct IF3 {
    int id;
    int value;
    int pole1;
    int pole2;
    int pole3;
};

static void init( IF3 if3[] ) {
    for( int i=0 ; i<SIZE ; i++ ) {
        if3[i].id = i+1;
        if3[i].value = rand() % 1024;
        if3[i].pole1 = rand() % 32;
        if3[i].pole2 = rand() % 32;
        if3[i].pole3 = rand() % 1024;
    }
}

static int test1( const IF3 if3[] , const int v1 , const int v2) {
    int sum = 0;
    bool in[1024];
    for( int i=0 ; i<1024 ; i++ )
        in[i] = false;
    in[ 10] = true;
    in[ 20] = true;
    in[ 31] = true;
    in[101] = true;
    in[115] = true;
    in[201] = true;
    in[217] = true;
    in[301] = true;
    in[380] = true;
    in[401] = true;
    for( int i=0 ; i<SIZE ; i++ ) {
        if( in[ if3[i].pole3 ] && if3[i].pole1 == v1 && if3[i].pole2 == v2 )
            sum += if3[i].value;
    }
    return sum;
}

int main(int argc, char *argv[])
{
    IF3 *const if3 = new IF3[SIZE];
    init( if3 );
    int sum = 0;
    for( int i=0 ; i<1000 ; i++ )
        sum += test1( if3 , (i+5) % 32 , (i*5+4) % 32 );
    delete[] if3;
    printf("sum = %d\n",sum);
    fflush(stdout);
    return 0;
}
Shalom napisał(a):

To już procesory nie potrafią wykonać kilku instrukcji w jednym cyklu zegarowym?

Nie bo to fizycznie niemożliwe o_O Procesory są superskalarne i mają kilka jednostek wykonawczych a dodatkowo mogą wykonywać poszczególne kroki (fetch, decode, execute) w pipeline i w efekcie mogą teoretycznie "wykonać" kilka instrukcji w jednym cyklu, ale to jest szczególny przypadek kiedy akurat da się wszystko ładnie ułożyć.

Kiedyś czytałem książkę 'procesory pentium' i tam gość opisywał jak optymalizować w asemblerze aby uzyskać efekt
wykonania kilku instrukcji w jednym takcie. Czy coś pomyliłem, czy nie, to w poście powyżej wkleilem program, w
którym procesor wykonał 20mln pętli w języku wysokiego poziomu w 40ms na dużej pamięci która na pewno nie
mieści się cała w cache procesora. To daje pół miliarda pętli na sekundę. Nie na ile instrukcji asemblera przekłada
się ta pętla, ale jeśli średnio na 10, to daje 5mld operacji na sekundę, a zegar w moim procesorze ma 2.3GHz.

Dodatnie nowego pola zwyczajnie powoduje zwiększenie rozmiaru rekordu na stronie co skutkuje szybszym zapychaniem cache, tzn mniej rekordów tam na raz wejdzie. Jak będziesz to potem joinował to się nie będzie specjalnie opłacać.

Z Joinem na pewno nie będzie się opłacało. ALe jeśli wyciągnę 10 id z jednej tabeli i potem wykonam drugie zapytanie do
drugiej tabeli aby tylko dociągnąć dane, to widzę, że czasami może się opłacać i to bardzo.

Zrób count na tym twoim zapytaniu i zobacz ile rekordów tam sortujesz.

</quote>
Już mi się nie chciało tam odpisywać o sortowaniu, ale błędnie doszukujesz się ogromnego narzutu przy sortowaniu. Jeśli
robimy zapytanie na tabeli która ma N rekordów z "order i limit M", to czas zapytania wynosi N * Log M. Gdy limit jest 10, to
czas jest praktycznie liniowy względem ilości rekordów. I teraz uwaga: gdy nie ma indeksów! Gdy są indeksy to skacze w
2-3 dostępach do pamięci do strony zawierającej pierwszy rekord i czas wynsoi około Log N + M (tak, tam jest plus a nie mnożenie).

0

@artur_bredzki szkoda z tobą dyskutować po tym poście z tablicami bo udowodniłeś kolejny raz że nie masz pojęcia o czym mówisz.

Program w C++, odpowiedni zapytania na fulscanie, bez indeksów, 20mln rekordów:

Ani trochę. Ani w minimalnym stopniu nie ma to nic wspólnego z bazodanowym zapytaniem. Wiedziałbyś o tym gdybyś przeczytał link który podałem na początku tego tematu.

Kiedyś czytałem książkę 'procesory pentium' i tam gość opisywał jak optymalizować w asemblerze aby uzyskać efekt wykonania kilku instrukcji w jednym takcie

O tym też wspomniałem. Ale to wymaga bardzo konkretnego ułożenia instrukcji. Jak to będzie jakieś SIMD (jak twój kod) to można uzyskać taki efekt, ale baza danych wykonuje dużo innych operacji w międzyczasie i to sie nie uda. Ja rozumiem że dla twojego konkretnego zapytania to może i byłoby zbędne, ale silnik bazodanowy działa dla przypadku ogólnego. To oznacza ze dla konkretnej operacji zadziała wolniej niż customowy kod.

na dużej pamięci która na pewno nie mieści się cała w cache procesora

Wręcz przeciwnie. Napisałeś kod który iteruje po kolei bo ciągłym bloku pamięci w kierunku rosnących adresów, więc oczywiście że wszystko zmieści sie w cache poprzez read-ahead.

Jeśli robimy zapytanie na tabeli która ma N rekordów z "order i limit M", to czas zapytania wynosi N * Log M

To ciekawe. Jakieś źródło/nazwa algorytmu który wykonuje takie cuda? Bo standardowe n-th element jest O(n) więc wybranie M elementów da O(n*m) jeśli już, niemniej faktycznie dla odpowiednio małego M wynik jest liniowy.

Baza danych ma dane ułożone zgodnie z indeksem primary ale jeśli wykonujesz operacje za pomocą wielu indeksów to będziesz iterować po rekordach w losowych miejscach w pamięci/na dysku.

Moja rada: spróbuj założyć indeks primary na pole4 w swoim zapytaniu i zobacz jaki będzie efekt i ewentualnie zrób to samo usuwając pozostałe indeksy. Ale nie spodziewaj sie ze będzie to działać tam jak ten twój śmieszny tablicowy kod.

1 użytkowników online, w tym zalogowanych: 0, gości: 1, botów: 0