GiST index not used for ORDER BY?

Поиск
Список
Период
Сортировка
От Max
Тема GiST index not used for ORDER BY?
Дата
Msg-id 6.2.1.2.0.20050127135937.02fa7448@209.81.157.235
обсуждение исходный текст
Ответы Re: GiST index not used for ORDER BY?
Список pgsql-general
Hi,

I'm setting up a simple search engine using Tsearch2.
The basic idea is: a user enters a search query and a maximum of 1000
results is returned, sorted by date, newest first.

At the moment the table holding the searchable data has 1.1 million entries.
It works great when the search only produces a few hundred results. However
when people search on a common word with 10.000+ results, there's a
performance problem.

The database design looks like this:

CREATE TABLE posts_index
(
....
         startdate INT NOT NULL,
         idxFTI   tsvector,
....
);

Where startdate is a unix timestamp, and idxFTI is a tsvector with the data
to be searched.

Since only 1000 results need to be returned sorted by date (newest first),
I hoped to solve the problem by installing the btree_gist extension and
adding the following index:

CREATE INDEX idxFTI_idx2 ON posts_index USING gist(idxFTI,(-startdate));

However the -startdate portion of the index doesn't seem to be used:

------
EXPLAIN SELECT startdate, headline(subject,q) AS subject FROM posts_index
i, to_tsquery('default', '_SEARCH_TERM_') AS q WHERE idxfti @@ q ORDER BY
(-i.startdate) LIMIT 1000;

QUERY PLAN

Limit  (cost=5152014.10..5152016.60 rows=1000 width=126)
   ->  Sort  (cost=5152014.10..5155079.61 rows=1226201 width=126)
         Sort Key: (- i.startdate)
         ->  Nested Loop  (cost=0.00..4912754.84 rows=1226201 width=126)
               ->  Function Scan on q  (cost=0.00..12.50 rows=1000 width=32)
               ->  Index Scan using idxfti_idx2 on posts_index
i  (cost=0.00..4891.27 rows=1227 width=253)
                     Index Cond: (i.idxfti @@ "outer".q)
----

Any suggestions?

Regards,

Max


В списке pgsql-general по дате отправления:

Предыдущее
От: "Florian G. Pflug"
Дата:
Сообщение: Re: visualizing B-tree index coverage
Следующее
От: Stephan Szabo
Дата:
Сообщение: Re: [SQL] Foreign Key relationship between two databases