Opened 17 years ago

Closed 17 years ago

Last modified 16 years ago

#4676 closed (duplicate)

Full text search support for PostgreSQL (postgresql_psycopg2 backend)

Reported by: djangoproject.com@… Owned by: nobody
Component: Core (Other) Version: dev
Severity: Keywords: postgresql full text search
Cc: elaatifi@…, sam@…, johann.queuniet@… Triage Stage: Unreviewed
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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 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:

Attachments (1)

sql.diff (728 bytes ) - added by djangoproject.com@… 17 years ago.
Patch againt svn revision 5526

Download all attachments as: .zip

Change History (8)

by djangoproject.com@…, 17 years ago

Attachment: sql.diff added

Patch againt svn revision 5526

comment:1 by djangoproject.com@…, 17 years ago

You might also want to set up a PostgreSQL Trigger on your table for updates and inserts, which looks something like that:

CREATE TRIGGER tsvectorupdate BEFORE UPDATE OR INSERT ON your_tablename FOR EACH ROW EXECUTE PROCEDURE tsearch2(fieldvariable_tsv, fieldvariable)

comment:2 by Amirouche B., 17 years ago

How can use this to search on several column ?

I tried to get some inspiration from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
and http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html

but it's hard to mix with django.

in reply to:  2 comment:3 by anonymous, 17 years ago

Replying to Amirouche B.:

How can use this to search on several column ?

I tried to get some inspiration from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
and http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html

but it's hard to mix with django.

You can check this link, http://smelaatifi.blogspot.com/2007/09/full-text-search-with-tsearch2-and.html,
there is a solution to use it with django and an index over several columns.

comment:4 by anonymous, 17 years ago

Cc: elaatifi@… added

comment:5 by James Bennett, 17 years ago

Resolution: duplicate
Status: newclosed

Both this ticket and #3524 deal with the same issues and have patches dating from the same day. #3524 deals with more than just Postgres, though, so I'll close this one in favor of it; could the author of this patch take a look at the other ticket and look into joining efforts on this feature?

comment:6 by anonymous, 16 years ago

Cc: sam@… added

comment:7 by Johann Queuniet <johann.queuniet@…>, 16 years ago

Cc: johann.queuniet@… added

Referenced ticket is #3254, not #3524 (since Google seems to prefer this ticket to the other).

Note: See TracTickets for help on using tickets.
Back to Top