Re: Full text search ordering question

John,

it's a good tradition to include query and their EXPLAIN ANALYZE. 
Pg version is also useful.
Did you try GIN index ?
In 8.4 you can use gin index on (views,tsvector)

Oleg

On Tue, 25 Nov 2008, John Lister wrote:

> Hi, is it possible to order the results of a full text search using another 
> field?
>
> for example with the following table:
>
> CREATE TABLE breadcrumbs (
> node_id integer NOT NULL,
> breadcrumb character varying,
> textsearchable tsvector,
> views integer,
> CONSTRAINT pk_breadcrumbs PRIMARY KEY (node_id)
> )
>
> I'd like to do something like this
>
> select node_id, views from breadcrumbs  where textsearchable @@ 
> to_tsquery('word') order by views desc limit 100;
>
> As such I'd like to create a fts index on the textsearchable field and views 
> field such that it orders the results by the views column.
>
> atm, this table has over 3M rows (and is likely to b magnitudes bigger) and 
> some words match hundreds of thousands of rows, The best i've got so far is 
> to create a fts index which is used and then the resulting rows are sorted in 
> memory. Unfortunately because of the number of rows returned this takes a few 
> seconds.
>
> With a btree index i could index on the 2 columns and it would only hit the 
> index and take a fraction of a second.
>
> I've tried the btree_gist module, but it doesn't make any difference (except 
> in letting me use an int in the gist index)
>
> Any ideas or is this simply not possible?
>
> Thanks
>
>
> --
>
> Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/ 
>
>
    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