Receitas para consultas SQL em dificuldades

# 1: índice "undersorting"

Quando surge

Como reconhecer

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

Recomendações

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: interseção de índice (BitmapAnd)

Quando surge

Como reconhecer

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

Recomendações

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 de índices (BitmapOr)

Quando surge

Como reconhecer

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

Recomendações

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,    

Uma história mais detalhada sobre esse método de otimização usando exemplos específicos pode ser encontrada nos artigos dos Antipatterns do PostgreSQL: prejudiciais JOIN e OR e Antipatterns do PostgreSQL: uma história sobre o refinamento iterativo da pesquisa por nome ou “Otimização lá e volta” .

Uma versão generalizada da seleção ordenada por várias chaves (e não apenas pelo par const / NULL) foi considerada no artigo do SQL HowTo: escrevemos o loop while diretamente na consulta, ou “Três vias elementares” .

# 4: leia muitas coisas desnecessárias

Quando surge

"E você não tem o mesmo, mas com botões de pérola ?" filme "Mão de diamante"

Como reconhecer

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

Recomendações

Se a condição de filtragem for "estática" para suas tarefas - ou seja, não envolverá a expansão da lista de valores no futuro - é melhor usar um índice WHERE. Diferentes status booleanos / enum se encaixam bem nessa categoria.

Se a condição do filtro puder assumir valores diferentes , é melhor expandir o índice com esses campos - como na situação do Bitmap e acima.

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: mesa esparsa

Quando surge

Como reconhecer

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

Recomendações

Na maioria dos casos, esses problemas são causados ​​por um layout de consulta ruim ao fazer chamadas da lógica de negócios, como as discutidas nos antipatterns do PostgreSQL: combatendo as hordas de “mortos” .

Mas você precisa entender que mesmo o VACUUM FULL nem sempre pode ajudar. Para esses casos, você deve se familiarizar com o algoritmo do artigo DBA: quando o VACUUM passa, limpamos a tabela manualmente .

# 6: lendo no meio do índice

Quando surge

Como reconhecer

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

Recomendações

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

Outros exemplos de situações de uso ineficiente de índices podem ser vistos no artigo do DBA: Localizar índices inúteis .

# 7: CTE × CTE

Quando surge

Como reconhecer

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

Recomendações

# 8: trocar para disco (temp escrito)

Quando surge

Como reconhecer

-> *
   && temp written > 0

Recomendações

SHOW work_mem;
-- "16MB"

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

SET work_mem = '128MB'; --   

# 9: estatísticas irrelevantes

Quando surge

Como reconhecer

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

Recomendações

Essa situação é descrita em mais detalhes nos Antipatterns do PostgreSQL: as estatísticas estão em toda parte .

# 10: "algo deu errado"

Quando surge

Como reconhecer

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

Recomendações