'PostgreSQL not using any index in regex search
I have the following SQL statement to filter data with a regex search:
select * from others.table
where vintage ~* '(17|18|19|20)[0-9]{2,}'
Upon some researching, I found that I need to create gin/gist index for better performance:
create index idx_vintage_gist on others.table using gist (vintage gist_trgm_ops);
create index idx_vintage_gin on others.table using gin (vintage gin_trgm_ops);
create index idx_vintage_varchar on others.table using btree (vintage varchar_pattern_ops);
Looking at the explain plan, it is not using any index but a seq scan:
Seq Scan on table t (cost=0.00..45412.25 rows=1070800 width=91) (actual time=0.038..8518.830 rows=1075980 loops=1)
Filter: (vintage ~* '(17|18|19|20)[0-9]{2,}'::text)
Rows Removed by Filter: 25400
Planning Time: 0.481 ms
Execution Time: 8767.998 ms
There are total 1101380 rows in the table.
My question is why is it not using any index for the regex search?
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
