PostgreSQL: Jak zoptymalizować moją bazę danych do przechowywania i odpytywania ogromnego wykresu
Używam PostgreSQL 8.3 na Intel Core Duo Mac Mini 1,83 GHz z 1 GB pamięci RAM i Mac OS X 10.5.8. Mam zapisany ogromny wykres w mojej bazie PostgreSQL. Składa się z 1,6 miliona węzłów i 30 milionów krawędzi. Mój schemat bazy danych wygląda następująco:
CREATE TABLE nodes (id INTEGER PRIMARY KEY,title VARCHAR(256));
CREATE TABLE edges (id INTEGER,link INTEGER,PRIMARY KEY (id,link));
CREATE INDEX id_idx ON edges (id);
CREATE INDEX link_idx ON edges (link);
Dane w krawędziach tabeli wyglądają jak
id link
1 234
1 88865
1 6
2 365
2 12
...
Przechowuje więc dla każdego węzła o identyfikatorze x x link wychodzący do id y.
Czas wyszukiwania wszystkich linków wychodzących jest dobry:
=# explain analyze select link from edges where id=4620;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using id_idx on edges (cost=0.00..101.61 rows=3067 width=4) (actual time=135.507..157.982 rows=1052 loops=1)
Index Cond: (id = 4620)
Total runtime: 158.348 ms
(3 rows)
Jeśli jednak szukam linków przychodzących do węzła, baza danych jest ponad 100 razy wolniejsza (chociaż wynikająca z tego liczba linków przychodzących jest tylko 5-10 razy większa niż liczba linków wychodzących):
=# explain analyze select id from edges where link=4620;
QUERY PLAN
----------------------------------------------------------------------------------
Bitmap Heap Scan on edges (cost=846.31..100697.48 rows=51016 width=4) (actual time=322.584..48983.478 rows=26887 loops=1)
Recheck Cond: (link = 4620)
-> Bitmap Index Scan on link_idx (cost=0.00..833.56 rows=51016 width=0) (actual time=298.132..298.132 rows=26887 loops=1)
Index Cond: (link = 4620)
Total runtime: 49001.936 ms
(5 rows)
Próbowałem zmusić Postgres do nieużywania skanowania bitmap przez
=# set enable_bitmapscan = false;
ale szybkość zapytania o linki przychodzące nie uległa poprawie:
=# explain analyze select id from edges where link=1588;
QUERY PLAN
-------------------------------------------------------------------------------------------
Index Scan using link_idx on edges (cost=0.00..4467.63 rows=1143 width=4) (actual time=110.302..51275.822 rows=43629 loops=1)
Index Cond: (link = 1588)
Total runtime: 51300.041 ms
(3 rows)
Zwiększyłem także moje współdzielone bufory z 24 MB do 512 MB, ale to nie pomogło. Zastanawiam się więc, dlaczego moje zapytania dotyczące linków wychodzących i przychodzących pokazują takie asymetryczne zachowanie? Czy coś jest nie tak z moim wyborem indeksów? Czy powinienem lepiej stworzyć trzecią tabelę zawierającą wszystkie przychodzące linki dla węzła o identyfikatorze x? Ale to byłaby dość strata miejsca na dysku. Ale odkąd jestem nowym użytkownikiem baz danych SQL, może brakuje mi tutaj czegoś podstawowego?