Thanks for the reply and apologies, it was my first post.
I'm running on PG 8.3.3 (ubuntu), i haven't tried gin as that doesn't
support multi-column. I haven't used 8.4 as it is still in development?
a sample query is as follows
select node_id from breadcrumbs where textsearchable @@ to_tsquery('book')
order by views desc limit 100;
explain analyze results in this:
Limit (cost=10300.58..10300.83 rows=100 width=381) (actual
time=69887.851..69887.880 rows=100 loops=1)
-> Sort (cost=10300.58..10307.61 rows=2812 width=381) (actual
time=69887.849..69887.862 rows=100 loops=1)
Sort Key: views
Sort Method: top-N heapsort Memory: 84kB
-> Bitmap Heap Scan on breadcrumbs (cost=171.49..10193.10
rows=2812 width=381) (actual time=60311.197..69574.742 rows=569519 loops=1)
Filter: (textsearchable@@ to_tsquery('book'::text))"
-> Bitmap Index Scan on idx_breadcr (cost=0.00..170.79
rows=2812 width=0) (actual time=60261.959..60261.959 rows=569519 loops=1)
Index Cond: (textsearchable @@ to_tsquery('book'::text))
Total runtime: 69896.896 ms
As you can see it sorts the full result set from the search. Ideally i'd
like to use an index on the views.
How stable is 8.4? Is it worth trying that or is the multi-column gin likely
to be back-ported?
Thanks
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: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
