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


Diese Seite für

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

Photo of Markus Winand
Markus Winand stimmt Entwickler auf SQL-Performance ein. Er hat das Buch SQL Performance Explained veröffentlicht und bietet inhouse Schulungen sowie Tuning-Leistungen auf http://winand.at/ an.