Search Postgresql Archives

two questions about fulltext searchign / tsvector indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



I'm having some issues with fulltext searching.  

I've gone though the list archives and stack overflow, but can't seem to get the exact answers.  hoping someone can help.

Thanks in advance and apologies for these questions being rather basic.  I just felt the docs and some online posts are leading me into possibly making the wrong decision and I want to make sure I"m doing this right.


1.  I need to make both 'title' and 'description' searchable.   What is the current proper way to index multiple columns of a table ( ie, not one ) ?

	I've essentially seen the following in the docs, mailing list, and various websites:

	A unified index
		CREATE INDEX CONCURRENTLY unified_tsvector_idx ON mytable USING gin(to_tsvector('english', title || ' ' || description ));

	Individual indexes
		CREATE INDEX CONCURRENTLY title_tsvector_idx ON mytable USING gin(to_tsvector('english', title ));
		CREATE INDEX CONCURRENTLY description_tsvector_idx ON mytable USING gin(to_tsvector('english', description ));

	Using dedicated columns ( one or more )
		ALTER TABLE .... 
		create trigger ....

	I can't figure out which one to use.  This is on a steadily growing table of around 20MM rows that gets 20-80k new records a day, but existing records are rarely updated.
		

2. I've been getting a handful of 'can not index words longer than 2047 characters' in my tests.  

	if this 2047 character max is on tokens, is there a way to lower it?  or to profile the index for distribution of tokens ?  I don't think we have to support any tokens larger than 20chars or so.

3a. What should EXPLAIN ANALYZE show if it is using the index ?  i couldn't find an example.

3b. Depending on how I index the column, what do I need to pass into the query so that it uses the index ?

	1. 	if the index is created like 
			gin(to_tsvector('english', title ));

		do i have to search in this format ?
			to_tsvector('english',title) @@ to_tsquery('english', 'dog') ;

	2. 	if i use an index like 
			 gin(to_tsvector('english', title || ' ' || description ));
 
		what is the correct way to query the database and let the planner know I want to use the index ?






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux