0

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?

for instance:

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);

and

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.

EDIT:

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.

  • 2
    From the data type of is_active, there are chances that the index won't be used ever (unless it's true only in a small amount of rows). But the main question is: why do you think it would be better to use two indexes? What does EXPLAIN (ANALYZE, BUFFERS) tell you about the query performance? – dezso Mar 19 at 12:07
  • What percentage of your table is_active? – jjanes Mar 19 at 12:26
  • @dezso I added into question output of explain – Denis Stephanov Mar 19 at 12:58
  • @jjanes about 50% – Denis Stephanov Mar 19 at 12:58
  • 1
    PostgreSQL will automatically combine indexes when it thinks it is beneficial. There is no reason to think it will be beneficial in this case. – jjanes Mar 19 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.