Why does PostgreSQL not use the trigram index?

Question

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;

Laurenz Albe · Accepted Answer · 2019-01-30 11:34:27Z

10

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).

answered Jan 30, 2019 at 11:34

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rodrigo Over a year ago

You're right. There are few rows in that table. After set seq scan off, PostgreSQL use the index. Thanks.

NiTiN Over a year ago

@Laurenz What do you mean by 'the index is really unusable'? In my case, the sequence scan is done even after disabling the sequence scan.

Laurenz Albe Over a year ago

@NiTiN That is an indication that the index cannot be used to support your query.

Laurenz Albe Over a year ago

@NiTiN By the way, there is no "sequence scan". You mean a "sequential scan".

Collectives™ on Stack Overflow

Why does PostgreSQL not use the trigram index?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related