Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1ai1YB-00040P-9f for pgsql-performance@arkaria.postgresql.org; Mon, 21 Mar 2016 15:15:23 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1ai1YA-0006KQ-58 for pgsql-performance@arkaria.postgresql.org; Mon, 21 Mar 2016 15:15:22 +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 1ai1WT-0003S5-C7 for pgsql-performance@postgresql.org; Mon, 21 Mar 2016 15:13:37 +0000 Received: from mail-lb0-x229.google.com ([2a00:1450:4010:c04::229]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1ai1WL-0007c1-Oi for pgsql-performance@postgresql.org; Mon, 21 Mar 2016 15:13:36 +0000 Received: by mail-lb0-x229.google.com with SMTP id k12so128207968lbb.1 for ; Mon, 21 Mar 2016 08:13:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:reply-to:in-reply-to:references:from:date:message-id :subject:to:cc; bh=UmkX8isR3Dp5GdyFtoULzH9NVwS9+AU5Y84YhpUfC5Y=; b=hULJrTjiI7rLfGibfr1wOKLifdNpTkl4OLX/9P5Iqj8cMgkQC9dXkTRayzGd1J4Z9t hbU6qxuoWqclI4l8sE4awLVZspeiRfLcbPZqQJMJz2CBSNJFzoM+wqoTAMPOQd3hIsQa jtawKkla2gtN/Z564W83ln0PJryr+tTrIk05kPiZYOqKVNeTjm0JyhDA1GeBA4rgmYur uIBayyQY9s3H/GAWcniYKuqK2+ZeqadwFIxAkAtZVSqrmAyQxqKHNmIxj9MiikiWpAeS VtW1gRlEWpTosMskBhhTRPRBLZxTcFToTvqZcPP9bD96Nl8oUnwl9AU394/M3+t0squ1 42Yg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:reply-to:in-reply-to:references :from:date:message-id:subject:to:cc; bh=UmkX8isR3Dp5GdyFtoULzH9NVwS9+AU5Y84YhpUfC5Y=; b=U0EniuF1d3foctOQl6Uv7q9dxFLJdOhjZxWTt5vZgKNl3AHwBJQN0cLgPDz9/sjsG7 SxdMPQpIMBl6EfKRWj+1PMU0hBCq7VUeJuM15yJJl9xIEhmZcRItmU0ptpI+nuO418jO gjmOPqoGfYwL2nnU562HzNgxp8juYp5xPAyWlsE35WiR575FEwbAja+79rBpY07cSNXq 6oW31GtIWr7YOoUInpFHhuRFqZUWh5rb+t6xKfqhYU0NjpSlx1nli3NJy3lk4R5xZFUK Q+mC5cz/f3LlpDEh+MmzDp1S7OAYFBu7dOP0oR5LIv/S7e02c0VuXztkbXBaznqiZR5Z xQPw== X-Gm-Message-State: AD7BkJKVIwTS9hw3hpYzAqPC6ewtaHHFxbq+ZucF42s4b/TOi9WScladiv3zv/oE1SDcHc6ztbMiOn5tTAAG7g== X-Received: by 10.112.30.163 with SMTP id t3mr11355730lbh.15.1458573207989; Mon, 21 Mar 2016 08:13:27 -0700 (PDT) MIME-Version: 1.0 Received: by 10.25.146.14 with HTTP; Mon, 21 Mar 2016 08:13:07 -0700 (PDT) Reply-To: obartunov@gmail.com In-Reply-To: References: From: Oleg Bartunov Date: Mon, 21 Mar 2016 18:13:07 +0300 Message-ID: Subject: Re: Searching GIN-index (FTS) and sort by timestamp-column To: Andreas Joseph Krogh , Teodor Sigaev Cc: pgsql-performance@postgresql.org Content-Type: multipart/related; boundary=001a11336424f1165f052e9089ba X-Pg-Spam-Score: -2.0 (--) 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 --001a11336424f1165f052e9089ba Content-Type: multipart/alternative; boundary=001a11336424f1165b052e9089b9 --001a11336424f1165b052e9089b9 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Mon, Mar 21, 2016 at 5:41 PM, Andreas Joseph Krogh wrote: > P=C3=A5 l=C3=B8rdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes < > jeff.janes@gmail.com>: > > 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 >> 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 existin= g > PostgreSQL full-text search engine by extending 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, an= d > improving information security."* > This is different feature ! Actually, we already have prototype of what Jeff suggested, we called it bitmap filtering, but failed to find use case where it provides benefits. Teodor will comment this idea more detail. > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andreas@visena.com > www.visena.com > > > --001a11336424f1165b052e9089b9 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable


On Mon, Mar 21, 2016 at 5:41 PM, Andreas Joseph Krogh <andreas@visena= .com> wrote:
P=C3=A5 l=C3=B8rdag 19. mars 2016 kl. 03:44:55, skrev Jeff Janes &l= t;jeff.janes@gmai= l.com>:
On Wed, 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&= gt;:
An= dreas Joseph Krogh <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 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?
=C2=A0
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 e= quality or range expression), and takes a bitmap (as produced by the FTS/GI= N) to apply as a filter.=C2=A0 But, I don't know of anyone planning on = doing that.
=C2=A0
Isn't this what Postgres Pro are planning? http://postgrespro.co= m/roadmap/mssearch
=C2=A0
"Unlike external special-purpose search e= ngines, a full-text search engine built in a DBMS is capable of combining f= ull-text and attributive search criteria in SQL query syntax. It is planned= to improve the existing PostgreSQL full-text search engine by extending= the functionality of Generalized Inverted Index (GIN) to make it capable o= f storing extra information required for ranging query results. This se= arch acceleration will allow to go back from external full-text search engi= nes, thus facilitating system administration and use, reducing technology r= isks, and improving information security."

This is different feature ! Actually, we already have= prototype of what Jeff suggested, we called it bitmap filtering, but faile= d to find use case where it provides benefits. Teodor will comment this ide= a more detail.
=C2=A0
=C2=A0
--
Andreas Jose= ph Krogh
CTO / Partner - Visena AS
Mobile: +47 909= 56 963
3D""
=C2=A0

--001a11336424f1165b052e9089b9-- --001a11336424f1165f052e9089ba Content-Type: image/png Content-Disposition: inline Content-Transfer-Encoding: base64 Content-ID: X-Attachment-Id: 5eeb7f6b3b74dc35_0.1 iVBORw0KGgoAAAANSUhEUgAAAIUAAAAYCAYAAADUIj6hAAAABHNCSVQICAgIfAhkiAAABzBJREFU aEPtmNFxHDcMhmVP3i1VECpvnjzkVIHWFfhcgVcVRKrAUgWRK/C6Al8H3lTgy0PGbzFdQc4VJP/H ADs43q6kROeJNbOYgQACIAgCWJKng4MZ5gxUGXh0U0b+SIuF9G+EWXj2Q15vbrE/NPtk9uub7Gfd t5mBx1NhqSEo8HshjbEUtlO2yIM9tsx5dZP9rPt2MzDZFAqZE4LGcFjdsg3saQaHt7fY70X949On aS+OZidDBkavD33157L4xaw2os+Mp/DAC10l2XhOCeStj0W5ajrJL8WfCi80Xgf9vVlrhg9yRONe /f7xI2vNsIcM7JwU9o6oGyJrLT8JOA1aX9sKP4wl94bAniukEbo/n7YPmuTETzIab4Y9ZWCrKexd 8M58lxPCvvD6aihfvexbEQoPYO8NQRO0JnddGO6FJYaVsBde7cXj7KRk4LsqDxQzCYeGsJNgaXZe +JXkyGgWINq3Gp+bHNILz+wEYs5qH1eJrouNrpDXtk4O6x1IvtD40GWyJYYdkF0jIbYZlN16xygI gl9smbMDsmFdfAKb23xGB8H/mv3tOJfArs1kukk79DEPdQ5u0g1vCvvqKXIW8mZYW+F3Tg4r8HvZ kQCCLydK8EFMQCe5N8RgL9mRG0SqQFuNvdE6beSs0qPDBuCdg0+gvClso8SbTO4ki7mQzQqBrcMH QPwReg2wW8umEe/+L8T/LEzBGF9nXjzZ46s+ITHvhegW8LL39xm6App7KYL/GE+vcYnFbJiP/0aY hdiCnRC7jaj7eiK2ETInC5PRF6LYkSN0+HabF75WuT6syCyI0YkVGGMvUC33AmfZTDUEj8u6IVhu EhRUJ2U2g6UlugyNb03Hl9obHwlxJbcRzcYjO4QPjVfGFTQav4vrmp7cpMp2qbHnBxWJbisbho2Q XI6C1sIHDUFhH4Hij4UbIev66eANeiwbkA+LBsO36zAHzoXU7AhbqLAXEiOYTXcSdO8VS9L4wN8U BIYhBd6oSUgYMijOkecRuTdQa/YiBXhbXFcnCnI2SrfeBG9NydrLYMhGHa5qB9pQIxlzAE6Okjzx JO5afGe6kmhBicWKQNKuTZ5EW+MjQY8v/9rQ0bhJSJyNGUe/JH1t8h1iMbdSPAvxHYin6YmN9YBX QmTYZZNh14vHhhhal5vtcIrJjmvszPRJdExH3MWHvykoBEc9CoCGWJisOLOGoCORs1FvIMbYA8z3 kwM59oemy6LlWrLxFLmWgiQAfEGd8S+NssbK+EhyGLxUkhhiy717waBqHOJYSEacwBejkFNhjJNj v/gANOdQxPfciP/JVBCK2cOIcg3RRJ+CPrLPNVhhN6F38VKMF3XLlIJrjU5C8gMFxvKDvOcPc4rV NjCn7KM0BV+161V8viSCuJZ8SITGJGEh7IUUlxOFMYUH1kJOCN4Wh+I5pqCuK01k40kSNtnKiKIl UfxAgW5sU5JlS05rtt5YFJF1SWoqHv6BRgQcA4/bdb9WRjmMk3jyUMAbIoyJq9e4cVmgzKt9j5gN J/aYDhk+hhjExwaPcz5PObA5Zd9bvz5UzCTZuZDidu5AchpiKSwPR+TV1bCWqBQ9nCjJ5neivC82 Nr4LeS2j1gw5LUqwBuhGQQU5UwHeSslXkwIynz2U2A2yKDgG7OffwLA3TpGRpk0Tzpj3ZEJXi/GR a6GN0e0NtppChePdcAz1FTS+FN8Kpxoiykk+J8fC5tenjbu9kSqpHLu9jBrhUuhNwVGbpyZrzrl0 HPVD8SXj5EOOD/eDC64VjvYBZLuUbIVAfBN1t/C/SU+cAGtdGo+fVnzycUX5wmn6eCKPmWYJG2E/ ppTsuRCbvUD9fwquksG5GqLVKhzDQ3GrEyLKSXhsiK3T5j9EyxffCFOYi2wUlPyFFDQAhehESDjQ GoX0ho0oj8RPou6TxHJdcT0NTcWkO0AnG7+uXsnHqcas/72wvWF+mSf7N/Watp9kTXoluzeS0fB9 9CcZ/hvhSZTfh99pispZOXL9KrGrARkNUMu9ITbScZWs7xOYNt9pwxSZtYDsX/GE3xTkrXgwAsXm fud08FiTeC+m2/o7ppo+PTS/NBK5ARpDG5YHr++DpmVfreYdiX8mnp+DzPEG9WbqJON0JBenZofs sxBA1gj5NXGvfJu/Qh7HwQh/VDUEyUxCit5hH94QfKkEdu+GwK8BX0hvCF+D67xhjmXQCXMwJCZ+ olK0A1EKRCE4snsh42w8Mv/Zhxw9iD7Cjo7CyQC/KyF6oBfShL4PYgG+CHsYKyZxvxZSZBAgjhIz YDz+AbfD37GtbaoSKzgGt+lKfI/GZtay6vG4VXTpPsh+IcQhOk9I7WYeP5AM3HZ9+DaSGIp9HIuu huC4pCGGx+YD2fcc5tfIAA0h/Et4/jX8zz4fWAasIf4UbR9Y6HO4d8jAnd4U0Y8ageuCB+c+H5R3 CHU2mTMwZ+B/y8DfSMBLLOYXVuEAAAAASUVORK5CYII= --001a11336424f1165f052e9089ba--