Início / dba / Perguntas / 80311
Accepted
Alexandros
Asked: 2014-10-17 00:03:39 +0800 CST

Postgres top-k com laços

  • 772

Suponha que eu tenha a seguinte tabela de banco de dados com 3 campos inteiros.

A | B | C
1 | 2 | 3
1 | 2 | 4
1 | 3 | 1
2 | 4 | 2
2 | 4 | 3

Quando eu faço:

SELECT * FROM dbTable ORDER BY A,B LIMIT 1;

eu recebo

1 | 2 | 3

que é esperado. Mas o 2º registro:

 1 | 2 | 4

também tem os mesmos valores para dbFields A e B. Existe alguma maneira eficiente de realmente recuperar todos os registros que têm o mesmo valor que os registros top-k? Por exemplo, quando procuro os primeiros 100 registros para obter 102 registros, se os dois últimos tiverem os mesmos valores do 100º registro? Existe algum índice para acelerar tais consultas? Não me importo se tiver que ser feito com pl/pgsql (e não SQL simples) se a implementação for eficiente.

postgresql postgresql-9.3

2 respostas

  1. Você pode usar uma função de janela para isso:

    select a,b,c
from (
  select a,b,c,
         dense_rank() over (order by a,b) as rnk
  from dbTable
) t
where rnk = 1;

    Para as "primeiras" linhas, não importa se você usa rank()ou dense_rank(). Quando você, por exemplo, deseja os "segundos", o rank()e dense_rank()retornaria resultados diferentes em caso de empate. Porque rank()vai ter "lacunas" nos números, mas dense_rank()não vai.

    Uma possível aceleração pode ser alcançada fazendo isso em duas etapas e, claro, tendo um índice em(a,b)

    with ranked as (
  select *
  from (
    select a,b,
           dense_rank() over (order by a,b) as rnk
    from dbTable
  ) t
  where r.rnk = 1  -- (or <= for "top-k")
)
select t.a, t.b, t.c
from dbTable t
   join ranked r on r.a = t.a and r.b = t.b;

    A ideia é dar ao Postgres a chance de fazer uma varredura somente de índice para a parte de classificação e, em seguida, juntar apenas o resultado disso à tabela base para obter a(s) coluna(s) restante(s). A filtragem na classificação é feita dentro do CTE, pois o Postgres não envia as condições da consulta externa para o próprio CTE (é por isso que tenho a tabela derivada dentro do CTE)

    Não tenho certeza se isso realmente melhora o desempenho, mas acho que valeria a pena tentar e dar uma olhada no plano de execução com as tabelas reais (e dados).

    • 2
  2. Best Answer

    Isto é o que eu vim com:

    /* Get the records of original table that correspond to each value of A 
and for values of B better or the same for the top-k records */ 
SELECT n3.A,n3.B,n3.C
FROM dbTable n3,
(
/* n2 = Get the worst value of B per A for the top-k records */ 
SELECT A, MAX(B) AS B
FROM 
/* n1 = Count records per A, ordered by B */ 
(SELECT A, B, C,
        row_number() over (partition BY A ORDER BY B,C)  AS counter
  FROM dbTable) n1

WHERE n1.counter<=100 /* k=100 */
GROUP BY A) n2

WHERE n3.A=n2.A AND n3.B<=n2.B
ORDER BY n3.A,n3.B,n3.C;

    Parece correto, mas, por favor, identifique qualquer possível descuido.

    • 0

relate perguntas