Is it possible to use both GIN index and pgvector index for querying in postgres?

Question

I have a table like below in postgres:

create table posts (
    id bigserial,
    tags text[],
    content text,
    content_embedding vector(512)
);

create index on posts using GIN(tags);
-- from pgvector
create index ON posts USING hnsw(content_embedding vector_cosine_ops) WITH (m = 24, ef_construction = 100);

Each row is basically a post in a blog with content storing its text, tags is an array of tags (e.g. '{"database","coding"}'), content_embedding is where I store a vector representation of content generated with some AI model which I hope to use for semantic search.

I want to run queries like below to get posts whose tags contain database or hobby and order them by how "similar" they are to a given vector ('[...]' below for the sake of brevity):

select id, (content_embedding <=> '[...]') as cosine_similarity from posts where tags && '{"database","hobby"}' ORDER BY cosine_distance ASC

However, it looks like the query plan from explain analyze does not make use of the vector index as I hope

 Sort  (cost=8081.77..8089.15 rows=2952 width=16) (actual time=10.444..10.445 rows=20 loops=1)
   Sort Key: ((content_embedding <=> '[...]'::vector))
   Sort Method: quicksort  Memory: 26kB
   ->  Bitmap Heap Scan on posts (cost=1698.88..7911.62 rows=2952 width=16) (actual time=9.966..10.424 rows=20 loops=1)
         Recheck Cond: (tags && '{database,hobby}'::text[])
         Heap Blocks: exact=19
         ->  Bitmap Index Scan on posts_tags_idx  (cost=0.00..1698.14 rows=2952 width=0) (actual time=9.842..9.842 rows=20 loops=1)
               Index Cond: (tags && '{database,hobby}'::text[])
 Planning Time: 0.536 ms
 Execution Time: 10.496 ms

When I remove the where clause, I do see an index scan being used for sorting

 Index Scan using posts_content_embedding_idx on posts  (cost=164.90..41510.78 rows=301590 width=16)
   Order By: (content_embedding <=> '[...]'::vector)

I have around 300000 rows in posts. Is that a factor? Is there a way for postgres to use both the gin and hnsw indices? If it can't then how many rows is the limit before my query takes too long (>100ms)?

I'm aware that there are solutions built towards this use case of searching like Elasticsearch or maybe vector databases but I already have a postgres database and I hope to be able to stretch it as far as I can.

Answer 1

There is no mechanism for PostgreSQL to combine those types of index usages. A multicolumn GiST index can do it internally, but I don't know if hnsw can support something like that, I rather doubt it. (And when GiST does that, it seems not to be very efficient at it)

If it can't then how many rows is the limit before my query takes too long (>100ms)?

Surely you are in a better position to answer that than we are. You already have the data and the system, just pick the most common tags and see what happens.