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: <D77F27E4-3825-454F-B2CC-F91064E2FAF7@gmail.com>
Date: Wed, 16 Mar 2016 18:04:08 +0300
Cc: Andreas Joseph Krogh <andreas@visena.com>,
 pgsql-performance <pgsql-performance@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <9D98454D-D4E5-457F-8F5C-F49526BFA9FA@gmail.com>
References: <VisenaEmail.42.91df4628bdf7755c.1537e96e852@tc7-visena>
 <27201.1458135447@sss.pgh.pa.us>
 <D77F27E4-3825-454F-B2CC-F91064E2FAF7@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org


> On 16 Mar 2016, at 17:52, Evgeniy Shishkin <itparanoia@gmail.com> wrote:
>=20
>=20
>> 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.
>=20
> I don't see how gin inability to return sorted data relates to index cond=
ition.
> In fact i tried to reproduce the example,
> and if i change folder_id to int from bigint, then index condition with f=
older_id is used
>=20
>         Index Cond: ((fts_all @@ '''hi'''::tsquery) AND (folder_id =3D 1))
>=20

Looks like documentation http://www.postgresql.org/docs/9.5/static/btree-gi=
n.html
is lying about supporting int8 type

>=20
>>=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 sortin=
g?
>>=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.or=
g)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>=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