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:

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