1. 7
  1.  

  2. 2

    I usually do this for shorter strings:

    CREATE EXTENSION btree_gist;
    CREATE EXTENSION pg_trgm;
    
    CREATE INDEX "idx1"
      ON data
      USING gist
      (((name::text) COLLATE pg_catalog."default" gist_trgm_ops);
    

    Now, construct the query like so in 9.1+:

    SELECT name FROM data WHERE name LIKE '%Jack%';
    

    This will make an index-only scan and that’s not all! Works very well with UTF-8 too.

    The trigram documentation has a bit more search options (regex in 9.3+).