Início / dba / Perguntas / 297372
Accepted
Nathan Long
Asked: 2021-07-30 12:35:47 +0800 CST

Existe um índice que suporte a comparação de valores de 2 tuplas?

  • 772

Quero implementar a paginação baseada em cursor para um grande conjunto de dados.

Com OFFSETpaginação baseada, quando o usuário quiser page N, você apenas OFFSET N * page_size. A consulta termina assim:

SELECT *
FROM books
ORDER BY id
OFFSET 100000
LIMIT 10;

Mas OFFSETfica mais lento quanto maior o valor dado porque o PostgreSQL precisa carregar e descartar as linhas anteriores.

Uma abordagem baseada em cursor é onde dizemos ao usuário "aqui está a página N, e como o último registro nela tem , idpara Xobter a próxima página, você deve me pedir registros com id > X." A consulta termina assim:

SELECT *
FROM books
WHERE id > 100000
ORDER BY id
LIMIT 10;

Nesse caso, o PostgreSQL pode carregar apenas as linhas necessárias.

Isso funciona muito bem ao classificar por arquivos id. Mas eu gostaria de poder classificar por outras colunas - por exemplo, por title- e ainda paginar.

O problema é que titlenão é único. Portanto, se o registro final na página Ntiver o título "Sobre Doninhas" e houver vários livros com esse título, solicitar a próxima página WHERE title > 'About Weasels'pode pular alguns deles.

Eu posso obter valores exclusivos tendo a solicitação do usuário WHERE (title, id) > ('About Weasels', 100000), mas isso funciona mal porque o PostgreSQL precisa calcular (title, id)para cada linha.

Eu tentei adicionar um índice para pré-computar essa 2-tupla:CREATE INDEX books_title_and_id ON books (title, id);

...mas esse índice não faz diferença para o plano de consulta.

Existe um índice que eu poderia criar para acelerar essa consulta?

postgresql index

1 respostas

  1. Best Answer

    Aqui está um exemplo usando seu índice:

    explain analyze 
select * from books WHERE 
(title, id) > ('KVFNdl5F', 994364) 
order by title, id 
limit 10;

    Observe que a primeira ocorrência de title, id(na comparação >) deve estar entre parênteses, e a segunda ocorrência (no ORDER BY) não deve estar entre parênteses.

                                                                    QUERY PLAN                                                                 
-------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..1.09 rows=10 width=45) (actual time=0.062..0.081 rows=10 loops=1)
   ->  Index Scan using books_title_and_id on books  (cost=0.42..42823.96 rows=644090 width=45) (actual time=0.060..0.077 rows=10 loops=1)
         Index Cond: (ROW(title, id) > ROW('KVFNdl5F'::text, 994364))
 Planning Time: 0.107 ms
 Execution Time: 0.109 ms
    • 2

relate perguntas