id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
4676	Full text search support for PostgreSQL (postgresql_psycopg2 backend)	djangoproject.com@…	nobody	"The small patch attached to this ticket adds full text search support via the already implemented field lookup {{{search__}}}.

The one and only (maintained) way to have full text search in PostgreSQL is via the extension [http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Tsearch2] which is already included in most of the distributions.

In order to be able to index a field for full text search you have to do the following steps:
 * Make sure your PostgreSQL has the Tsearch2 module
 * Set-Up Tsearch2 for your Django project (as postgres user):
   * psql yourdatabase < /path/to/tsearch2/contrib/script/tsearch2.sql
   * In psql shell: GRANT ALL ON public.pg_ts_cfgmap TO djangoDBuser;
   * In psql shell: GRANT ALL ON public.pg_ts_cfgmap TO djnagoDBuser;
   * In psql shell: GRANT ALL ON public.pg_ts_dict TO djangoDBuser;
   * In psql shell: GRANT ALL ON public.pg_ts_parser TI djangoDBuser;
 * Add an extra column for the table you want to use the full text search index on (as djangoDBuser). Make sure that the column name (in the example fieldvariable_tsv) has the EXACT name of the field you want to search on plus _tsv on the end (otherwise it won't work)
   * Example: ALTER TABLE your_tablename ADD COLUMN fieldvariable_tsv tsvector; 
 * Create an index on the full text search field (as djangoDBuser):
   * CREATE INDEX djangoapp_columnname_tsv_index ON your_tablename USING gist(fieldvariable_tsv);
 * Finally update the full text index for the data already in the database (as djngoDBuser):
   * UPDATE your_tablename SET fieldvariable_tsv = to_tsvector(fieldvariable);
 * Finally apply the attached patch

Now you should be able to do something like this:

{{{MyModel.objects.filter(fieldvariable__search = 'some text')}}}

in your app which is normally blinding fast, even with millions of records in the database:"		closed	Core (Other)	dev		duplicate	postgresql full text search	elaatifi@… sam@… johann.queuniet@…	Unreviewed	1	0	0	0	0	0