From: Andreas Joseph Krogh <andreas@visena.com>
To: pgsql-performance@postgresql.org
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
Date: Mon, 21 Mar 2016 15:41:42 +0100 (CET)
Message-ID: <VisenaEmail.3.72ca8808c48140d5.153999d91d6@tc7-visena> (raw)
In-Reply-To: <CAMkU=1zAcFQAdysVY6fyyR0k5OTgWvoja2TpkGBhe5t0E9RopA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
På lørdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes <jeff.janes@gmail.com
<mailto:jeff.janes@gmail.com>>:
On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh <andreas@visena.com
<mailto:andreas@visena.com>> wrote: På onsdag 16. mars 2016 kl. 14:37:27, skrev
Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>:
Andreas Joseph Krogh <andreas@visena.com <mailto:andreas@visena.com>> 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 byextending 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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
<https://www.visena.com;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@visena.com
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
In-Reply-To: <VisenaEmail.3.72ca8808c48140d5.153999d91d6@tc7-visena>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox