On Fri, 20 Mar 2009, esemba wrote:
Well, thank you both for response, but I'm not sure, I understand Oleg's
solution. This would work, but where is the variability of searched columns?
In your example, I create new indexed column with concatenated vectors of 2
columns. But I sometimes new to search only annotation, sometimes resume,
sometomes both.
if you assign different labels to the concatenated columns, you can
specify in query which columns you're interested in. Also, you
can explicitly specify weight=0 for columns you're not interested.
Oleg Bartunov wrote:
On Thu, 19 Mar 2009, esemba wrote:
Hi,
I have table with several columns and need to perform fulltext search
over
volatile number of columns.
I can't use multicolumn gist index or gin index over concatenated
columns,
so I've created several single column indexes (one for each column I want
to
search) and now I need to query them like this:
to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');
alter table YOURTABLE add columnt fts tsvector;
update YOURTABLE set fts=
to_tsvector('cs', coalesce(annotation, '')) ||
to_tsvector('cs', coalesce(resume, '')) || ...
create index fts_idx on YOURTABLE using gin(fts);
vacuum analyze YOURTABLE;
select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
This query works, but EXPLAIN has shown me, that postgres doesn't use the
indexes, so the query over a table with several thousands of records last
very long time. I've figured out, that indexes probably cannot be used
this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns
to
search.
Thank you for your reply.
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
