Re: Searching GIN-index (FTS) and sort by timestamp-column

От: Andreas Joseph Krogh
Тема: Re: Searching GIN-index (FTS) and sort by timestamp-column
Дата: ,
Msg-id: VisenaEmail.3.72ca8808c48140d5.153999d91d6@tc7-visena
(см: обсуждение, исходный текст)
Ответ на: Re: Searching GIN-index (FTS) and sort by timestamp-column  (Jeff Janes)
Ответы: Re: Searching GIN-index (FTS) and sort by timestamp-column  (Oleg Bartunov)
Список: pgsql-performance

Скрыть дерево обсуждения

Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
 Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
  Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Jeff Janes, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Oleg Bartunov, )
      Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Oleg Bartunov, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Jeff Janes, )
  Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
   Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Evgeniy Shishkin, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
     Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
      Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )
       Re: Searching GIN-index (FTS) and sort by timestamp-column  (Tom Lane, )
    Re: Searching GIN-index (FTS) and sort by timestamp-column  (Andreas Joseph Krogh, )

På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes <>:
On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh <> wrote:
På onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane <>:
Andreas Joseph Krogh <> writes:
> 1. Why isnt' folder_id part of the index-cond?

Because a GIN index is useless for sorting.

> 2. Is there a way to make it use the (same) index to sort by
> received_timestamp?

No.

> 3. Using a GIN-index, is there a way to use the index at all for sorting?

No.

> 4. It doesn't seem like ts_rank uses the index for sorting either.

Same reason.

regards, tom lane
 
So it's basically impossible to use FTS/GIN with sorting on large datasets?
Are there any plans to improve this situation?
 
I don't see why it would not be possible to create a new execution node type that does an index scan to obtain order (or just to satisfy an equality or range expression), and takes a bitmap (as produced by the FTS/GIN) to apply as a filter.  But, I don't know of anyone planning on doing that.
 
Isn't this what Postgres Pro are planning? http://postgrespro.com/roadmap/mssearch
 
"Unlike external special-purpose search engines, a full-text search engine built in a DBMS is capable of combining full-text and attributive search criteria in SQL query syntax. It is planned to improve the existing PostgreSQL full-text search engine by extending the functionality of Generalized Inverted Index (GIN) to make it capable of storing extra information required for ranging query results. This search acceleration will allow to go back from external full-text search engines, thus facilitating system administration and use, reducing technology risks, and improving information security."
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
 
Вложения

В списке pgsql-performance по дате сообщения:

От: Andreas Joseph Krogh
Дата:
Сообщение: Re: Searching GIN-index (FTS) and sort by timestamp-column
От: Dave Stibrany
Дата:
Сообщение: Re: Disk Benchmarking Question