von Markus Winand.

PostgreSQL Test Skripte für den „3-Minuten Test“


Dieser Abschnitt enthält die create, insert und select Kommandos für den „3-Minuten Test“. Mach den Test doch selbst, bevor du weiter liest.

Die Ausführungspläne sind zur besseren Lesbarkeit abgekürzt.

Tabellen Setup

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  PRIMARY KEY (id)
);


CREATE OR REPLACE FUNCTION random_string(minlen NUMERIC, maxlen NUMERIC)
RETURNS VARCHAR(1000)
AS
$$
DECLARE
  rv VARCHAR(1000) := '';
  i  INTEGER := 0;
  len INTEGER := 0;
BEGIN
  IF maxlen < 1 OR minlen < 1 OR maxlen < minlen THEN
    RETURN rv;
  END IF;

  len := floor(random()*(maxlen-minlen)) + minlen;

  FOR i IN 1..floor(len) LOOP
    rv := rv || chr(97+CAST(random() * 25 AS INTEGER));
  END LOOP;
  RETURN rv;
END;
$$ LANGUAGE plpgsql;


INSERT INTO tbl
SELECT generate_series
     , CURRENT_DATE - generate_series
     , MOD(generate_series,1234)
     , CAST(RANDOM() * 10 AS NUMERIC), random_string(20, 20)
     , CASE MOD(generate_series,5) WHEN 0 THEN 'X' ELSE 'A' END
FROM generate_series(1, 50000);

VACUUM ANALYZE tbl;

Frage 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);


EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE TO_CHAR(date_column, 'YYYY') = '2011';

EXPLAIN
 SELECT text, date_column
   FROM tbl
  WHERE date_column >= TO_DATE('2011-01-01', 'YYYY-MM-DD')
    AND date_column <  TO_DATE('2012-01-01', 'YYYY-MM-DD');


DROP INDEX tbl_idx;

Der erste Ausführungsplan liest die ganze Tabelle (Seq Scan). Der zweite Ausführungsplan verwendet den Index (Index Scan).

                           QUERY PLAN
---------------------------------------------------------------
Seq Scan on tbl  (cost=0.00..1386.00 rows=250)
  Filter: (to_char((date_column), 'YYYY'::text) = '2011'::text)


                           QUERY PLAN
---------------------------------------------------------------
Index Scan using tbl_idx on tbl  (cost=0.01..14.36 rows=205)
Index Cond: ((
 date_column >= to_date('2011-01-01'::text, 'YYYY-MM-DD'::text)
) AND (
 date_column <  to_date('2012-01-01'::text, 'YYYY-MM-DD'::text)
))

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

PREPARE STMT(int) AS
 SELECT id, date_column
   FROM tbl
  WHERE a = $1
  ORDER BY date_column DESC
  LIMIT 1;

EXPLAIN EXECUTE STMT(123);
DEALLOCATE STMT;

DROP INDEX tbl_idx;

Die Abfrage verwendet den Index (Index Scan) in absteigender Reihenfolge (Backward). Beachte, dass keine Sortieroperation aufscheint.

                  QUERY PLAN
----------------------------------------------
Limit  (cost=0.00..4.03 rows=1 width=12)
  ->  Index Scan Backward using tbl_idx on tbl
        (cost=0.00..164.96 rows=41)
        Index Cond: (a = ($1)::numeric)

Frage 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b);

PREPARE STMT(int, int) AS
 SELECT id, a, b
   FROM tbl
  WHERE a = $1
    AND b = $2;

EXPLAIN EXECUTE STMT(123, 1);
DEALLOCATE STMT;

PREPARE STMT(int) AS
 SELECT id, a, b
   FROM tbl
  WHERE b = $1;

EXPLAIN EXECUTE STMT(1);
DEALLOCATE STMT;

DROP INDEX tbl_idx;

Die zweite Abfrage liest die ganze Tabelle (Seq Scan). Wenn die Spalten im Index umgedreht werden, können beide Abfragen optimal vom Index profitieren und einen (Bitmap) Index Scan durchzuführen.

                         QUERY PLAN
-------------------------------------------------------------
Bitmap Heap Scan on tbl  (cost=4.30..19.32 rows=4)
 Recheck Cond: ((a = ($1)::numeric) AND (b = ($2)::numeric))
 -> Bitmap Index Scan on tbl_idx  (cost=0.00..4.30 rows=4)
    Index Cond: ((a = ($1)::numeric) AND (b = ($2)::numeric))

                        QUERY PLAN
----------------------------------------------------------
Seq Scan on tbl  (cost=0.00..1261.00 rows=4545 width=20)
  Filter: (b = ($1)::numeric)

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops);

EXPLAIN
 SELECT id, text
   FROM tbl
  WHERE text LIKE '%TERM%';

DROP INDEX tbl_idx;

Die Abfrage liest die ganze Tabelle (Seq Scan).

                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on tbl  (cost=0.00..1136.00 rows=5 width=29)
   Filter: ((text)::text ~~ '%TERM%'::text)

Frage 5 — Bereichsbedingung

Dieses Beispiel ist etwas schwerer zu beweisen, da die PostgreSQL Kostenschätzung den Performanceunterschied nicht widerspiegelt. Stattdessen vergleichen wir die tatsächliche Ausführungszeit mittels EXPLAIN ANALYZE.

CREATE INDEX tbl_idx ON tbl (date_column, state);
CLUSTER tbl_idx ON tbl;
VACUUM ANALYZE tbl;

EXPLAIN ANALYZE
 SELECT id, date_column, state
   FROM tbl
  WHERE date_column >= current_date - INTERVAL '5' YEAR
    AND state = 'X';

DROP INDEX tbl_idx;
CREATE INDEX tbl_idx ON tbl (state, date_column);
CLUSTER tbl_idx ON tbl;
VACUUM ANALYZE tbl;

EXPLAIN ANALYZE
 SELECT id, date_column, state
   FROM tbl
  WHERE date_column >= current_date - INTERVAL '5' YEAR
    AND state = 'X';

DROP INDEX tbl_idx;

Beide Indizes werden benutzt, der Unterschied liegt in der Ausführungszeit. Je größer der gewählte Zeitraum wird, desto größer wird auch der Performanceunterschied.

                        QUERY PLAN
------------------------------------------------------------
Index Scan using tbl_idx (actual time=0.071..0.764 rows=365)
Index Cond: (
 (date_column >= ((now())::date - '5 years'::interval year))
AND
 (state = 'X'::bpchar))
Total runtime: 0.842 ms

                        QUERY PLAN
-------------------------------------------------------------
Index Scan using tbl_idx (actual time=0.092..0.419 rows=365)
Index Cond: (
 (state = 'X'::bpchar)
AND
 (date_column >= ((now())::date - '5 years'::interval year)))
Total runtime: 0.520 ms

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

„Use The Index, Luke!“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz