Recettes pour les requêtes SQL en difficulté

# 1: index "sous-tri"

Quand surgit

Comment reconnaître

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

Recommandations

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: intersection d'index (BitmapAnd)

Quand surgit

Comment reconnaître

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

Recommandations

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: Pool d'index (BitmapOr)

Quand surgit

Comment reconnaître

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

Recommandations

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,    

Une histoire plus détaillée sur cette méthode d'optimisation à l' aide d'exemples spécifiques peut être trouvée dans les articles de PostgreSQL Antipatterns: JOIN JOIN et OR nuisibles et PostgreSQL Antipatterns: un récit sur le raffinement itératif de la recherche par nom, ou «Optimisation ici et là» .

Une version généralisée de la sélection ordonnée par plusieurs clés (et pas seulement par la paire const / NULL) a été envisagée dans l'article SQL HowTo: nous écrivons la boucle while directement dans la requête, ou «Elementary three-way» .

# 4: lire beaucoup d'excès

Quand surgit

"Et vous n'avez pas la même chose, mais avec des boutons de nacre ?" film "Diamond Hand"

Comment reconnaître

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

Recommandations

Si la condition de filtre est «statique» pour vos tâches - c'est-à-dire qu'elle n'implique pas d'élargir la liste de valeurs à l'avenir - il est préférable d'utiliser l'index WHERE. Différents statuts booléens / enum entrent bien dans cette catégorie.

Si la condition de filtre peut prendre différentes valeurs , il est préférable d'étendre l'index avec ces champs - comme dans la situation avec BitmapAnd ci-dessus.

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: Table clairsemée

Quand surgit

Comment reconnaître

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

Recommandations

Dans la plupart des cas, ces problèmes sont causés par des requêtes mal structurées lors d'appels à partir de la logique métier, telles que celles abordées dans PostgreSQL Antipatterns: combat des hordes de «morts» .

Mais vous devez comprendre que même VACUUM FULL peut ne pas toujours aider. Pour de tels cas, vous devez vous familiariser avec l'algorithme de l'article DBA: lorsque VACUUM passe, nous nettoyons la table manuellement .

# 6: lecture à partir du milieu de l'index

Quand surgit

Comment reconnaître

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

Recommandations

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);

D'autres exemples de situations d'utilisation inefficace des index peuvent être vus dans l'article DBA: Find Useless Indexes .

# 7: CTE × CTE

Quand surgit

Comment reconnaître

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

Recommandations

# 8: échange sur disque (écrit temporairement)

Quand surgit

Comment reconnaître

-> *
   && temp written > 0

Recommandations

SHOW work_mem;
-- "16MB"

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

SET work_mem = '128MB'; --   

# 9: Statistiques non pertinentes

Quand surgit

Comment reconnaître

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

Recommandations

Cette situation est décrite plus en détail dans PostgreSQL Antipatterns: les statistiques sont partout .

# 10: «quelque chose s'est mal passé»

Quand surgit

Comment reconnaître

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

Recommandations