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