CODEWITHSUNDEEP
postgresqlsearch
sennin
sennin

Reputation: 8972

Fast search within strings in PostgreSQL

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

Upvotes: 0

Views: 1093

Answers (2)

user330315
user330315

Reputation:

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.

Upvotes: 1

Frank Heikens
Frank Heikens

Reputation: 127556

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.

Upvotes: 1

Related Questions