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);