Realize uma obtenção top-k eficiente

Muitas aplicações consultam uma base de dados para preencher uma única página nas respetivas aplicações. Nestas aplicações, a aplicação não precisa de todas as correspondências, mas apenas das k principais correspondências com base na ordem de ordenação do índice. Os índices de pesquisa podem implementar este tipo de pesquisa de forma muito eficiente. Esta página descreve como criar e pesquisar um índice que tenha correspondência top-k.

Crie índices de pesquisa para as principais k correspondências

Para configurar um índice de pesquisa para a correspondência top-k, use ORDER BY para ordenar o índice de pesquisa por uma coluna específica. As consultas têm de ter uma cláusula ORDER BY que corresponda exatamente à ordem de ordenação do índice de pesquisa (incluindo a direção ascendente versus descendente) e uma cláusula LIMIT que peça à consulta para parar depois de encontrar k linhas correspondentes.

Também pode implementar a paginação através destas cláusulas. Para mais informações, consulte o artigo Paginar consultas de pesquisa.

Para alguns exemplos de utilização, pode ser útil manter vários índices de pesquisa ordenados por colunas diferentes. Tal como a partição, é um equilíbrio entre o custo de armazenamento e gravação versus a latência de consulta.

Por exemplo, considere uma tabela que usa o seguinte esquema:

GoogleSQL 

CREATE TABLE Albums (
  AlbumId STRING(MAX) NOT NULL,
  RecordTimestamp INT64 NOT NULL,
  ReleaseTimestamp INT64 NOT NULL,
  ListenTimestamp INT64 NOT NULL,
  AlbumTitle STRING(MAX),
  AlbumTitle_Tokens TOKENLIST AS (TOKENIZE_FULLTEXT(AlbumTitle)) HIDDEN
) PRIMARY KEY(AlbumId);

CREATE SEARCH INDEX AlbumsRecordTimestampIndex
ON Albums(AlbumTitle_Tokens, SingerId_Tokens)
STORING (ListenTimestamp)
ORDER BY RecordTimestamp DESC

CREATE SEARCH INDEX AlbumsReleaseTimestampIndex
ON Albums(AlbumTitle_Tokens)
STORING (ListenTimestamp)
ORDER BY ReleaseTimestamp DESC

PostgreSQL 

CREATE TABLE albums (
  albumid character varying NOT NULL,
  recordtimestamp bigint NOT NULL,
  releasetimestamp bigint NOT NULL,
  listentimestamp bigint NOT NULL,
  albumtitle character varying,
  albumtitle_tokens spanner.tokenlist
      GENERATED ALWAYS AS (spanner.tokenize_fulltext(albumtitle)) VIRTUAL HIDDEN,
PRIMARY KEY(albumid));

CREATE SEARCH INDEX albumsrecordtimestampindex
ON Albums(albumtitle_tokens, singerid_tokens)
INCLUDE (listentimestamp)
ORDER BY recordtimestamp DESC

CREATE SEARCH INDEX albumsreleasetimestampindex
ON Albums(albumtitle_tokens)
INCLUDE (listentimestamp)
ORDER BY releasetimestamp DESC

Consultar índices de pesquisa para as principais k correspondências

Conforme indicado anteriormente, as consultas têm de ter uma cláusula ORDER BY que corresponda exatamente à ordem de ordenação do índice de pesquisa (incluindo a direção ascendente em comparação com a direção descendente) e uma cláusula LIMIT que peça à consulta para parar depois de encontrar k linhas correspondentes.

A lista seguinte analisa a eficiência de algumas consultas comuns.

  • Esta consulta é muito eficiente. Seleciona o AlbumsRecordTimestampIndex índice. Mesmo que existam muitos álbuns com a palavra "feliz", a consulta apenas analisa um pequeno número de linhas:

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY RecordTimestamp DESC
LIMIT 10

    PostgreSQL 

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY recordtimestamp DESC
LIMIT 10

  • A mesma consulta, que pede a ordem de ordenação por ReleaseTimestamp em ordem descendente, usa o índice AlbumsReleaseTimestampIndex e é igualmente eficiente:

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY ReleaseTimestamp DESC
LIMIT 10

    PostgreSQL 

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY releasetimestamp DESC
LIMIT 10

  • Uma consulta que pede a ordem de classificação por ListenTimestamp não executa uma consulta top-k de forma eficiente. Tem de obter todos os álbuns correspondentes, ordená-los por ListenTimestamp, e devolver os 10 principais. Uma consulta deste tipo usa mais recursos se houver um grande número de documentos que contenham o termo "feliz".

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY ListenTimestamp DESC
LIMIT 10

    PostgreSQL 

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY listentimestamp DESC
LIMIT 10

  • Da mesma forma, uma consulta não é executada de forma eficiente se pedir que os resultados sejam ordenados através da coluna RecordTimestamp por ordem ascendente. Analisa todas as linhas com a palavra "feliz", apesar de ter um LIMIT.

    GoogleSQL 

    SELECT AlbumId
FROM Albums
WHERE SEARCH(AlbumTitle_Tokens, 'happy')
ORDER BY RecordTimestamp ASC
LIMIT 10

    PostgreSQL 

    SELECT albumid
FROM albums
WHERE spanner.search(albumtitle_tokens, 'happy')
ORDER BY recordtimestamp ASC
LIMIT 10

O que se segue?