Porównanie wydajności baz danych (MySQL, PostgreSQL)

Po wczorajszych testach, i pamiętnych flame’ach MySQL vs PostgreSQL, postanowiłem sprawdzić jak się mają te 2 bazy do siebie w kwestii prostych zapytań na tabelach z 2 mln wierszy każda (liczby i stringi), w przypadku indeksowanych kolumn, i nie.

Zapytanie	INDEX	MySQL	PostgreSQL
SELECT COUNT(id) FROM liczby		1 rows fetched (844 ms)	1 rows fetched (719ms)
SELECT id FROM liczby WHERE liczba = 67	NIE	55 rows fetched (1,078 s)	20533 rows fetched (735 ms)
SELECT id FROM liczby WHERE liczba = 67	TAK	55 rows fetched (141 ms)	20533 rows fetched (234 ms)
SELECT COUNT(id) FROM stringi;		1 rows fetched (859 ms)	1 rows fetched (828 ms)
SELECT id FROM stringi WHERE string=’HDDFBAGFHCABIBIAFAAE’;	NIE	1 rows fetched (1,172 s)	1 rows fetched (1,016 sec)
SELECT id FROM stringi WHERE string=’HDDFBAGFHCABIBIAFAAE’;	TAK	1 rows fetched (16 ms)	1 rows fetched (15 ms)

Dla tabel z 0.6 mln wierszy wyniki przedstawiają się następująco:

Zapytanie	INDEX	MySQL	PostgreSQL
SELECT COUNT(id) FROM liczby		1 rows fetched (265 ms)	1 rows fetched (188 ms)
SELECT id FROM liczby WHERE liczba = 67	NIE	55 rows fetched (312 ms)	6170 rows fetched (193 ms)
SELECT id FROM liczby WHERE liczba = 67	TAK	55 rows fetched (47 ms)	6170 rows fetched (31 ms)
SELECT COUNT(id) FROM stringi;		1 rows fetched (265 ms)	1 rows fetched (234 ms)
SELECT id FROM stringi WHERE string=’HDDFBAGFHCABIBIAFAAE’;	NIE	1 rows fetched (359 ms)	1 rows fetched (250 ms)
SELECT id FROM stringi WHERE string=’HDDFBAGFHCABIBIAFAAE’;	TAK	1 rows fetched (15 ms)	1 rows fetched (16 ms)

Jak widać, dla 2mln wierszy z warunek, gdzie kolumna jest nieindeksowana wygrywa PostgreSQL, przy kolumnie typu INTEGER widać znaczącą przewagę.
Ale jeśli już utworzymy indeksy, różnicy nie ma (typ VARCHAR), lub jest nieznaczna (INTEGER).

Sytuacja ma się zupełnie inaczej, jeśli mamy mniej niż 1 mln wierszy, w przykładzie ponad 0.6 mln.
W każdym przypadku PostgreSQL jest lepiej zoptymalizowany, nawet po utworzeniu indeksu dla kolumny INTEGER. Takie same wyniki (z przybliżeniem) są tylko dla kolumn indeksowanych VARCHAR.

Oczywiście czasy na poszczególnych maszynach, wersjach baz danych mogą się różnić. Testy robiłem wyłącznie na jednej maszynie, która nie jest wyłącznie przeznaczona na serwer bazodanowy.

Poniżej zastosowane funkcje dla PostgreSQL (wzorowane na poprzednich procedurach dla MySQL):

DROP FUNCTION IF EXISTS LOSOWO();

CREATE FUNCTION  LOSOWO()
RETURNS integer AS 'SELECT CAST((FLOOR(1 + RANDOM() * 99)) as integer)'
LANGUAGE sql;

DROP FUNCTION IF EXISTS WYPELNIJ_INT(IN ilosc INT);

CREATE FUNCTION  WYPELNIJ_INT(IN ilosc INT)
RETURNS integer AS $$
DECLARE i integer;
BEGIN
	i = 0;
	LOOP
		IF i &gt;= ilosc THEN
    		EXIT;
	    END IF;

		INSERT INTO liczby(liczba) VALUES(LOSOWO());
	    i=i+1;
	END LOOP;
	RETURN 0;
END;
$$ LANGUAGE plpgsql;

select WYPELNIJ_INT(2000000);

DROP FUNCTION IF EXISTS LOSOWE_ZNAKI();

CREATE FUNCTION LOSOWE_ZNAKI()
RETURNS VARCHAR(20) AS $$
DECLARE i integer;
DECLARE poz integer;
DECLARE wyjscie varchar(20);
BEGIN

wyjscie = '';
i = 0;
LOOP
	IF i &gt;= 20 THEN
    	EXIT;
    END IF;

	poz = FLOOR(1 + RANDOM() * 9);
    IF poz = 1 THEN
    	wyjscie = wyjscie || 'A';
    ELSEIF poz = 2 THEN
    	wyjscie = wyjscie || 'B';
    ELSEIF poz = 3 THEN
    	wyjscie = wyjscie || 'C';
    ELSEIF poz = 4 THEN
    	wyjscie = wyjscie || 'D';
    ELSEIF poz = 5 THEN
    	wyjscie = wyjscie || 'E';
    ELSEIF poz = 6 THEN
    	wyjscie = wyjscie || 'F';
    ELSEIF poz = 7 THEN
    	wyjscie = wyjscie || 'G';
    ELSEIF poz = 8 THEN
    	wyjscie = wyjscie || 'H';
    ELSEIF poz = 9 THEN
    	wyjscie = wyjscie || 'I';
    END IF;

    i=i+1;
END LOOP;
RETURN wyjscie;
END;
$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS WYPELNIJ_STR(IN ilosc INT);

CREATE FUNCTION  WYPELNIJ_STR(IN ilosc INT)
RETURNS INTEGER AS $$
DECLARE i INTEGER;
BEGIN

i=0;
LOOP
	IF i &gt;= ilosc THEN
    	EXIT;
    END IF;

	INSERT INTO stringi(string) VALUES(LOSOWE_ZNAKI());
    i=i+1;

END LOOP;
RETURN 0;
END;
$$ LANGUAGE plpgsql;

select WYPELNIJ_STR(600199);

DROP FUNCTION IF EXISTS LOSOWO();

CREATE FUNCTION LOSOWO()

RETURNS integer AS 'SELECT CAST((FLOOR(1 + RANDOM() * 99)) as integer)'

LANGUAGE sql;

DROP FUNCTION IF EXISTS WYPELNIJ_INT(IN ilosc INT);

CREATE FUNCTION WYPELNIJ_INT(IN ilosc INT)

RETURNS integer AS $$

DECLARE i integer;

BEGIN

i = 0;

LOOP

IF i >= ilosc THEN

EXIT;

END IF;

INSERT INTO liczby(liczba) VALUES(LOSOWO());

i=i+1;

END LOOP;

RETURN 0;

END;

$$ LANGUAGE plpgsql;

select WYPELNIJ_INT(2000000);

DROP FUNCTION IF EXISTS LOSOWE_ZNAKI();

CREATE FUNCTION LOSOWE_ZNAKI()

RETURNS VARCHAR(20) AS $$

DECLARE i integer;

DECLARE poz integer;

DECLARE wyjscie varchar(20);

BEGIN

wyjscie = '';

i = 0;

LOOP

IF i >= 20 THEN

EXIT;

END IF;

poz = FLOOR(1 + RANDOM() * 9);

IF poz = 1 THEN

wyjscie = wyjscie || 'A';

ELSEIF poz = 2 THEN

wyjscie = wyjscie || 'B';

ELSEIF poz = 3 THEN

wyjscie = wyjscie || 'C';

ELSEIF poz = 4 THEN

wyjscie = wyjscie || 'D';

ELSEIF poz = 5 THEN

wyjscie = wyjscie || 'E';

ELSEIF poz = 6 THEN

wyjscie = wyjscie || 'F';

ELSEIF poz = 7 THEN

wyjscie = wyjscie || 'G';

ELSEIF poz = 8 THEN

wyjscie = wyjscie || 'H';

ELSEIF poz = 9 THEN

wyjscie = wyjscie || 'I';

END IF;

i=i+1;

END LOOP;

RETURN wyjscie;

END;

$$ LANGUAGE plpgsql;

DROP FUNCTION IF EXISTS WYPELNIJ_STR(IN ilosc INT);

CREATE FUNCTION WYPELNIJ_STR(IN ilosc INT)

RETURNS INTEGER AS $$

DECLARE i INTEGER;

BEGIN

i=0;

LOOP

IF i >= ilosc THEN

EXIT;

END IF;

INSERT INTO stringi(string) VALUES(LOSOWE_ZNAKI());

i=i+1;

END LOOP;

RETURN 0;

END;

$$ LANGUAGE plpgsql;

select WYPELNIJ_STR(600199);

Aktualizacja (2009-06-19 14:20)
Polecam również MySQL vs PostgreSQL Benchmarks

Related