Início / dba / Perguntas / 342916
Accepted
kadircancetin
Asked: 2024-10-10 17:34:28 +0800 CST

ONDE A=x DISTINTO EM (B), com um índice composto em (A, B, C)

  • 772

Tenho uma tabela enorme com um índice composto em (A, B, C).

-- psql (13.16 (Debian 13.16-0+deb11u1), server 14.12)

\d index_a_b_c
         Index "public.index_a_b_c"
  Column  |         Type          | Key? | 
----------+-----------------------+------+
 A        | character varying(44) | yes  |
 B        | numeric(20,0)         | yes  |
 C        | numeric(20,0)         | yes  |
btree, for table "public.table_a_b_c"

Preciso de todos Bos s distintos.

Esta consulta é executada com Index Only Scan, mas, verifica todas as Acorrespondências. O que não é escala para o meu caso, pois para alguns As há milhões de linhas. Milhões de Index Only Scanlinhas são lentos.

EXPLAIN (ANALYZE true) 
SELECT DISTINCT ON ("B") "B"
  FROM "table_a_b_c"
 WHERE "A" = 'astring'

-- Execution time: 0.172993s
-- Unique  (cost=0.83..105067.18 rows=1123 width=5) (actual time=0.037..19.468 rows=67 loops=1)
--  ->  Index Only Scan using index_a_b_c on table_a_b_c  (cost=0.83..104684.36 rows=153129 width=5) (actual time=0.036..19.209 rows=1702 loops=1)
--        Index Cond: (A = 'astring'::text)
--        Heap Fetches: 351
-- Planning Time: 0.091 ms
-- Execution Time: 19.499 ms

Como você pode ver, ele executa mais de 1,7 mil linhas, filtra manualmente e retorna 67 linhas. 20 ms, obtendo dezenas de segundos quando 1,7 mil chega a milhões.

Também preciso de todos os maiores Cs para Bs distintos.

Mesma coisa que em 1) . Em teoria, o Postgres poderia conhecer possíveis Bs, e não precisaria verificar toda a lista correspondida a A.

EXPLAIN (ANALYZE true)
SELECT DISTINCT ON ("B") *
  FROM "table_a_b_c"
 WHERE "A" = 'astring'
 ORDER BY "B" DESC,
          "C" DESC

-- Execution time: 0.822705s 
-- Unique  (cost=0.83..621264.51 rows=1123 width=247) (actual time=0.957..665.927 rows=67 loops=1)
--   ->  Index Scan using index_a_b_c on table_a_b_c  (cost=0.83..620881.69 rows=153130 width=247) (actual time=0.955..664.408 rows=1702 loops=1)
--         Index Cond: (a = 'astring'::text)
-- Planning Time: 0.116 ms
-- Execution Time: 665.978 ms

Mas, por exemplo, isto é rápido:

SELECT * WHERE A="x" AND B=1 ORDER BY C DESC
  UNION
SELECT * WHERE A="x" AND B=2 ORDER BY C DESC
  UNION
....

para todos Bos s possíveis. É como um loop com número de Btempo.

Questões

a) O índice on não deveria (A, B, C)ser um superconjunto de (A, B)em teoria? (A, B)será super rápido para distinct.

b) Por que é difícil encontrar Bs distintos para o Postgres?

c) Como lidar com isso sem um novo índice?

postgresql

1 respostas

  1. Best Answer

    Anexo A (da sua descrição):

    para alguns As há milhões de linhas.

    Anexo B (do plano de consulta):

    rows=153129→rows=67

    Significa muitas linhas por valor distinto em B.

    Emular varredura de índice-pulo para muitas linhas por grupo

    Apenas distinto B:

    WITH RECURSIVE rcte AS (
   (
   SELECT B
   FROM   table_abc
   WHERE  A = 'astring'
   ORDER  BY B
   LIMIT  1
   )

   UNION ALL
   SELECT t.B
   FROM   rcte r
   CROSS  JOIN LATERAL (  -- implicit break condition
      SELECT t.B
      FROM   table_abc t
      WHERE  t.A = 'astring'
      AND    t.B > r.B
      ORDER  BY t.B
      LIMIT  1
      ) t
   )
TABLE rcte;

    Distinto Bcom o maior C:

    WITH RECURSIVE rcte AS (
   (
   SELECT B, C
   FROM   table_abc
   WHERE  A = 'astring'
   ORDER  BY B DESC, C DESC
   LIMIT  1
   )

   UNION ALL
   SELECT t.B, t.C
   FROM   rcte r
   CROSS  JOIN LATERAL (  -- implicit break condition
      SELECT t.B, t.C
      FROM   table_abc t
      WHERE  t.A = 'astring'
      AND    t.B < r.B
      ORDER  BY t.B DESC, t.C DESC  -- keep in sync to match index!
      LIMIT  1
      ) t
   )
TABLE rcte;

    violino

    Agora, as varreduras somente de índice buscam apenas os valores necessários. (Nenhuma varredura sequencial do índice de cobertura.) Efetivamente, você obtém o que tentou forçar com a UNIONconsulta (o que deveria ser UNION ALLo caso).

    Deve ser muito rápido .

    Relacionado:

    Há detalhes intrincados nessa técnica. Veja:

    Sobre a ordem de classificação alternada ( ASC/ DESC) em um índice multicolunas:

    DISTINCT ONpara apenas algumas linhas por grupo

    As consultas que você tentou são as ótimas nesse caso. A sobrecarga considerável para o CTE recursivo não compensaria.

    violino

    Ver:

    • 1

relate perguntas