Realizar una recuperación top-k eficiente

Muchas aplicaciones consultan una base de datos para rellenar una sola página en sus aplicaciones. En estas aplicaciones, no se necesitan todas las coincidencias, sino solo las k coincidencias principales según el orden de clasificación del índice. Los índices de búsqueda pueden implementar este tipo de búsqueda de forma muy eficiente. En esta página se describe cómo crear y buscar un índice que tenga una coincidencia de los k elementos principales.

Crear índices de búsqueda para las k coincidencias principales

Para configurar un índice de búsqueda para la coincidencia de los k elementos principales, usa ORDER BY para ordenar el índice de búsqueda por una columna específica. Las consultas deben tener una cláusula ORDER BY que coincida exactamente con el orden de clasificación del índice de búsqueda (incluida la dirección ascendente o descendente) y una cláusula LIMIT que solicite que la consulta se detenga después de encontrar k filas coincidentes.

También puedes implementar la paginación con estas cláusulas. Para obtener más información, consulta el artículo Paginación de consultas de búsqueda.

En algunos casos prácticos, puede ser útil mantener varios índices de búsqueda ordenados por diferentes columnas. Al igual que el particionado, se trata de un equilibrio entre el almacenamiento y el coste de escritura, por un lado, y la latencia de las consultas, por otro.

Por ejemplo, supongamos que tiene una tabla con el siguiente 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 búsqueda para obtener las k mejores coincidencias

Como se ha indicado anteriormente, las consultas deben tener una cláusula ORDER BY que coincida exactamente con el orden de clasificación del índice de búsqueda (incluida la dirección ascendente o descendente) y una cláusula LIMIT que solicite que la consulta se detenga después de encontrar k filas coincidentes.

En la siguiente lista se analiza la eficiencia de algunas consultas habituales.

  • Esta consulta es muy eficiente. Selecciona el AlbumsRecordTimestampIndex índice. Aunque haya muchos álbumes con la palabra "feliz", la consulta solo analiza un pequeño número de filas:

    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

  • La misma consulta, que solicita el orden de clasificación por ReleaseTimestamp en orden descendente, usa el índice AlbumsReleaseTimestampIndex y es igual de 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

  • Una consulta que solicita el orden de clasificación por ListenTimestamp no ejecuta una consulta de los k elementos principales de forma eficiente. Tiene que obtener todos los álbumes coincidentes, ordenarlos por ListenTimestamp, y devolver los 10 primeros. Una consulta de este tipo usa más recursos si hay un gran número de documentos que contienen el término "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

  • Del mismo modo, una consulta no se ejecuta de forma eficiente si solicita que los resultados se ordenen mediante la columna RecordTimestamp en orden ascendente. Analiza todas las filas que contienen la palabra "happy", aunque tengan un 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

Siguientes pasos