I am using Postgres 9.5. It is possible this is related to the changes in 9.6 pointed out by PostgreSQL not using index during count(*), but I cannot be sure yet.
I have a table that has a
character varying column called
text. I am attempting to optimize queries which have to perform a regex on this column.
This often looks like
select * from table where text ~ 'some|key|words|we|want';
or the inverse
select * from table where text !~ 'some|key|words|we|want';
sometimes in joins
select category, count(*) from other_t join table on (other_t.table_id = table.id) where table.text ~ 'some|key|words|we|want'; select category, count(*) from other_t join (select id from table where table.text ~ 'some|key|words|we|want') as ti on (other_t = ti.id);
Notice that the regex is always the same.
I do not want to index the column directly because AFAIK indexing text columns results in a relatively huge index that would just be a waste of space. This rules out a partial index per the linked post above. So, I've been trying to use an expression index, which I should be able to do because the regex is always the same.
However, I can't get Postgres to use the index!
I've done a
vacuum analyze, I've tried every type of query I can imagine, but it always just does a
The column averages ~85 characters and typically only has one or two of the keywords we're looking for if it is a match.
I am not interested in full keyword search. We have a finite, stable list of words we're looking for. I don't want to make an enormous trigram/ts_vector index on this text column when all I really need is an expression index that Postgres decides to use.
We cannot eliminate the text column because it has other uses. We have already thought of adding a column which is essentially a cache of this query, but that's just ugly. If that is the best option, so be it, but we really should be able to use an index here.
create index text_col_idx on table ((text ~ 'some|key|words|we|want'));
explain (analyze, verbose) select id from table where text ~ 'some|key|words|we|want';
Seq Scan on public.table (cost=0.00..405624.24 rows=5190575 width=4) (actual time=0.113..52582.259 rows=5113014 loops=1) Output: id Filter: ((table.text)::text ~ 'some|key|words|we|want'::text) Rows Removed by Filter: 4728206 Planning time: 10.938 ms Execution time: 52766.607 ms