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);
SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2016;
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2016-01-01'
   AND date_column <  DATE'2017-01-01';

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

                             QUERY PLAN
--------------------------------------------------------------------
 Aggregate (actual time=13.842..13.843 rows=1)
   Buffers: shared hit=516
   ->  Seq Scan on tbl  (actual time=0.070..13.807 rows=366)
         Filter: (date_part('year', date_column) = 2016)
         Rows Removed by Filter: 49634
         Buffers: shared hit=516


                                 QUERY PLAN
-------------------------------------------------------------------------------
 Aggregate  (actual time=0.109..0.109 rows=1)
   Buffers: shared hit=7
   ->  Index Only Scan using tbl_idx on tbl (actual time=0.019..0.080 rows=366)
         Index Cond: ((date_column >= '2016-01-01')
                  AND (date_column <  '2017-01-01'))
         Heap Fetches: 366
         Buffers: shared hit=7

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);
SELECT *
  FROM tbl
 WHERE a = 123
 ORDER BY date_column DESC
 LIMIT 1;

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);
SELECT *
  FROM tbl
 WHERE a = 123
   AND b = 1;
SELECT *
  FROM tbl
 WHERE b = 123;

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);
SELECT *
  FROM tbl
 WHERE text LIKE '%TERM%';

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 (a, date_column);
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
 GROUP BY date_column;
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 123
   AND b = 1
 GROUP BY date_column;

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