Prácticas recomendadas del índice GIN
Un índice GIN es una estructura de datos que asocia una clave con su posting list. La clave indica un valor específico, y la posting list rastrea todas las ubicaciones en las que aparece esta clave. Por ejemplo, 'hello', '14:2 23:4' indica que hello se encuentra en las ubicaciones 14:2 y 23:4. Un índice GIN localiza de manera eficiente tuplas con palabras clave específicas, lo que lo hace ideal para buscar elementos dentro de campos de múltiples valores. Esta sección describe cómo utilizar índices GIN para buscar en tipos de arreglo y JSONB, así como cómo realizar búsquedas de texto completo.
Uso de un índice GIN para buscar en el tipo de arreglo
Cree un índice GIN para acelerar las búsquedas de etiquetas.
- Cree un clúster en la consola de DWS. Para obtener más información, consulte Creación de un clúster de DWS de almacenamiento y cómputo acoplados.
- Use the gsql client to connect to a DWS database (using the password you defined when creating the cluster).
1gsql -d gaussdb -p 8000 -h 192.168.0.86 -U dbadmin -W password -r
If the following information is displayed, the connection succeeded:
1gaussdb=>
- Cree la tabla books. La columna tags almacena la información de etiquetas de books utilizando el tipo de arreglo.
1CREATE TABLE books (id SERIAL PRIMARY KEY, title VARCHAR(100), tags TEXT[]);
- Inserte datos.
1 2 3 4 5
INSERT INTO books (title, tags) VALUES ('Book 1', ARRAY['fiction', 'adventure']), ('Book 2', ARRAY['science', 'fiction']), ('Book 3', ARRAY['romance', 'fantasy']), ('Book 4', ARRAY['adventure']);
- Cree un índice GIN.
1CREATE INDEX idx_books_tags_gin ON books USING GIN (tags);
- Utilice el índice GIN para realizar una consulta de búsqueda para encontrar libros que contengan una etiqueta específica en la columna tags. Busque libros que contengan la etiqueta "fiction":
1 2 3 4 5 6
SELECT * FROM books WHERE tags @> ARRAY['fiction']; id | title | tags ----+--------+--------------------- 1 | Book 1 | {fiction,adventure} 2 | Book 2 | {science,fiction} (2 rows)
- Utilice el índice GIN para buscar libros que contengan las etiquetas "fiction" y "adventure".
1 2 3 4 5
SELECT * FROM books WHERE tags @> ARRAY['fiction', 'adventure']; id | title | tags ----+--------+--------------------- 1 | Book 1 | {fiction,adventure} (1 row)
Uso de un índice GIN para buscar en el tipo JSONB
Cuando se utiliza el tipo JSONB para almacenar y consultar datos JSON, se pueden utilizar índices GIN para mejorar el rendimiento de las consultas. Los índices GIN son adecuados para consultar columnas JSONB que contienen una gran cantidad de pares clave-valor diferentes.
- Cree la tabla my_table. La columna data almacena información sobre cada persona que utiliza el tipo JSONB.
1CREATE TABLE my_table (id SERIAL PRIMARY KEY, data JSONB);
- Inserte datos.
1 2 3 4
INSERT INTO my_table (data) VALUES ('{"name": "John", "age": 30, "address": {"career": "announcer", "state": "NY"}}'), ('{"name": "Alice", "age": 25, "address": {"career": "architect", "state": "CA"}}'), ('{"name": "Bob", "age": 35, "address": {"career": "dentist", "state": "WA"}}');
- Cree un índice GIN para acelerar la consulta de columnas JSONB.
1CREATE INDEX my_table_data_gin_index ON my_table USING GIN (data);
- Utilice el índice GIN para realizar consultas en columnas JSONB. Por ejemplo, busque una persona cuyo trabajo sea dentista:
1 2 3 4 5
SELECT * FROM my_table WHERE data @> '{"address": {"career": "dentist"}}'; id | data ----+----------------------------------------------------------------------------- 3 | {"age": 35, "name": "Bob", "address": {"state": "WA", "career": "dentist"}} (1 row)
- Los índices GIN también se pueden consultar en las claves de las columnas JSONB. Por ejemplo, busque personas de 30 años o más:
1 2 3 4 5 6
SELECT * FROM my_table WHERE data ->> 'age' >= '30'; id | data ----+-------------------------------------------------------------------------------- 3 | {"age": 35, "name": "Bob", "address": {"state": "WA", "career": "dentist"}} 1 | {"age": 30, "name": "John", "address": {"state": "NY", "career": "announcer"}} (2 rows)
Uso de un índice GIN para la búsqueda de texto completo
Cuando se utilizan índices GIN para la búsqueda de texto completo, se pueden utilizar los tipos de datos tsvector y tsquery y las funciones relacionadas.
Para crear un objeto tsquery, se debe utilizar la función to_tsquery y proporcionar los criterios de búsqueda y la configuración de búsqueda de texto correspondiente (en este caso, english). También se pueden utilizar otras funciones y operadores de búsqueda de texto para búsquedas de texto completo más complejas, como plainto_tsquery y ts_rank. El uso específico depende de sus necesidades.
- Cree una tabla articles en la que la columna content almacene el contenido del artículo.
1CREATE TABLE articles (id SERIAL PRIMARY KEY,title VARCHAR(100),content TEXT);
- Inserte datos.
1 2 3 4 5
INSERT INTO articles (title, content) VALUES ('Article 1', 'This is the content of article 1.'), ('Article 2', 'Here is the content for article 2.'), ('Article 3', 'This article discusses various topics.'), ('Article 4', 'The content of the fourth article is different.');
- Crea una columna auxiliar tsvector para la columna content que almacena los índices de texto procesados.
1ALTER TABLE articles ADD COLUMN content_vector tsvector;
- Actualice el valor de la columna content_vector y convierta el texto de la columna Contenido al tipo tsvector.
1UPDATE articles SET content_vector = to_tsvector('english', content);
- Cree un índice GIN.
1CREATE INDEX idx_articles_content_gin ON articles USING GIN (content_vector);
- Realice una búsqueda de texto completo, utilizando el tipo tsquery para especificar los criterios de búsqueda. Por ejemplo, busque un artículo que contenga la palabra "content":
1SELECT * FROM articles WHERE content_vector @@ to_tsquery('english', 'content');
