PostgreSQL Test Skripts 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 create und insert Anweisungen findest du in diesem Download.

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

Frage 1 — DATE Anti-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);

SELECT COUNT(*)
  FROM tbl
 WHERE EXTRACT(YEAR FROM date_column) = 2024;

SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= DATE'2024-01-01'
   AND date_column <  DATE'2018-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 rows=1 loops=1)
Buffers: shared hit=3
-> Seq Scan on tbl (actual rows=271 loops=1)
   Filter: (date_part('year', date_column) = '2017')
   Rows Removed by Filter: 29
   Buffers: shared hit=3

                  QUERY PLAN
------------------------------------------------
Aggregate (actual rows=1 loops=1)
Buffers: shared hit=3
-> Seq Scan on tbl (actual rows=271 loops=1)
   Filter: ((date_column >= '2017-01-01'::date)
        AND (date_column <  '2018-01-01'::date))
   Rows Removed by Filter: 29
   Buffers: shared hit=3

Frage 2 — Indiziertes Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 FETCH FIRST 1 ROW ONLY;

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

                            QUERY PLAN
-------------------------------------------------------------------
Limit (actual rows=1 loops=1)
Buffers: shared hit=3
-> Index Scan Backward using tbl_idx on tbl (actual rows=1 loops=1)
   Index Cond: (a = '12'::numeric)
   Buffers: shared hit=3

Frage 3 — Spaltenreihenfolge

CREATE INDEX tbl_idx ON tbl (a, b);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

DROP INDEX tbl_idx ;

CREATE INDEX tbl_idx ON tbl (b, a);

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1;

SELECT *
  FROM tbl
 WHERE b = 1;

Die erste Abfrage kann beide Indizes optimal nutzen:

                       QUERY PLAN
--------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: ((a = '38'::numeric) AND (b = '1'::numeric))
Buffers: shared hit=3

                       QUERY PLAN
--------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: ((b = '1'::numeric) AND (a = '38'::numeric))
Buffers: shared hit=3

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
---------------------------------------
Seq Scan on tbl (actual rows=2 loops=1)
Filter: (b = '1'::numeric)
Rows Removed by Filter: 298
Buffers: shared hit=11

                      QUERY PLAN
-------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=2 loops=1)
Index Cond: (b = '1'::numeric)
Buffers: shared hit=4

Frage 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text varchar_pattern_ops);

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%';

Der Ausführungsplan zeigt klar, dass ein Index Scan durchgeführt wird. Da der Suchbegriff 'TJ%' nur ganz am ende ein Wildcard-Zeichen hat, kann der komplette Suchbegriff als Index-Zugriffsprädikat genutzt werden.

Da bei PostgreSQL nicht unmittelbar erkennbar ist, ob es sich um einen Full-Inde-Scan handelt, kann man zur Sicherheit die Tabelle erheblich vergrößern (z. B. 100-fach) und dann die Anzahl der gelesenen Blöcke betrachten (explain (analyze, buffers)).

                      QUERY PLAN
-------------------------------------------------------
Index Scan using tbl_idx on tbl (actual rows=1 loops=1)
Index Cond: (((text)::text ~>=~ 'TJ'::text)
         AND ((text)::text ~<~ 'TK'::text))
Filter: ((text)::text ~~ 'TJ%'::text)
Buffers: shared hit=3

Frage 5 — Index-Only Scan

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 = 38
 GROUP BY date_column

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

Beide Abfragen können den Index gut nutzen. Der unterschied ist, dass die zweite Abfrage zusätzlich auf die Tabelle zugreifen muss, um die Spalte B zu laden, und dadurch langsamer wird.

                          QUERY PLAN
---------------------------------------------------------------
GroupAggregate (actual rows=3 loops=1)
Group Key: date_column
Buffers: shared hit=3
-> Index Only Scan using tbl_idx on tbl (actual rows=3 loops=1)
   Index Cond: (a = '12'::numeric)
   Heap Fetches: 0
   Buffers: shared hit=3

                         QUERY PLAN
-------------------------------------------------------------
GroupAggregate (actual rows=1 loops=1)
Group Key: date_column
Buffers: shared hit=5
-> Sort (actual rows=1 loops=1)
   Sort Key: date_column
   Sort Method: quicksort  Memory: 25kB
   Buffers: shared hit=5
   -> Bitmap Heap Scan on tbl (actual rows=1 loops=1)
      Recheck Cond: (a = '38'::numeric)
      Filter: (b = '1'::numeric)
      Rows Removed by Filter: 2
      Heap Blocks: exact=3
      Buffers: shared hit=5
      -> Bitmap Index Scan on tbl_idx (actual rows=3 loops=1)
         Index Cond: (a = '38'::numeric)
         Buffers: shared hit=2