BTW, Oleg I don't need colasque since those values can't be null.
On Fri, Feb 6, 2009 at 4:11 PM, Oleg Bartunov <o...@sai.msu.su> wrote: > James, > > syntax is documented on > > http://www.postgresql.org/docs/8.3/static/textsearch-tables.html#TEXTSEARCH-TABLES-SEARCH > and in the Introduction > http://www.postgresql.org/docs/8.3/static/textsearch-intro.html#TEXTSEARCH-MATCHING > text-search operator was specified for tsvector @@ tsquery. > You did wrong twice, you didn't specified type tsvector and you forgot > about coalesce. > > There is general rule for partial indexes - you should use the same > expression in query as you used in create index command. > > Oleg > On Fri, 6 Feb 2009, James Dooley wrote: > > Oleg, but I am only interested in whether or not the syntax of my >> search-query is correct. >> >> Having created the index as I mentioned above, would the correct way of >> searching and using that index be >> >> ... AND (title || '' || description || '' || name) @@ >> plainto_tsquery('car') >> >> or should it be as Richard just mentioned >> >> ... AND to_tsvector(title || '' || description || '' || name) @@ >> plainto_tsquery('car') >> >> or some other way ? >> >> >> >> On Fri, Feb 6, 2009 at 3:30 PM, Richard Huxton <d...@archonet.com> wrote: >> >> James Dooley wrote: >>> >>>> Hi again, >>>> >>>> I have set my configuration as default and I have created a GIN index on >>>> three columns, like this >>>> >>>> create index textsearch_index on products using gin(strip( to_tsvector( >>>> 'my_config', title || '' || description || '' || name))) >>>> >>>> Searching these columns the way I have >>>> >>>> ... AND (title || '' || description || '' || name) @@ >>>> >>> plainto_tsquery('car') >>> >>>> >>>> seems not to be correct since it's taking as much time as non-indexed. >>>> >>> >>> PG's planner isn't smart enough to transform a complex expression so as >>> to use a functional index (which is what you've got). You need to >>> mention the function explicitly. >>> >>> So, if you had: >>> CREATE INDEX lowercase_idx ON mytable ( lower(mycolumn) ) >>> You then need to search against "lower(mycolumn)" and not just expect >>> the planner to notice that mycolumn="abc" could use the index. >>> >>> The following should work for you as a starting point: >>> >>> CREATE TABLE tsearch_tbl (id SERIAL, title text, body text, >>> PRIMARY KEY (id)); >>> >>> INSERT INTO tsearch_tbl (title, body) >>> SELECT 'title number ' || n, 'This is body number ' || n >>> FROM generate_series(1,9999) n; >>> >>> ANALYSE tsearch_tbl; >>> >>> CREATE INDEX tsearch_tbl_words_idx ON tsearch_tbl USING gin ( >>> to_tsvector('english', title || body) ); >>> >>> EXPLAIN ANALYSE SELECT * FROM tsearch_tbl WHERE to_tsvector('english', >>> title || body) @@ to_tsquery('17'); >>> QUERY PLAN >>> >>> >>> >>> ------------------------------------------------------------------------------------------------------------------------------- >>> Bitmap Heap Scan on tsearch_tbl (cost=4.34..34.76 rows=10 width=45) >>> (actual time=0.067..0.067 rows=1 loops=1) >>> Recheck Cond: (to_tsvector('english'::regconfig, (title || body)) @@ >>> to_tsquery('17'::text)) >>> -> Bitmap Index Scan on tsearch_tbl_words_idx (cost=0.00..4.34 >>> rows=10 width=0) (actual time=0.059..0.059 rows=1 loops=1) >>> Index Cond: (to_tsvector('english'::regconfig, (title || body)) >>> @@ to_tsquery('17'::text)) >>> Total runtime: 0.121 ms >>> >>> >>> Note that you'll have problems if any of your text-fields contain nulls >>> (since null || anything = null). >>> >>> Personally, unless I'm dealing with a large table, I like to have a >>> separate tsvector column which I keep up to date with triggers. It makes >>> it easier to debug problems. >>> >>> -- >>> Richard Huxton >>> Archonet Ltd >>> >>> >> > Regards, > Oleg > _____________________________________________________________ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: o...@sai.msu.su, > http://www.sai.msu.su/~megera/<http://www.sai.msu.su/%7Emegera/> > phone: +007(495)939-16-83, +007(495)939-23-83 >
