From: Jordi <jmaillists@promani.be>
To: pgsql-performance@postgresql.org
Subject: Bitmap and-ing between btree and gin?
Date: Thu, 4 Feb 2016 12:15:02 +0100
Message-ID: <56B332B6.1040109@promani.be> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jmaillists@promani.be
Subject: Re: Bitmap and-ing between btree and gin?
In-Reply-To: <56B332B6.1040109@promani.be>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox