how free text search works in postgresql -

i working on project in have implement free text using postgresql database, not able understand how can work using @@ instead of command.

i have table

employee - id - name - location - managername  //my query  select * employee name @@ 'xxx' 

i searching on name free text , gives result, can nay 1 tell me index or search catalogue, didn't made , configuration etc still working.

any chance postgresql tables or created catalogue on runtime?

can 1 tell me how works?

what's happening

if @ operators defined @@ you'll see there few variants different data types. they're overloads same operator.

regress=> \do @@                                         list of operators    schema   | name | left arg type | right arg type | result type |         description           ------------+------+---------------+----------------+-------------+------------------------------  pg_catalog | @@   | text          | text           | boolean     | text search match  pg_catalog | @@   | text          | tsquery        | boolean     | text search match  pg_catalog | @@   | tsquery       | tsvector       | boolean     | text search match  pg_catalog | @@   | tsvector      | tsquery        | boolean     | text search match 

now, if a @@ b a , b both text fields (or varchar, gets converted text), calls @@(text, text) operator.

this converts inputs automatically text search token lists, compares them.

so it's shorthand to_tsvector(a) @@ to_tsquery(b). why works.

see, there 2 parts fulltext search: comparison operations tsearch vectors , queries fancy wildcards , stemming, , index support.

what you're using here comparison part, not index. index can make faster, doesn't add features.

step step

so still works:

regress=> select 'frightening' @@ 'frightened';  t (1 row) 

because it's converting both sides using @@(tsvector,tsquery) operator, like:

regress=> select to_tsvector('frightening') @@ to_tsquery('frightened');  t (1 row) 

it's using globally configured tsearch stemming dictionary, on system is:

regress=> show default_text_search_config;  default_text_search_config  ----------------------------  pg_catalog.english (1 row) 

so it's doing:

regress=> select to_tsvector('english', 'frightening') @@ to_tsquery('english', 'frightened');  t (1 row) 

let's @ each side separately:

which comparing:

regress=> select to_tsvector('english', 'frightening'), to_tsquery('english', 'frightened');  to_tsvector  | to_tsquery  --------------+------------  'frighten':1 | 'frighten' 

see it's done? it's taken words , trimmed them down root, or stem, using rules english language. why match.

so really, @@ operator text values shorthand. in case there is no index, because no index required, though having 1 might make things faster. if explain analyze query, you'll see it's still doing plain old seqscan.

hopefully this'll understand what's going on. i'm not sure - i'm relying on understanding of concepts data types , operator overloading may or may not familiar you, , i'm intentionally skipping on bits require explanations that'd more confusing (like how tell different @@ operators do).