Re: Searching a gin index

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 <oleg(at)sai(dot)msu(dot)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 <dev(at)archonet(dot)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: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/<http://www.sai.msu.su/%7Emegera/>
> phone: +007(495)939-16-83, +007(495)939-23-83
>