2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / El Planeta PostgreSQL de Jaime3 / Indexando ando: Índices GIN
Jaime Casanova

Indexando ando: Índices GIN

April 2, 2014/0 Comments/in El Planeta PostgreSQL de Jaime, PostgreSQL /by Jaime Casanova

PostgreSQL tiene varios tipos de índices: B-tree, Hash, GiST, Gin y SP-GiST. Obviamente cada uno de ellos cubre una necesidad específica. Por ejemplo, la documentación de PostgreSQL dice sobre los índices GIN:

Los índices GIN son índices invertidos que pueden manejar valores que contienen más de una clave, arreglos, por ejemplo. Como GiST y SP-GiST, GIN puede soportar diferentes estrategias de indexación definidos por el usuario y los operadores particulares con la que un índice GIN se puede utilizar varía en función de la estrategia de indexación. A modo de ejemplo, la distribución estándar de PostgreSQL incluye clases de operador GIN para arreglos unidimensionales, que admiten consultas indexadas utilizando estos operadores:

<@
@>
=
&&

(Consulte la Sección 9.18 para el significado de estos operadores.) Muchas otras clases de operadores GIN están disponibles en la colección contrib o como proyectos separados. Para obtener más información, consulte el Capítulo 57.

Así que los índices GIN pueden usarse para indexar los elementos de un arreglo, de un hstore y así.

Pero en esta ocasión hablaremos de uno de esos módulos contrib que proveen mas clases de operadores que pueden usarse con los índices GIN: pg_trgm.

Este módulo crea trigramas de cadenas de texto de modo que puede usarse para encontrar similitudes. Eso permite que los índices tipo GIN que usen la clase de operador gin_trgm_ops puedan ser usados en busquedas con LIKE aun cuando el comodín ‘%’ se encuentre al inicio del patrón de busqueda (por ejemplo: nombre LIKE ‘%jaime%’).

Para crear un índice que pueda usarse así, el índice debe crearse de este modo:

CREATE INDEX idx_gin ON table USING GIN (campo_texto gin_trgm_ops);

Con un índice como este he visto consultas bajar de más de 10s a unos pocos milisegundos; sin embargo, antes que corran a crear estos índices consideremos los problemas que tiene.

Considere la siguiente consulta “select show_trgm(‘Jaime Casanova’);” esta nos muestra los trigramas de una cadena de texto, en este caso 15 trigramas. Así que no es difícil imaginar que este tipo de índices crece mucho, y mientras mas grandes las cadenas de texto más crece el índice (debido a que habrá mas trigramas). Otra conclusión obvia es que mantener este tipo de índices puede ser costoso, de hecho pueden afectar mucho el rendimiento de INSERT y UPDATE, especialmente si hay varios de estos índices en la misma tabla, para disminuir un poco este problema se invento una técnica llamada fastupdate que consiste en mantener una lista no ordenada de pendientes. Así que los INSERT y UPDATE en lugar de insertar en el índice principal lo hacen en esta estructura de adicional hasta que ocurra un VACUUM o hasta que la lista de pendientes se haga mas grande que work_mem. Los inconvenientes son: 1) al leer el índice también debe leerse esta estructura adicional, lo que puede afectar al rendimiento de las consultas; y 2) un INSERT o UPDATE puede causar que la lista de pendientes se crezca demasiado y por lo tanto empezará procesar la lista de pendientes lo que afectará a ese INSERT o UPDATE y a todas las demás operaciones que esten ocurriendo de forma concurrente sobre esa tabla.

En conclusión; un índice GIN junto con el módulo pg_trgm puede ayudar enormemente al rendimiento de algunas consultas, sin embargo no se debe abusar de ellos pues pueden resultar un arma de doble filo.

Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Announcing repmgr 2.0 Announcing Barman 1.3.1
Scroll to top
×