From: | "John Lister" <john(dot)lister-ps(at)kickstone(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Full text search ordering question |
Date: | 2008-11-25 15:28:11 |
Message-ID: | 95906B58A4C14FD2A7AE111CCA5F1DAD@squarepi.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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/
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Midgley | 2008-11-25 16:07:59 | Re: Sequence and nextval problem |
Previous Message | Craig Ringer | 2008-11-25 04:44:46 | Re: Sequence and nextval problem |