Início / dba / Perguntas / 317886
Accepted
Vladimir Baranov
Asked: 2022-10-06 22:30:11 +0800 CST

O Postgres pode verificar os índices para trás?

  • 772

Usamos uma instância do Amazon RDS com

PostgreSQL 11.13 em x86_64-pc-linux-gnu, compilado por gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64 bits

Eu tenho uma consulta clássica simples de 1 por grupo. Eu preciso obter o último item no histórico para cada arquivo creativeScheduleId.

Aqui está uma tabela e definições de índice:

CREATE TABLE IF NOT EXISTS public.creative_schedule_status_histories (
  id serial PRIMARY KEY,
  "creativeScheduleId" text NOT NULL,
  -- other columns
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_creativescheduleid_id
  ON public.creative_schedule_status_histories ("creativeScheduleId" ASC, id ASC);

Quando uma consulta ordena pelo id ASCmecanismo lê apenas o índice e não faz nenhuma classificação extra:

EXPLAIN (ANALYZE) 
SELECT history.id, history."creativeScheduleId"
FROM  (
    SELECT cssh.id, cssh."creativeScheduleId"
         , ROW_NUMBER() OVER (PARTITION BY cssh."creativeScheduleId"
                              ORDER BY cssh.id ASC) AS rn  -- !
    FROM creative_schedule_status_histories as cssh
    ) AS history
WHERE history.rn = 1;

"Subquery Scan on history  (cost=0.56..511808.63 rows=26377 width=41) (actual time=0.047..4539.058 rows=709030 loops=1)"
"  Filter: (history.rn = 1)"
"  Rows Removed by Filter: 4579766"
"  ->  WindowAgg  (cost=0.56..445866.24 rows=5275391 width=49) (actual time=0.046..4165.835 rows=5288796 loops=1)"
"        ->  Index Only Scan using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..353546.90 rows=5275391 width=41) (actual time=0.037..1447.490 rows=5288796 loops=1)"
"              Heap Fetches: 2372"
"Planning Time: 0.072 ms"
"Execution Time: 4568.235 ms"

Eu esperava ver exatamente o mesmo plano para uma consulta ao fazer o pedido por id DESC, mas há uma classificação explícita no plano que se espalha para o disco e, obviamente, tudo é apenas mais lento.

EXPLAIN (ANALYZE) 
SELECT history.id, history."creativeScheduleId"
FROM  (
    SELECT cssh.id, cssh."creativeScheduleId"
         , ROW_NUMBER() OVER (PARTITION BY cssh."creativeScheduleId"
                              ORDER BY cssh.id DESC) AS rn  -- !
    FROM creative_schedule_status_histories as cssh
    ) AS history
WHERE history.rn = 1;

"Subquery Scan on history  (cost=1267132.63..1438582.84 rows=26377 width=41) (actual time=11974.827..15840.338 rows=709046 loops=1)"
"  Filter: (history.rn = 1)"
"  Rows Removed by Filter: 4579802"
"  ->  WindowAgg  (cost=1267132.63..1372640.45 rows=5275391 width=49) (actual time=11974.825..15529.679 rows=5288848 loops=1)"
"        ->  Sort  (cost=1267132.63..1280321.11 rows=5275391 width=41) (actual time=11974.814..13547.038 rows=5288848 loops=1)"
"              Sort Key: cssh.""creativeScheduleId"", cssh.id DESC"
"              Sort Method: external merge  Disk: 263992kB"
"              ->  Index Only Scan using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..353550.90 rows=5275391 width=41) (actual time=0.015..1386.310 rows=5288848 loops=1)"
"                    Heap Fetches: 2508"
"Planning Time: 0.078 ms"
"Execution Time: 15949.877 ms"

Eu esperava que o índice fornecido fosse igualmente útil em ambas as variantes da consulta.
O Postgres não pode digitalizar um índice para trás aqui?
O que estou perdendo aqui?

Quando faço uma consulta para um dado específico creativeScheduleId, o Postgres usa o índice igualmente bem para ambos ASCe a DESCordem de classificação. Não há classificação explícita em nenhuma variante:

EXPLAIN (ANALYZE) 
SELECT id, "creativeScheduleId"
FROM   creative_schedule_status_histories AS cssh
WHERE  "creativeScheduleId" = '24238370-a64c-4b30-ac8e-27eb2b693aca'
ORDER  BY id DESC  -- or ASC, no sort
LIMIT  1

"Limit  (cost=0.56..0.71 rows=1 width=41) (actual time=0.022..0.022 rows=1 loops=1)"
"  ->  Index Only Scan Backward using idx_creativescheduleid_id on creative_schedule_status_histories cssh  (cost=0.56..14.06 rows=86 width=41) (actual time=0.021..0.021 rows=1 loops=1)"
"        Index Cond: (""creativeScheduleId"" = '24238370-a64c-4b30-ac8e-27eb2b693aca'::text)"
"        Heap Fetches: 0"
"Planning Time: 0.064 ms"
"Execution Time: 0.033 ms"

Aqui nós realmente vemos Index Only Scan Backward, então o Postgres é capaz disso. Mas não para a mesa inteira.

Alguma idéia de como incentivar o mecanismo a varrer todo o índice para trás para a primeira consulta que lê a tabela inteira?

postgresql

3 respostas

  1. Por causa da limitação discutida, não podemos fazer o Postgres varrer o índice para trás para o caso de uso específico. No entanto ...

    Caso de teste limpo

    Eu removi o ruído do caso de teste:

    CREATE TABLE tbl (
  id   int PRIMARY KEY
, part int NOT NULL
, ballast text  -- possibly big column(s)?
);

CREATE UNIQUE INDEX tbl_part_id_idx ON tbl (part, id);

    Livre-se da etapa de classificação cara

    No Postgres 14 ou posterior, vejo um Incremental Sortarquivo Index Only Scan.
    No Postgres 11 ou posterior, a classificação adicional desaparece com esta solução alternativa:

    SELECT id, part
FROM  (
   SELECT *
        , CASE WHEN part = lead(part) OVER (ORDER BY part, id ROWS UNBOUNDED PRECEDING)
               THEN false 
               ELSE true END AS qualified
   FROM   tbl
   ) sub
WHERE  qualified;

    Subquery Scan on sub (cost=0.42..10293.58 rows=89880 width=8) (actual time=1.759..115.799 rows=67 loops=1)
  Filter: sub.qualified
  Rows Removed by Filter: 179692
  -> WindowAgg (cost=0.42..8495.99 rows=179759 width=41) (actual time=0.022..106.609 rows=179759 loops=1)
        -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4900.81 rows=179759 width=8) (actual time=0.017..29.208 rows=179759 loops=1)
              Heap Fetches: 0
Planning Time: 0.114 ms
Execution Time: 115.850 ms

    Isso é baseado na consulta de Paul. É melhor do que minha primeira ideia comparar o número da linha e a contagem por partição. Eu me adaptei para obter o melhor idpor grupo, simplifiquei e mudei para o ROWSmodo para melhor desempenho. Ver:

    Ele verifica o índice para frente . Para ver um real Index Scan backwards:

    ...
          CASE WHEN part = lag(part) OVER (ORDER BY part DESC, id DESC ROWS UNBOUNDED PRECEDING)
               THEN false 
               ELSE true END AS qualified
...

    A primeira variante é apenas um pouco mais curta e mais rápida.

    Enquanto você está preso à sua consulta original, você pode pelo menos fazer a classificação acontecer na RAM. Seu plano de consulta diz Disk: 263992kB. Aumente work_memem 300 MB (se você puder pagar) para conseguir isso. Possivelmente apenas em sua sessão para a grande consulta. Ver:

    O que você realmente quer

    Sua consulta atual nunca vence nenhuma competição .
    Para agilizar sua consulta (mesmo sem índice adicional):

    Para poucas linhas por grupo (e suficiente work_mem), use DISTINCT ON. É mais rápido com índice de correspondência e talvez até sem:

    SELECT DISTINCT ON (part) id, part
FROM   tbl
ORDER  BY part, id DESC;

    Unique (cost=33480.91..34380.55 rows=67 width=8) (actual time=96.726..131.735 rows=67 loops=1)
  -> Sort (cost=33480.91..33930.73 rows=179929 width=8) (actual time=96.724..118.292 rows=179929 loops=1)
        Sort Key: part, id DESC
        Sort Method: external merge Disk: 3184kB
        -> Index Only Scan using tbl_part_id_idx on tbl (cost=0.42..4903.35 rows=179929 width=8) (actual time=0.019..25.871 rows=179929 loops=1)
              Heap Fetches: 0
Planning Time: 0.102 ms
Execution Time: 132.208 ms

    Para muitas linhas por grupo (como neste teste), DISTINCT ONnão é o ideal, mas normalmente também não é tão ruim.

    Para mais do que algumas linhas por grupo , gostaríamos de uma verificação de salto de índice. Um esforço considerável foi feito , mas, infelizmente, isso não chegou ao Postgres 15. Ainda podemos emular a técnica com grande efeito com um CTE recursivo:

    EXPLAIN ANALYZE
WITH RECURSIVE cte AS (
   (
   SELECT part, id
   FROM   tbl
   ORDER  BY part DESC, id DESC
   LIMIT  1
   )
   
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.part, t.id
      FROM   tbl t
      WHERE  t.part < c.part
      ORDER  BY t.part DESC, t.id DESC
      LIMIT  1
      ) l
   )
TABLE  cte;

    'CTE Scan on cte  (cost=40.74..42.76 rows=101 width=8) (actual time=0.013..0.304 rows=34 loops=1)'
'  CTE cte'
'    ->  Recursive Union  (cost=0.29..40.74 rows=101 width=8) (actual time=0.012..0.297 rows=34 loops=1)'
'          ->  Limit  (cost=0.29..0.33 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)'
'                ->  Index Only Scan Backward using tbl_part_id_idx on tbl  (cost=0.29..3410.28 rows=100000 width=8) (actual time=0.010..0.011 rows=1 loops=1)'
'                      Heap Fetches: 1'
'          ->  Nested Loop  (cost=0.29..3.84 rows=10 width=8) (actual time=0.008..0.008 rows=1 loops=34)'
'                ->  WorkTable Scan on cte c  (cost=0.00..0.20 rows=10 width=4) (actual time=0.000..0.000 rows=1 loops=34)'
'                ->  Limit  (cost=0.29..0.34 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=34)'
'                      ->  Index Only Scan Backward using tbl_part_id_idx on tbl t  (cost=0.29..1714.08 rows=33333 width=8) (actual time=0.007..0.007 rows=1 loops=34)'
'                            Index Cond: (part < c.part)'
'                            Heap Fetches: 33'
'Planning Time: 0.115 ms'
'Execution Time: 0.324 ms'  -- !!!

    Um ou outro é normalmente (muito) mais rápido que sua consulta original.

    violino para Postgres 15 com 3000 linhas por grupo
    violino para Postgres 11
    violino para Postgres 11 com 8 linhas por grupo (~ sua distribuição) & work_mem
    violino suficiente para Postgres 11 com 2 linhas por grupo & suficientework_mem

    Ver:

    • 3
  2. Best Answer

    Como solução alternativa, considere as linhas classificadas em ordem de índice decrescente:

    Eu iria CreativeScheduleId
    10 d
    9 c
    8 c
    7 b
    6 b
    5 b
    4 uma
    3 uma
    2 uma
    1 uma

    As linhas que você deseja (em negrito) são aquelas em que a linha anterior não tem um valor correspondente para "creativeScheduleId":

    EXPLAIN (ANALYZE) 
SELECT 
    q1.id, 
    q1."creativeScheduleId" 
FROM 
(
    SELECT
        cssh.*,
        CASE
            WHEN cssh."creativeScheduleId" = 
                LAST_VALUE(cssh."creativeScheduleId") OVER (
                    ORDER BY cssh."creativeScheduleId" DESC, cssh.id DESC
                        ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)
            THEN 0
            ELSE 1
    END AS qualified
    FROM public.creative_schedule_status_histories AS cssh
) AS q1
WHERE
    q1.qualified = 1;

    Subquery Scan on q1 (cost=0.15..104.48 rows=6 width=36) (actual time=0.014..0.014 rows=0 loops=1)
  Filter: (q1.qualified = 1)
  -> WindowAgg (cost=0.15..88.60 rows=1270 width=40) (actual time=0.013..0.014 rows=0 loops=1)
        -> Index Only Scan Backward using idx_creativescheduleid_id on creative_schedule_status_histories cssh (cost=0.15..63.20 rows=1270 width=36) (actual time=0.011..0.011 rows=0 loops=1)
              Heap Fetches: 0
Planning Time: 0.415 ms
Execution Time: 0.076 ms

    db<>violino

    Em um comentário, você expressou interesse em como o SQL Server lida com isso.

    Ele pode usar uma verificação inversa do índice, mas precisa de um pouco de ajuda :

    SELECT
    Q1.id, 
    Q1.creativeScheduleId
FROM 
(
    SELECT 
        CSSH.id, 
        CSSH.creativeScheduleId,
        rn = ROW_NUMBER() OVER (
            PARTITION BY CSSH.creativeScheduleId
            ORDER BY CSSH.id DESC)
    FROM dbo.creative_schedule_status_histories AS CSSH
) AS Q1
WHERE
    Q1.rn = 1
-- Encourage optimizer
ORDER BY
    Q1.creativeScheduleId DESC,
    Q1.id DESC;

     |--Filter(WHERE:([Expr1001]=(1)))
       |--Sequence Project(DEFINE:([Expr1001]=row_number))
            |--Segment
                 |--Index Scan([idx_creativescheduleid_id]), ORDERED BACKWARD)

    Varredura inversa do índice; nenhum tipo

    db<>violino

    • 3

  3. Embora o PostgreSQL saiba ler um índice de trás para frente em geral, existem alguns casos que excedem seu alcance e essa função de janela com particionamento e ordenação é um deles.

    Você também pode imaginar essa consulta sendo resolvida usando tabelas de hash em vez de classificação/ordenação de qualquer tipo (no caso específico de rn=1) , mas isso também não é implementado.

    • 2

relate perguntas