Bitmap and-ing between btree and gin?

From: Jordi
Subject: Bitmap and-ing between btree and gin?
Date: ,
Msg-id: 56B332B6.1040109@promani.be
(view: Whole thread, Raw)
Responses: Re: Bitmap and-ing between btree and gin?  (Tom Lane)
List: pgsql-performance

Tree view

Bitmap and-ing between btree and gin?  (Jordi, )
 Re: Bitmap and-ing between btree and gin?  (Tom Lane, )
  Re: Bitmap and-ing between btree and gin?  (Jordi, )
   Re: Bitmap and-ing between btree and gin?  (Jeff Janes, )

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


pgsql-performance by date:

From: Tom Lane
Date:
Subject: Re: Bitmap and-ing between btree and gin?
From: Jordi
Date:
Subject: Re: Bitmap and-ing between btree and gin?