we have query for fulltext where we have also another condition. Can you tell me what is correct usage of multiple indexes of different type?
select * from foo_tbl foo where foo.bar_tsv @@ to_tsquery('simple', 'xxx & yyy') and foo.reg_date is null and foo.is_active = true;
I have created index like this:
CREATE INDEX foo_fts_idx ON foo_tbl USING GIN(bar_tsv);
CREATE INDEX foo_active_idx ON foo_tbl(is_active); CREATE INDEX foo_reg_date_idx ON foo_tbl(reg_date);
when I execute query plan with
EXPLAIN command I find out there is hit only full text index, can you tell me how to fix it? Thank you.
Output of EXPLAIN (ANALYZE, BUFFERS):
"Bitmap Heap Scan on foo_tbl foo (cost=44.55..323.29 rows=71 width=2963) (actual time=0.475..0.497 rows=3 loops=1)" " Recheck Cond: (foo_tsv @@ '''xxx '':* & ''yyy'''::tsquery)" " Filter: ((reg_date IS NULL) AND is_active)" " Heap Blocks: exact=3" " Buffers: shared hit=24 read=4" " I/O Timings: read=0.089" " -> Bitmap Index Scan on foo_fts_idx (cost=0.00..44.53 rows=71 width=0) (actual time=0.412..0.412 rows=3 loops=1)" " Index Cond: (foo_tsv @@ '''xxx'':* & ''yyy'''::tsquery)" " Buffers: shared hit=24 read=1" " I/O Timings: read=0.014" "Planning time: 4.977 ms" "Execution time: 0.578 ms"
There is not used index for is_active nor reg_date.