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--