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


> On 16 Mar 2016, at 18:04, Evgeniy Shishkin <itparanoia@gmail.com> wrote:
>=20
>>=20
>> 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 con=
dition.
>> In fact i tried to reproduce the example,
>> and if i change folder_id to int from bigint, then index condition with =
folder_id is used
>>=20
>>        Index Cond: ((fts_all @@ '''hi'''::tsquery) AND (folder_id =3D 1))
>>=20
>=20
> Looks like documentation http://www.postgresql.org/docs/9.5/static/btree-=
gin.html
> is lying about supporting int8 type
>=20

Uh, it works if i cast to bigint explicitly
      WHERE  del.fts_all @@ to_tsquery('simple', 'hi')
      AND del.folder_id =3D 1::bigint;
results in=20
         Index Cond: ((folder_id =3D '1'::bigint) AND (fts_all @@ '''hi''':=
:tsquery))

>>=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 sorti=
ng?
>>>=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.o=
rg)
>>> 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