Início / dba / Perguntas / 339197
Accepted
hunter
Asked: 2024-05-03 02:52:14 +0800 CST

Emular varredura de índice solto para múltiplas colunas com direção de classificação alternada

  • 772

Há algum tempo, fiz esta pergunta sobre como selecionar com eficiência permutações exclusivas de colunas no Postgres. Agora tenho uma pergunta complementar sobre como fazer isso, além de poder ordenar qualquer uma das colunas com qualquer combinação de ASC/ DESCentre as colunas.

A tabela contém centenas de milhões de linhas e, embora a resposta aceita à minha pergunta anterior seja muito mais rápida do que as abordagens tradicionais, não ser capaz de ordenar as colunas de maneira ad-hoc me impede de fazer bom uso dessa consulta ( Eu realmente preciso 'paginar', com LIMIT/ OFFSETem pequenos pedaços). Existe uma maneira de fazer isso? O autor da resposta anterior sugeriu gentilmente uma solução alternativa (alterar a comparação de linhas para uma cláusula where explícita), que tentei, mas não parece funcionar (ou não entendi).

Dada a seguinte consulta genérica:

WITH RECURSIVE cte AS (
   (
   SELECT col1, col2, col3, col4
   FROM   tbl
   ORDER  BY 1,2,3,4
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.col1, t.col2, t.col3, t.col4
      FROM   tbl t
      WHERE (t.col1, t.col2, t.col3, t.col4) > (c.col1, c.col2, c.col3, c.col4)
      ORDER  BY 1,2,3,4
      LIMIT  1
      ) l
   )
SELECT * FROM cte

Existe uma maneira de ordenar as colunas de forma ad hoc, mantendo o desempenho? Por exemplo:

ORDER BY by col1 DESC, col2 ASC, col3 ASC, col4 DESC

Suponha um índice em cada coluna, bem como um índice combinado em todas as 4 colunas.

A versão do Postgres é 15.4.
A tabela é somente leitura no sentido de que os dados não podem/não serão modificados, porém serão adicionados. A seguir está um CREATE TABLEscript para replicar minha tabela problemática (mais ou menos):

CREATE TABLE tbl (id SERIAL primary key, col1 integer NOT NULL, col2 integer NOT NULL, col3 integer NOT NULL, col4 integer NOT NULL);

INSERT INTO tbl (col1, col2, col3, col4) SELECT (random()*1000)::int AS col1, (random()*1000)::int AS col2, (random()*1000)::int AS col3, (random()*1000)::int AS col4 FROM generate_series(1,10000000);

CREATE INDEX ON tbl (col1);
CREATE INDEX ON tbl (col2);
CREATE INDEX ON tbl (col3);
CREATE INDEX ON tbl (col4);
CREATE INDEX ON tbl (col1, col2, col3, col4);
postgresql

1 respostas

  1. Best Answer

    Basicamente: Não.
    A comparação de valores de linha funciona com ASCENDNGou DESCENDINGordem de classificação, mas não para uma combinação de direções entre campos aninhados. Ver:

    No entanto ...

    Solução alternativa para tipos numéricos

    Emulando varredura de salto de índice

    Observe que emular uma varredura de salto de índice só faz sentido para tabelas grandes com muitas duplicatas para seu conjunto de colunas. Mas então isso pode fazer uma diferença de ordens de magnitude .

    Fundamentos:

    Para ordem de classificação alternada, existem soluções alternativas para tipos de dados numéricos (ou qualquer tipo que possa ser "invertido") com uma expressão de várias colunas index . Basicamente, em vez de col1 DESC, você opera (col1 * -1) ASC- ASCsendo a direção padrão.

    Para o seu exemplo:

    ORDER BY col1 DESC, col2 ASC, col3 ASC, col4 DESC

    CREATE INDEX tbl_1d2a3a4d_idx ON tbl ((col1 * -1), col2, col3, (col4 * -1));  -- !!!

    A consulta então se torna:

    WITH RECURSIVE cte AS (
   (
   SELECT col1, col2, col3, col4
   FROM   tbl
   ORDER  BY (col1 * -1), col2, col3, (col4 * -1)              -- !
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.col1, t.col2, t.col3, t.col4
      FROM   tbl t
      WHERE    ((t.col1 * -1), t.col2, t.col3, (t.col4 * -1))  -- !
             > ((c.col1 * -1), c.col2, c.col3, (c.col4 * -1))  -- !
      ORDER  BY (t.col1 * -1), t.col2, t.col3, (t.col4 * -1)   -- !
      LIMIT  1
      ) l
   )
SELECT *
FROM   cte

    violino

    O Postgres não pode se contentar com uma varredura somente de índice, pois col1e col4estão ocultos atrás de uma expressão. Dependendo de vários fatores de armazenamento e RAM, pode valer a pena usar um índice de cobertura . Ver:

    CREATE INDEX tbl_1d2a3a4d_covering_idx
ON tbl ((col1 * -1), col2, col3, (col4 * -1)) INCLUDE (col1, col4);  -- !

    Mesma consulta.

    Mas tudo isso ainda está no tópico de "selecionar com eficiência permutações únicas de colunas".

    Paginação

    Você mencionou paginação (com LIMIT/ OFFSET). Você pode basear-se na consulta acima. Como:

    -- query from above
OFFSET 10000
LIMIT  10;

    Deve funcionar bem. Faz sentido limitado para uso ad hoc .
    Se a tabela não for somente leitura ou quando OFFSET+ LIMITficar grande, a paginação do conjunto de chaves faz mais sentido. Ver:

    Materialize linhas exclusivas com um número de linha

    Para uso repetido em uma tabela grande somente leitura , considere uma visualização materializada com um número de linha. Pode ou não fazer sentido se, no seu caso, "linhas serão adicionadas". Você pode usar a consulta acima para criar o MV:

    CREATE MATERIALIZED VIEW mv_tbl AS
WITH RECURSIVE cte AS (
   (
   SELECT col1, col2, col3, col4
   FROM   tbl
   ORDER  BY (col1 * -1), col2, col3, (col4 * -1)
   LIMIT  1
   )
   UNION ALL
   SELECT l.*
   FROM   cte c
   CROSS  JOIN LATERAL (
      SELECT t.col1, t.col2, t.col3, t.col4
      FROM   tbl t
      WHERE  ((t.col1 * -1), t.col2, t.col3, (t.col4 * -1))
           > ((c.col1 * -1), c.col2, c.col3, (c.col4 * -1))
      ORDER  BY (t.col1 * -1), t.col2, t.col3, (t.col4 * -1)
      LIMIT  1
      ) l
   )
SELECT row_number() OVER () AS rn, *  -- !
FROM   cte;

    Então, índice e consulta são simples:

    CREATE INDEX mv_tbl_idx ON mv_tbl (rn);  -- !

SELECT rn, col1, col2, col3, col4
FROM   mv_tbl
WHERE  rn > 10000
ORDER  BY rn
LIMIT  10;

    violino

    Existem muitas nuances em tudo isso...

    • 3

relate perguntas