Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ah6t2-0006nQ-6Y for pgsql-performance@arkaria.postgresql.org; Sat, 19 Mar 2016 02:45:08 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ah6t1-000339-Oc for pgsql-performance@arkaria.postgresql.org; Sat, 19 Mar 2016 02:45:07 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1ah6sz-00030G-Ly for pgsql-performance@postgresql.org; Sat, 19 Mar 2016 02:45:05 +0000 Received: from mail-lf0-x229.google.com ([2a00:1450:4010:c07::229]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ah6sr-0001GC-Tk for pgsql-performance@postgresql.org; Sat, 19 Mar 2016 02:45:04 +0000 Received: by mail-lf0-x229.google.com with SMTP id d82so30644471lfe.3 for ; Fri, 18 Mar 2016 19:44:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=2uB4FskBpLnoS1+a3WcvulH4OWuGM4yFt027FN6YZYc=; b=lh3KAtUhfZPsT7KaiB5m6WqfkVnlB5veY4uobFXXcjPCGk0KFegk9y65Y8wA3cIDVE vQtQfouAo6ZRyIkQh89wrEJ5Ajp5D9CBFAn54LUSX0jOuS++kWf4GZmkkxwmrIZxE+aM LaHXsTl4OB6mhCvj30dsPBWpQLMzLjUIxt+zhOBXSfVI9f75ifnrAlR297UxoE8JEKUD UAyXJisJ8baVIWfk7eF6zifer5xnx/tNthTrkGmG6uIOpN1V8kah6YLJh+NHSTtELUVV GKIzfV30D3K+o0k6ErwjRpenxEgymo14H97+4OCkY2VbW6KzfBekab6RFeqgdSS3OIQ0 psZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=2uB4FskBpLnoS1+a3WcvulH4OWuGM4yFt027FN6YZYc=; b=J1ll1TSu1wuLbqjvzLj7lFGfE+XdBwJopUNduWuXUcjkzZQtuDyjspOgBHd/7BhhcG XsKaujb08PkzZzZ08WseIV21BBeDTGD+s4BSrEzt2I6/DziS6Oo/HnxRa5MyM/TvmNbX 4IrASpRuL1CgMDzn3Q5JiWYiSX35UoUfOgrYEhIvFHiaI6+D9jJPkgz/YbafMHqwqv3Z UqmYljJe5fyNbryiI75VkI1GWFjuZYbPEsYaYGuMjILfmOnD8Pa5nSwx4pf+6h9VznXM xHHPo7B2FGjoJbgpflMDBySkp4BW6Z7af0aeIkwOjy9CW5XxD2IJv6kbtNclaWBEuDvv CsCA== X-Gm-Message-State: AD7BkJIOI9sLZdf3jCu2u2f7ZPkbyFC0SrlpAprua7vDxv4DviUxIhEmuebBjNZdPJXLZGUYTU4L7JRwWtLipw== MIME-Version: 1.0 X-Received: by 10.25.152.205 with SMTP id a196mr7516770lfe.85.1458355495774; Fri, 18 Mar 2016 19:44:55 -0700 (PDT) Received: by 10.25.145.14 with HTTP; Fri, 18 Mar 2016 19:44:55 -0700 (PDT) In-Reply-To: References: <27201.1458135447@sss.pgh.pa.us> Date: Fri, 18 Mar 2016 19:44:55 -0700 Message-ID: Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column From: Jeff Janes To: Andreas Joseph Krogh Cc: "pgsql-performance@postgresql.org" Content-Type: multipart/alternative; boundary=001a11401c96482f9f052e5dd960 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11401c96482f9f052e5dd960 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Wed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh wrote: > P=C3=A5 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 sortin= g? > > 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 dataset= s? > 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. Cheers, Jeff --001a11401c96482f9f052e5dd960 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
On W= ed, Mar 16, 2016 at 6:53 AM, Andreas Joseph Krogh <andreas@visena.com= > wrote:
= P=C3=A5 onsdag 16. mars 2016 kl. 14:37:27, skrev Tom Lane <tgl@sss.pgh.pa.us>:
Andreas = Joseph Krogh <an= dreas@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 sorti= ng?

No.

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

Same reason.

regards, tom lane
=C2=A0
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 sa= tisfy an equality or range expression), and takes a bitmap (as produced by = the FTS/GIN) to apply as a filter.=C2=A0 But, I don't know of anyone pl= anning on doing that.

Cheers,

Jeff
<= /div>
--001a11401c96482f9f052e5dd960--