W nawiązaniu do wątku "OpenSource sux" przetestowałem jak radzi sobie PostgreSQL z tabelą 50 mln rekordów (ok 2-3 GB danych, z indeksami zjadlo mi ok. 5 GB wolnego miejsca z dysku).
CREATE TABLE transakcja (id_transakcji INT4 PRIMARY KEY, data TIMESTAMP, kwota FLOAT);
COPY transakcja (id_transakcji, data, kwota) FROM stdin;
1 2004-08-12 150
2 2003-01-10 331
3 2005-11-02 24
....
itd. lacznie 50 milionow rekordow
"data" losowa z przedziału od 3 lata wstecz do dzisiaj. "kwota" losowa z przedzialu 0 - 500.
Czas zaladowania danych do bazy: 12 minut 19 sekund.
Pierwsze naiwne zapytanie bez optymalizacji:
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
180,0 sekund; baza wykonala sekwencyjny skan całej tabeli
CREATE INDEX idx_transakcja_data_kwota ON transakcja USING btree(data, kwota);
ok. 7 minut
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
89,5 sekundy; baza użyła indeksu
CREATE INDEX idx_transakcja_kwota ON transakcja USING btree(kwota);
ok. 6 minut
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
84 sekundy; baza nie użyła nowego indeksu
DROP INDEX idx_transakcja_data_kwota;
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50;
81,5 sekundy; baza użyła indeksu idx_transakcja_kwota
Ale to wszystko nic...
Dalej było ciekawiej:
ALTER TABLE transakcja ADD COLUMN miesiac INT4;
UPDATE transakcja SET miesiac = extract(year from data) * 12 + extract(month from data);
CREATE TABLE transakcja_tmp AS SELECT id_transakcji, data, miesiac, kwota FROM transakcja ORDER BY miesiac, kwota;
DROP TABLE transakcja;
ALTER TABLE transakcja_tmp RENAME TO transakcja;
CREATE INDEX idx_transakcja_miesiac_kwota ON transakcja USING btree(miesiac, kwota);
wszystko łącznie koło 40 minut.
Ale za to teraz:
SELECT count(*) FROM transakcja WHERE data > '2005-09-09' AND kwota < 50 AND miesiac >= (2005 * 12 + 9);
19,5 sekundy (agregacja z 1,6 mln rekordow)
A np. teraz analiza dla jednego wybranego miesiaca:
select count(*) from transakcja where data > '2005-09-09' and data < '2005-10-09' and miesiac >= (9 + 2005 * 12) and miesiac <= (10 + 2005 * 12) and kwota < 50;
1,9 sekundy (agregacja ze 136 tys. rekordow)
Dość mocne - zważywszy, że to 136 tys. rekordów z 50 mln...
Na dodatek, jeśli np. po miesiącu dojdą nowe wpisy, to wystarczy teraz zrobić:
CLUSTER idx_transakcja_miesiac_kwota ON transakcja; (ok. 6 minut)
żeby zapytania stopniowo się nie spowalniały.
Jestem ciekaw, na ile da sie zoptymalizowac MS SQL, Oracla pod tym wzgledem a na ile MySQL.
Czy czasy beda tego samego rzedu, czy np. MS SQL smignie to zapytanie 10 razy szybciej na podobnym sprzecie? :>
Moj sprzet: AMD Athlon 64+ @2,75 GHz, 1GB DDR RAM @220 MHz, 250 GB HDD SATA UDMA 133.
PostgreSQL 8.1.3, dostepne mial 500 MB RAMu (ustawione w konfiguracji).
P.S. Typowe zapytania transakcyjne zwracajace kilka a nie kilkaset tysiecy rekordow zajmuja Postgresowi czas rzedu kilkunastu milisekund jesli dane NIE BYLY w cache'u i ulamki milisekundy, jesli byly (za drugim razem). Wiec przy takich zapytaniach wielkosc bazy praktycznie nie ma znaczenia.