Opened 8 years ago

Closed 8 years ago

Last modified 8 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: master
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: UI/UX:

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@… 8 years ago.
Patch againt svn revision 5526

Download all attachments as: .zip

Change History (8)

Changed 8 years ago by djangoproject.com@…

Patch againt svn revision 5526

comment:1 Changed 8 years ago by djangoproject.com@…

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset

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 follow-up: Changed 8 years ago by 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.

comment:3 in reply to: ↑ 2 Changed 8 years ago by anonymous

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 Changed 8 years ago by anonymous

  • Cc elaatifi@… added

comment:5 Changed 8 years ago by ubernostrum

  • Resolution set to duplicate
  • Status changed from new to closed

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 Changed 8 years ago by anonymous

  • Cc sam@… added

comment:7 Changed 8 years ago by Johann Queuniet <johann.queuniet@…>

  • 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