Content-Type: text/plain; charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 9.2 \(3112\))
Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column
From: Evgeniy Shishkin <itparanoia@gmail.com>
In-Reply-To: <27201.1458135447@sss.pgh.pa.us>
Date: Wed, 16 Mar 2016 17:52:40 +0300
Cc: Andreas Joseph Krogh <andreas@visena.com>,
 pgsql-performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <D77F27E4-3825-454F-B2CC-F91064E2FAF7@gmail.com>
References: <VisenaEmail.42.91df4628bdf7755c.1537e96e852@tc7-visena>
 <27201.1458135447@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> On 16 Mar 2016, at 16:37, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> Andreas Joseph Krogh <andreas@visena.com> writes:
>> 1. Why isnt' folder_id part of the index-cond?
>=20
> Because a GIN index is useless for sorting.

I don't see how gin inability to return sorted data relates to index condit=
ion.
In fact i tried to reproduce the example,
and if i change folder_id to int from bigint, then index condition with fol=
der_id is used

         Index Cond: ((fts_all @@ '''hi'''::tsquery) AND (folder_id =3D 1))


>=20
>> 2. Is there a way to make it use the (same) index to sort by=20
>> received_timestamp?
>=20
> No.
>=20
>> 3. Using a GIN-index, is there a way to use the index at all for sorting?
>=20
> No.
>=20
>> 4. It doesn't seem like ts_rank uses the index for sorting either.
>=20
> Same reason.
>=20
> 			regards, tom lane
>=20
>=20
> --=20
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance