Fast search within strings in PostgreSQL

Question

Which is the fastest way to search within string in PostgreSQL (case insensivity):

SELECT col FROM table WHERE another_col ILIKE '%typed%'

or

SELECT col FROM table WHERE another_col ~* 'typed'

How can I turn on showing the time which query need to return results? Something like is on default in mySQL (I am thinking about CLI client).

Frank Heikens · Accepted Answer · 2011-01-26 11:30:59Z

1

Both queries are the same, PostgreSQL rewrites ILIKE to ~*. Check the results from EXPLAIN to see this behaviour.

I'm not sure about your question, but the psql-client can show you some timing of the query, using \timing.

answered Jan 26, 2011 at 11:30

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

2 Comments

This is not the same. If I write sth like that: ilike '%[text]%' this not the same as ~* '[text]'. I think this is something with regex.

This example is different, you have to escape the brackets in the regex if you want to search for brackets.

user330315 · Accepted Answer · 2011-01-26 11:36:28Z

1

Regarding the timing:

One solution is to use the switch for psql that Frank has already mentioned.

When you use EXPLAIN ANALZYE it also includes the total runtime of the query on the server.

I prefer this when comparing the runtime for different versions of a query as it removes the network from the equation.

answered Jan 26, 2011 at 11:36

user330315