hunter's questions

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

Já li esta excelente resposta para emular o índice de verificação de salto para recuperar os valores distintos de uma única coluna. Isso é incrivelmente rápido.

Agora, o que estou tentando fazer é recuperar permutações únicas (combinações) em n colunas. Cada coluna a ser incluída possui um índice. Há também um índice exclusivo combinado em todas essas colunas. A tabela tem mais de 100 milhões de linhas e todos os meus esforços até agora foram dolorosamente lentos. Existe alguma maneira de aplicar a abordagem skip-scan-index a esse problema? Qualquer sugestão seria muito apreciada

Suponha que eu tenha uma tabela recordscom a seguinte estrutura

• id (int único)
• atualizado (timestamp)

Eu tenho um input_arraycom valores [[id_1, timestamp_1], [id_4, timestamp_4], ...]. Vou me referir a cada elemento como tuple_1, tuple_4, etc.

Estou procurando a consulta mais eficiente (no PostgreSQL v11.2+) para selecionar [id_1, id_4, ...], recordsmas apenas onde tuple_{n}.updated > row{n}.updated. Suponha input_arrayque pode conter milhares de tuplas e recordsmais de um milhão de linhas.

Eu nem sei por onde começar com isso. Lateral joinvem à mente, assim como unnest, e where in, mas tudo o que tentei até agora falha miseravelmente

Atualização Estou aberto a input_arrayestar em qualquer formato (tuplas, dois arrays separados, o que for), e updatedser umint