Re: Searching a gin index
От | Oleg Bartunov |
---|---|
Тема | Re: Searching a gin index |
Дата | |
Msg-id | Pine.LNX.4.64.0902061806540.4158@sn.sai.msu.ru обсуждение исходный текст |
Ответ на | Re: Searching a gin index (James Dooley <jamdooley@gmail.com>) |
Ответы |
Re: Searching a gin index
(James Dooley <jamdooley@gmail.com>)
|
Список | pgsql-general |
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@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: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83
В списке pgsql-general по дате отправления: