Multicolumn index including tsvector. - Mailing list pgsql-general

From Kris Gale
Subject Multicolumn index including tsvector.
Date
Msg-id ed47a9150911231508i1fa6d163qa7748893dc04c5e7@mail.gmail.com
Whole thread Raw
Responses Re: Multicolumn index including tsvector.
Re: Multicolumn index including tsvector.
List pgsql-general
Hi everyone,

In the documentation and past messages to this list, it sounds like the btree_gist and btree_gin modules included in contrib/ in 8.4 should give me the functionality I'm looking for, but I don't seem to be getting the behavior I want.

I've made an example table representing something like a simplified version of a web discussion board.

     Table "public.example"
 Column  |   Type   | Modifiers
---------+----------+-----------
 body    | text     |
 vectors | tsvector |
 user_id | bigint   |

I've got btree_gin and btree_gist installed, so I can make a composite index on vectors and user_id (which is a bigint).

create index index_examples_gin on example using gist (user_id,vectors);
create index index_examples_gist on example using gist (user_id,vectors);

So what I'm expecting here is that it'll be able to use one of those composite indexes to satisfy both the user_id and the vectors constraints.  That doesn't seem to be the case, based on this query plan:

explain analyze select body from example where user_id=1 and vectors @@ to_tsquery('simple', 'when') limit 10;
                                                                   QUERY PLAN                                                                   
-------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1786.87..10510.47 rows=10 width=5) (actual time=203.155..244.987 rows=10 loops=1)
   ->  Bitmap Heap Scan on example  (cost=1786.87..38425.99 rows=42 width=5) (actual time=203.153..244.980 rows=10 loops=1)
         Recheck Cond: (vectors @@ '''when'''::tsquery)
         Filter: (user_id = 1)
         ->  Bitmap Index Scan on index_examples_gist  (cost=0.00..1786.86 rows=26535 width=0) (actual time=186.711..186.711 rows=27477 loops=1)
               Index Cond: (vectors @@ '''when'''::tsquery)
 Total runtime: 245.062 ms
(7 rows)

So it seems to be using the index only to satisfy the tsquery part of the where clause, and then applying the user_id filter to the rows it fetches.  Ideally, I'd want to see it using both columns as part of the index condition, and not using a filter at all.  Is what I'm trying to achieve possible?

Thanks in advance.

Kris

pgsql-general by date:

Previous
From: Scott Bailey
Date:
Subject: Re: I need help creating a composite type with some sort of constraints.
Next
From: Tom Lane
Date:
Subject: Re: Multicolumn index including tsvector.