Rezepte für angeschlagene SQL-Abfragen

# 1: Index "Untersortierung"

Wann entsteht

Wie man erkennt

-> Limit
   -> Sort
      -> Index [Only] Scan [Backward] | Bitmap Heap Scan

Empfehlungen

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K ""
, (random() * 1000)::integer fk_cli; -- 1K   

CREATE INDEX ON tbl(fk_cli); --   foreign key

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 1 --    
ORDER BY
  pk DESC --    "" 
LIMIT 1;

DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); --   

# 2: Indexschnittpunkt (BitmapAnd)

Wann entsteht

Wie man erkennt

-> BitmapAnd
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Empfehlungen

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K ""
, (random() *  100)::integer fk_org  -- 100   
, (random() * 1000)::integer fk_cli; -- 1K   

CREATE INDEX ON tbl(fk_org); --   foreign key
CREATE INDEX ON tbl(fk_cli); --   foreign key

SELECT
  *
FROM
  tbl
WHERE
  (fk_org, fk_cli) = (1, 999); --    

DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);

# 3: Indexverknüpfung (BitmapOr)

Wann entsteht

Wie man erkennt

-> BitmapOr
   -> Bitmap Index Scan
   -> Bitmap Index Scan

Empfehlungen

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk  -- 100K ""
, CASE
    WHEN random() < 1::real/16 THEN NULL --   1:16  ""
    ELSE (random() * 100)::integer -- 100   
  END fk_own;

CREATE INDEX ON tbl(fk_own, pk); --   "  " 

SELECT
  *
FROM
  tbl
WHERE
  fk_own = 1 OR -- 
  fk_own IS NULL -- ...  ""
ORDER BY
  pk
, (fk_own = 1) DESC --  ""
LIMIT 20;

(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own = 1 --  "" 20
  ORDER BY
    pk
  LIMIT 20
)
UNION ALL
(
  SELECT
    *
  FROM
    tbl
  WHERE
    fk_own IS NULL --  "" 20
  ORDER BY
    pk
  LIMIT 20
)
LIMIT 20; --   - 20,    

Eine detailliertere Geschichte über diese Optimierungsmethode anhand spezifischer Beispiele finden Sie in den Artikeln von PostgreSQL Antipatterns: schädliche JOIN- und OR- und PostgreSQL-Antipatterns: eine Geschichte über die iterative Verfeinerung der Suche nach Namen oder „Optimierung hin und zurück“ .

Eine verallgemeinerte Version der geordneten Auswahl durch mehrere Schlüssel (und nicht nur durch ein const / NULL-Paar) wird im SQL HowTo- Artikel betrachtet : Wir schreiben eine while-Schleife direkt in die Abfrage oder "Elementary Three-Way" .

# 4: Lesen Sie viel Unnötiges

Wann entsteht

"Und du hast nicht das gleiche, aber mit Perlmuttknöpfen ?" Film "Diamond Hand"

Wie man erkennt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && 5 × rows < RRbF --  >80% 
   && loops × RRbF > 100 --     100  

Empfehlungen

Wenn die Filterbedingung für Ihre Aufgaben "statisch" ist, dh die Liste der Werte in Zukunft nicht erweitert wird, ist es besser, einen WHERE-Index zu verwenden. Verschiedene Boolesche / Enum-Status passen gut in diese Kategorie.

Wenn die Filterbedingung unterschiedliche Werte annehmen kann , ist es besser, den Index mit diesen Feldern zu erweitern - wie in der obigen Situation mit BitmapAnd.

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk -- 100K ""
, CASE
    WHEN random() < 1::real/16 THEN NULL
    ELSE (random() * 100)::integer -- 100   
  END fk_own
, (random() < 1::real/50) critical; -- 1:50,   ""

CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);

SELECT
  *
FROM
  tbl
WHERE
  critical
ORDER BY
  pk
LIMIT 20;

CREATE INDEX ON tbl(pk)
  WHERE critical; --  ""  

# 5: spärlicher Tisch

Wann entsteht

Wie man erkennt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      --   1KB   
   && shared hit + shared read > 64

Empfehlungen

In den meisten Fällen werden diese Probleme durch schlecht strukturierte Abfragen beim Aufrufen aus der Geschäftslogik verursacht, wie sie beispielsweise in PostgreSQL Antipatterns: Kampf gegen Horden von „Toten“ beschrieben werden .

Aber Sie müssen verstehen, dass selbst VACUUM FULL möglicherweise nicht immer hilft. In solchen Fällen sollten Sie sich mit dem Algorithmus aus dem DBA- Artikel vertraut machen : Wenn VACUUM erfolgreich ist, bereinigen wir die Tabelle manuell .

# 6: Lesen aus der Mitte des Index

Wann entsteht

Wie man erkennt

-> Index [Only] Scan [Backward]
   && loops × (rows + RRbF) < (shared hit + shared read) × 8
      --   1KB   
   && shared hit + shared read > 64

Empfehlungen

CREATE TABLE tbl AS
SELECT
  generate_series(1, 100000) pk      -- 100K ""
, (random() *  100)::integer fk_org  -- 100   
, (random() * 1000)::integer fk_cli; -- 1K   

CREATE INDEX ON tbl(fk_org, fk_cli); --     #2
--      fk_cli      

SELECT
  *
FROM
  tbl
WHERE
  fk_cli = 999 --  fk_org  ,     
LIMIT 20;

CREATE INDEX ON tbl(fk_cli);

Weitere Beispiele für Situationen ineffizienter Verwendung von Indizes finden Sie im DBA- Artikel : Suchen Sie nach nutzlosen Indizes .

# 7: CTE × CTE

Wann entsteht

Wie man erkennt

-> CTE Scan
   && loops > 10
   && loops × (rows + RRbF) > 10000
      --     CTE

Empfehlungen

# 8: Swap to Disk (Temp geschrieben)

Wann entsteht

Wie man erkennt

-> *
   && temp written > 0

Empfehlungen

SHOW work_mem;
-- "16MB"

SELECT
  random()
FROM
  generate_series(1, 1000000)
ORDER BY
  1;

SET work_mem = '128MB'; --   

# 9: irrelevante Statistiken

Wann entsteht

Wie man erkennt

-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
   && ratio >> 10

Empfehlungen

Diese Situation wird in PostgreSQL Antipatterns ausführlicher beschrieben: Statistiken sind allgegenwärtig .

# 10: "etwas ist schief gelaufen"

Wann entsteht

Wie man erkennt

-> *
   && (shared hit / 8K) + (shared read / 1K) < time / 1000
      -- RAM hit = 64MB/s, HDD read = 8MB/s
   && time > 100ms --  ,   

Empfehlungen