Optimieren durch Messen und Vergleichen#
Zeit ist Geld, Ausführungszeit wird in Datenbanken auch mit »Kosten« bezeichnet. Die Frage, warum etwas wie lange dauert, kann durch Vergleiche ermittelt werden. Zwei Messgrößen stehen zur Verfügung:
Zeitmessungen
query plan
Timing aktivieren/deaktivieren#
\timing
Tabelle anlegen#
CREATE TABLE experiment (
id INT PRIMARY KEY
, name TEXT NOT NULL
)
Tabelle füllen mit Zufallswerten#
INSERT INTO experiment SELECT n , md5 (random()::text)
FROM generate_series (1, 100000) AS wert(n);
Den Ausführungsplan abrufen#
analyze experiment;
Ausführungsplan für ein Statement#
EXPLAIN SELECT * from experiment;
EXPLAIN SELECT * from experiment;
QUERY PLAN
-------------------------------------------------------------------
Seq Scan on experiment (cost=0.00..1834.00 rows=100000 width=37)
^ ^ ^ ^
| | | |
Schätzung für die erste Zeile --o | | |
Schätzung für alle Zeilen ------------o | |
Zeilen die selektiert werden mussten ---------------o |
durchschnittliche Länge eines Tupel ---------------------------o
Passworte vergleichen#
optimieren des Index
zufällige Werte#
CREATE OR REPLACE FUNCTION generate_random_text ( int ) RETURNS TEXT AS
$$
SELECT string_agg(substr('0123456789abcdefghijklmnopqrstuvwxyzABCDEFGH
IJKLMNOPQRSTUVWXYZ', trunc(random() * 62)::integer + 1, 1), '') FROM
generate_series(1, $1)
$$
LANGUAGE SQL;
Passwort-Tabelle anlegen und füllen#
CREATE TABLE login as SELECT n, generate_random_text(8) as login_name
FROM generate_series(1, 1000) as foo(n);
VACUUM ANALYZE login;
Vergleich I#
suchen Sie sich einen Werte aus der Tabelle raus…
EXPLAIN SELECT * FROM login WHERE login_name = '5OsXXHuP';
EXPLAIN SELECT * FROM login WHERE login_name = lower('5OsXXHuP');
EXPLAIN SELECT * FROM login WHERE lower(login_name) = lower('5OsXXHuP');
-- oder mit der Timing-Funktion
SELECT * FROM login WHERE login_name = '5OsXXHuP';
SELECT * FROM login WHERE login_name = lower('5OsXXHuP');
SELECT * FROM login WHERE lower(login_name) = lower('5OsXXHuP');
Nun mit index#
CREATE INDEX ON login(lower(login_name));
und die drei Statements nochmal#
SELECT * FROM login WHERE login_name = '5OsXXHuP';
SELECT * FROM login WHERE login_name = lower('5OsXXHuP');
SELECT * FROM login WHERE lower(login_name) = lower('5OsXXHuP');
JOIN statt EXISTS#
Tabelle anlegen#
CREATE TABLE experiment_b AS
SELECT n as id, md5(n::text) as name
FROM generate_series(1, 1000) as wert(n);
Auswahl mit select (EXISTS)#
SELECT * FROM experiment
WHERE EXISTS (SELECT 1 FROM experiment_b
WHERE id = id);
Auswahl mit select (JOIN)#
SELECT experiment.* FROM experiment
INNER JOIN experiment_b
USING (id);
-- oder
SELECT * FROM experiment
WHERE id IN (SELECT id FROM experiment_b);