How to create index in postgresql for regexp_matches?

user_12964881 0
Viewed 530

I have a table product 

product_id | desciption                                     
============================================================
322919     | text {add}185{/add} text                       
322920     | text {add}184{/add} text {add}185{/add} text   
322921     | text {add}185{/add} text {add}187{/add} text

sql query with like is very slow

SELECT product_id, desciption 
FROM product 
WHERE LOWER(desciption) like '%{add}185{/add}%'
> Time: 340,159s

I only need an index to search for {add}185{/add} expressions. i.e. need to make an index for this table

SELECT product_id, regexp_matches (desciption, '(\{add\}\d+\{\/add\})', 'g') 
FROM product

returns:

product_id | regexp_matches 
================================================================================
322919     | {"{add}185{/add}"}
322920     | {"{add}184{/add}"}
322920     | {"{add}185{/add}"}
322921     | {"{add}185{/add}"}
322921     | {"{add}187{/add}"}
  1. Which is better to create an index for data sampling?
  2. Which is better to use the expression in "WHERE"?
indexing postgresql sql
2 Answers

The easiest solution is just to build a pg_trgm index.

 create extension pg_trgm;
 create index on product using gin (description gin_trgm_ops);

Then you can use the same query, only remove the LOWER and change the LIKE to ILIKE.

That should probably be good enough, but if it isn't you can make a more targeted index. You will need to create a helper function to do an aggregation, as you can't put an aggregate directly into a functional index.

create function extract_tokens(text) returns text[] immutable language sql as $$ 
   select array_agg(regexp_matches[1]) from 
      regexp_matches ($1, '\{add\}(\d+)\{\/add\}+','g') 
$$;

Note that I moved the capturing parenthesis in, so they only get the digits and not the surrounding tags, which just seem like noise. The fact that there was a match is evidence they were there, we don't need to see them.

create index on product using gin (extract_tokens(description))

select * from product where extract_tokens(description) @> ARRAY['185'];
user_17212390

For better searching you need to create index for column 'description'

When using like, remember only this wildcard work with index 

SELECT product_id, desciption FROM product WHERE LOWER(desciption) like '{add}185{/add}%'

So your query above not work with index

user_14209450
Related