postgresqldatabase-indexestrigram

Why does PostgreSQL not use the trigram index?


I have created a trigram index in order do execute a query with a like '%text%' condition, but PostgreSQL 9.6 doesn't use the index to perform the query.

CREATE EXTENSION pg_trgm;  

CREATE INDEX tb_estabelecimento_index08
  ON tb_estabelecimento
  USING gin
  (nm_estabelecimento COLLATE pg_catalog."default" 
  gin_trgm_ops);

When I execute the query:

SELECT * FROM tb_estabelecimento WHERE 
nm_estabelecimento LIKE '%SOMETEXT%'

PostgreSQL gives me the query plan:

Seq Scan on tb_estabelecimento  (cost=0.00..1.16 
rows=1 width=1706)
Filter: ((nm_estabelecimento)::text ~~                
'%SOMETEXT%'::text)"

Why does PostgreSQL execute a sequential scan instead of using the index?

My table:

CREATE TABLE tb_estabelecimento
(
  id_estabelecimento integer NOT NULL,
  nm_estabelecimento character varying(100) NOT NULL, 
  ds_url_website character varying(1000), 
  nm_municipio character varying(200), 
  id_unidade_federacao integer NOT NULL,
  CONSTRAINT tb_estabelecimento_pk PRIMARY KEY (id_estabelecimento),
  CONSTRAINT tb_estabelecimento_uk UNIQUE (nm_estabelecimento, nm_municipio, id_unidade_federacao)

My database:

CREATE DATABASE my_database_name
  WITH OWNER = postgres
       ENCODING = 'UTF8'
       TABLESPACE = pg_default
       LC_COLLATE = 'Portuguese_Brazil.1252'
       LC_CTYPE = 'Portuguese_Brazil.1252'
       CONNECTION LIMIT = -1;

Solution

  • You don't tell us how many rows the table has, but I guess there are few enough that PostgreSQL always uses a sequential scan (because it is cheapest).

    To see if your index can be used, temporarily discourage the optimizer from using sequential scans:

    SET enable_seqscan = off;
    

    If your query does not use the index after that, the index is really unusable (but it looks ok to me).