From jmaillists@promani.be Thu Feb  4 11:16:23 2016
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M53871=pgsql-performance-ar@postgresql.org>)
 id 1aRHtf-0002tO-DS
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 11:16:23 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-performance-owner+M53871=pgsql-performance-ar@postgresql.org>)
 id 1aRHte-0006j3-P2
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 11:16: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) (envelope-from <jmaillists@promani.be>)
 id 1aRHtd-0006iL-QX
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 11:16:21 +0000
Received: from mail.promani.be ([178.22.69.78])
 by makus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <jmaillists@promani.be>) id 1aRHta-0007yz-Sd
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 11:16:20 +0000
To: pgsql-performance@postgresql.org
From: Jordi <jmaillists@promani.be>
Subject: Bitmap and-ing between btree and gin?
Message-ID: <56B332B6.1040109@promani.be>
Date: Thu, 4 Feb 2016 12:15:02 +0100
MIME-Version: 1.0
Content-Type: multipart/alternative;
 boundary="------------040709080301030101030706"
X-Pg-Spam-Score: -2.4 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------040709080301030101030706
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello all,


I've been trying to get a query use indexes and it has raised a doubt 
whether pgsql supports bitmap and-ing between a multi-column btree index 
and a gin index.

The idea is to do a full-text search on a tsvector that is indexed with 
gin. Then there are typical extra filters like is_active that you would 
put in a btree index. Instead of using OFFSET I use a > operation on the 
id. Finally, to make sure the results listing always appear in the same 
order, I do an ORDER BY the id of the row. So something like this:

CREATE INDEX idx_gin_page ON page USING gin(search_vector);

CREATE INDEX idx_btree_active_iddesc ON page USING btree(is_active, id DESC);

SELECT * FROM page WHERE (( (page.search_vector) @@ (plainto_tsquery('pg_catalog.english', 'myquery'))) AND page.is_active = 1 AND page.id > 100) ORDER BY page.id DESC LIMIT 100;


Some options I considered:
- One big multi-column index with the btree_gin module, but that didn't 
work. I suppose it's because just like gin, it doesn't support sorting.
- Seperate indexes as above, but that didn't work. The planner would 
always choose the btree index to do the is_active=1 and id>100 filter 
and the sorting, and within those results do a manual filter on the 
tsvector, being extremely slow.

BUT: when I remove the ORDER BY statement, the query runs really fast. 
It uses the 2 indexes seperately and bitmap-ands them together, 
resulting in a fast executing query.

So my question is whether there is something wrong with my query or 
indexes, or does pgsql not support sorting and bitmap and-ing?


Thanks and have a nice day
Jordi


--------------040709080301030101030706
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=utf-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    Hello all,<br>
    <br>
    <br>
    I've been trying to get a query use indexes and it has raised a
    doubt whether pgsql supports bitmap and-ing between a multi-column
    btree index and a gin index.<br>
    <br>
    The idea is to do a full-text search on a tsvector that is indexed
    with gin. Then there are typical extra filters like is_active that
    you would put in a btree index. Instead of using OFFSET I use a &gt;
    operation on the id. Finally, to make sure the results listing
    always appear in the same order, I do an ORDER BY the id of the row.
    So something like this:<br>
    <br>
    <meta http-equiv="content-type" content="text/html; charset=utf-8">
    <pre>CREATE INDEX idx_gin_page ON page USING gin(search_vector);</pre>
    <pre>CREATE INDEX idx_btree_active_iddesc ON page USING btree(is_active, id DESC);</pre>
    <pre>
SELECT * FROM page WHERE (( (page.search_vector) @@ (plainto_tsquery('pg_catalog.english', 'myquery'))) AND page.is_active = 1 AND page.id &gt; 100) ORDER BY page.id DESC LIMIT 100;</pre>
    <br>
    Some options I considered:<br>
    - One big multi-column index with the btree_gin module, but that
    didn't work. I suppose it's because just like gin, it doesn't
    support sorting.<br>
    - Seperate indexes as above, but that didn't work. The planner would
    always choose the btree index to do the is_active=1 and id&gt;100
    filter and the sorting, and within those results do a manual filter
    on the tsvector, being extremely slow.<br>
    <br>
    BUT: when I remove the ORDER BY statement, the query runs really
    fast. It uses the 2 indexes seperately and bitmap-ands them
    together, resulting in a fast executing query.<br>
    <br>
    So my question is whether there is something wrong with my query or
    indexes, or does pgsql not support sorting and bitmap and-ing?<br>
    <br>
    <br>
    Thanks and have a nice day<br>
    Jordi<br>
    <br>
  </body>
</html>

--------------040709080301030101030706--




From tgl@sss.pgh.pa.us Thu Feb  4 16:08:48 2016
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M53872=pgsql-performance-ar@postgresql.org>)
 id 1aRMSe-0008FU-93
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 16:08:48 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-performance-owner+M53872=pgsql-performance-ar@postgresql.org>)
 id 1aRMSc-0006ca-Ip
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 16:08:46 +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) (envelope-from <tgl@sss.pgh.pa.us>) id 1aRMSb-0006cG-PO
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 16:08:45 +0000
Received: from sss.pgh.pa.us ([66.207.139.130])
 by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <tgl@sss.pgh.pa.us>) id 1aRMSZ-000628-6c
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 16:08:44 +0000
Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1])
 by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id u14G8fUL017856;
 Thu, 4 Feb 2016 11:08:42 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jordi <jmaillists@promani.be>
cc: pgsql-performance@postgresql.org
Subject: Re: Bitmap and-ing between btree and gin?
In-reply-to: <56B332B6.1040109@promani.be>
References: <56B332B6.1040109@promani.be>
Comments: In-reply-to Jordi <jmaillists@promani.be>
 message dated "Thu, 04 Feb 2016 12:15:02 +0100"
Date: Thu, 04 Feb 2016 11:08:41 -0500
Message-ID: <17855.1454602121@sss.pgh.pa.us>
X-Pg-Spam-Score: -2.4 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Jordi <jmaillists@promani.be> writes:
> I've been trying to get a query use indexes and it has raised a doubt 
> whether pgsql supports bitmap and-ing between a multi-column btree index 
> and a gin index.

Sure.  But such a plan would give an unordered result, meaning that we'd
have to process the whole table before doing the ORDER BY/LIMIT.  The
planner evidently thinks that it's better to try to process the rows in
ID order so it can stop as soon as it's got 100.  If it's wrong about
that, that's likely because it's got a bad estimate of the selectivity of
the other WHERE conditions.  You might see if you can improve the
statistics for the search_vector column.

			regards, tom lane


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



From jmaillists@promani.be Thu Feb  4 17:20:52 2016
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M53873=pgsql-performance-ar@postgresql.org>)
 id 1aRNaO-0003Dg-8Q
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 17:20:52 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-performance-owner+M53873=pgsql-performance-ar@postgresql.org>)
 id 1aRNaN-0007D9-RU
 for pgsql-performance@arkaria.postgresql.org; Thu, 04 Feb 2016 17:20:51 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
 (Exim 4.84) (envelope-from <jmaillists@promani.be>)
 id 1aRNaN-0007CW-2f
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 17:20:51 +0000
Received: from mail.promani.be ([178.22.69.78])
 by magus.postgresql.org with esmtp (Exim 4.84)
 (envelope-from <jmaillists@promani.be>) id 1aRNaK-0008GR-HN
 for pgsql-performance@postgresql.org; Thu, 04 Feb 2016 17:20:50 +0000
Subject: Re: Bitmap and-ing between btree and gin?
To: pgsql-performance@postgresql.org
References: <56B332B6.1040109@promani.be> <17855.1454602121@sss.pgh.pa.us>
Cc: tgl@sss.pgh.pa.us
From: Jordi <jmaillists@promani.be>
Message-ID: <56B38823.40809@promani.be>
Date: Thu, 4 Feb 2016 18:19:31 +0100
MIME-Version: 1.0
In-Reply-To: <17855.1454602121@sss.pgh.pa.us>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -2.4 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi Tom, thanks for your reply, much appreciated.

So basically you're saying it's hard to do sorting in any way when a gin 
index is involved? Neither with a complete multi-column btree_gin index 
because it doesn't support sorting per definition, nor with a seperate 
gin and btree because there would be an extra post-sorting step involved 
over the FULL resultset (because of the LIMIT).

Then would you have any hint on how to implement pagination when doing 
full text search?
Cause in theory, if I gave it a id>100 LIMIT 100, it might just as well 
return me results 150 to 250, instead of 100 to 200...

PS: I already tried maxing the statistics target setting and running 
ANALYSE after, with no change.

Regards,
Jordi


On 04-02-16 17:08, Tom Lane wrote:
> Jordi <jmaillists@promani.be> writes:
>> I've been trying to get a query use indexes and it has raised a doubt
>> whether pgsql supports bitmap and-ing between a multi-column btree index
>> and a gin index.
> Sure.  But such a plan would give an unordered result, meaning that we'd
> have to process the whole table before doing the ORDER BY/LIMIT.  The
> planner evidently thinks that it's better to try to process the rows in
> ID order so it can stop as soon as it's got 100.  If it's wrong about
> that, that's likely because it's got a bad estimate of the selectivity of
> the other WHERE conditions.  You might see if you can improve the
> statistics for the search_vector column.
>
> 			regards, tom lane
>
>



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



From jeff.janes@gmail.com Mon Feb  8 18:15:33 2016
Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M53889=pgsql-performance-ar@postgresql.org>)
 id 1aSqLV-0006SI-Ir
 for pgsql-performance@arkaria.postgresql.org; Mon, 08 Feb 2016 18:15:33 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.84) (envelope-from
 <pgsql-performance-owner+M53889=pgsql-performance-ar@postgresql.org>)
 id 1aSqLV-00038L-3D
 for pgsql-performance@arkaria.postgresql.org; Mon, 08 Feb 2016 18:15:33 +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) (envelope-from <jeff.janes@gmail.com>)
 id 1aSqJq-0001Eh-Pn
 for pgsql-performance@postgresql.org; Mon, 08 Feb 2016 18:13:50 +0000
Received: from mail-lb0-x235.google.com ([2a00:1450:4010:c04::235])
 by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
 (Exim 4.84) (envelope-from <jeff.janes@gmail.com>)
 id 1aSqJn-0001X8-VX
 for pgsql-performance@postgresql.org; Mon, 08 Feb 2016 18:13:49 +0000
Received: by mail-lb0-x235.google.com with SMTP id cw1so87538599lbb.1
 for <pgsql-performance@postgresql.org>; Mon, 08 Feb 2016 10:13:47 -0800 (PST)
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:content-type;
 bh=aSMGuqAqOQhshpnS1P8Wf1OE4k5BrhgjzxkX6wIyXAo=;
 b=w/EoAiX7hgY2eJwVItD7d/uXbeCrzOBu8SjbXrQljLwYKl2GZ/UtbC6fOIunjENdiK
 m1okgfQXIr19sKhFEiE/Ek+whMzjSv1eB7LG2G49g+epjMBEODDtlgX69AZnSQn2u013
 NiJK6oBdv+bhISPRA1r6/68+yLjqvXdVZhBKcq3AamNSTrj39MaiUexHa1TbusdMbIQF
 KC8f5NVbxaYZg5i8Yw6LkA/3mGBJD/WeQ5FCvaNTww8+Aq1UB4qUOzXx3VVF7hx5IH6Z
 obgTzsET95aQv+P0H1993Z9b7s7E5ZL4KpyyTpn0xV8v7GFFempYeqCOV0YfOnKfnjp4
 /JTQ==
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:content-type;
 bh=aSMGuqAqOQhshpnS1P8Wf1OE4k5BrhgjzxkX6wIyXAo=;
 b=XLiN+zgcfU/pDFbzPlFW+M+4qYoOdJ0hwIj3Uh3776WB5gWO+Pr/CpB53bKyAv5VDQ
 ZUrAEgXD1sGmEg7QDa2aQ8Zjzc6a1dU12rbqZtH1+hYxXgE7IAKvWpKlG2GpN2CCDPS9
 Lvlk1UbG5uN0USVcxrcftmO4MHSziFSDl53Itvs0EJAYk6BKpdjYiON9Q3N5pv5e/9iC
 /3M7euVlIV8wa3e/8xF2DiYmadl2TQF6x+dWwFMe71gx7xuBbEp7edivn63o7l3MAY26
 N7pdsLSB1EFXCipOrb+gCxW+dkOSqeSBzX7FkqgLnYsGDONQni0pzNeuSqiQ0f52bXRT
 /mjg==
X-Gm-Message-State: 
 AG10YOQ9DUqDrKAVOonOV+I50bc1vrZSXM03Ax8VSmQKfBYOwvxIXAke+VQDitKLqo4gfNsTCvzg7z7auHDUyg==
MIME-Version: 1.0
X-Received: by 10.112.63.161 with SMTP id h1mr11765537lbs.61.1454955226451;
 Mon, 08 Feb 2016 10:13:46 -0800 (PST)
Received: by 10.25.139.8 with HTTP; Mon, 8 Feb 2016 10:13:46 -0800 (PST)
In-Reply-To: <56B38823.40809@promani.be>
References: <56B332B6.1040109@promani.be> <17855.1454602121@sss.pgh.pa.us>
 <56B38823.40809@promani.be>
Date: Mon, 8 Feb 2016 10:13:46 -0800
Message-ID: 
 <CAMkU=1wvo6OQyskSzDG8K1Y3AVe_mUhy-QgK7CgbEZLSHSSibQ@mail.gmail.com>
Subject: Re: Bitmap and-ing between btree and gin?
From: Jeff Janes <jeff.janes@gmail.com>
To: Jordi <jmaillists@promani.be>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>,
 Tom Lane <tgl@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8
X-Pg-Spam-Score: -2.7 (--)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thu, Feb 4, 2016 at 9:19 AM, Jordi <jmaillists@promani.be> wrote:

The custom here is to respond in line, not to top-post.  Thanks.

>
> So basically you're saying it's hard to do sorting in any way when a gin
> index is involved? Neither with a complete multi-column btree_gin index
> because it doesn't support sorting per definition, nor with a seperate gin
> and btree because there would be an extra post-sorting step involved over
> the FULL resultset (because of the LIMIT).

In principle there is no reason (that I can think of) that a normal
btree index range scan couldn't accept a bitmap as an optional input,
and then use that as a filter which would allow it to walk the index
in order while throwing out tuples that can't match the other
conditions.  You are not the first person who would benefit from such
a feature.  But it would certainly not be trivial to implement.  It is
not on anyone's to-do list as far as I know.