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ühungsplan 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 werde mussten ----------------o |
durchschnittliche Länge eines Tupel ---------------------------o
EXPLAIN SELECT * FROM experiment a
JOIN experiment b ON (a.id = b.id) WHERE a.id < 1000;
GEHT-NICHT Order By in einer View ist nicht gut¶
Erster Versuch¶
CREATE OR REPLACE VIEW v_experiment AS
SELECT *
FROM experiment;
EXPLAIN SELECT *
FROM v_experiment order by id ASC;
QUERY PLAN
-------------------------------------------------------------------------------------------
Index Scan using experiment_pkey on experiment (cost=0.29..3441.29 rows=100000 width=37)
(1 row)
zweiter Versuch¶
CREATE OR REPLACE VIEW v_experiment AS
SELECT *
FROM experiment
ORDER BY id DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Sort (cost=15483.11..15733.11 rows=100000 width=37)
Sort Key: experiment.id
-> Index Scan Backward using experiment_pkey on experiment (cost=0.29..3441.29 rows=100000 width=37)
(3 rows)
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);
CREATE INDEX ON login(login_name);
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);