Charles Lavery

Postgres Full Text Search Example

Postgres full text search on single column w/ index. 'english' config parameter is required due to the default value being mutable in the server configuration. Example below on NFL play data: http://www.advancednflstats.com/2010/04/play-by-play-data.html

> CREATE table...; COPY PLAYS FROM...;
> CREATE INDEX plays_desc_idx
    ON plays
    USING gin
    (to_tsvector('english', description));
> SELECT DISTINCT
    gameid,
    COUNT(*) OVER (partition by gameid) AS plays_per_game
  FROM
    plays,
    to_tsquery('english', 'T.Brady') as query
  WHERE
    to_tsvector('english', description) @@ query AND
    season = '2011'
  ORDER BY gameid;

┌─────────────────┬────────────────┐
│     gameid      │ plays_per_game │
├─────────────────┼────────────────┤
│ [email protected]54 │
│ [email protected]45 │
│ [email protected]48 │
│ [email protected]37 │
│ [email protected]43 │
│ [email protected]49 │
│ [email protected]41 │
│ [email protected]52 │
│ [email protected]44 │
│ [email protected]33 │
│ [email protected]43 │
│ [email protected]43 │
│ [email protected]43 │
│ [email protected]46 │
│ [email protected]59 │
│ [email protected]41 │
│ [email protected]38 │
│ [email protected]44 │
│ [email protected]45 │
└─────────────────┴────────────────┘
(19 rows)
 
Time: 11.078 ms