SELECT * FROM patients WHERE "account_id" = 765 AND first_name ilike 'rahul%';
The query is currently taking 100ms plus, I have individually indexed account_id and first_name. First name is using gist index.
Bitmap Heap Scan on patients (cost=960.12..29301.39 rows=61 width=117) (actual time=8.399..100.206 rows=11 loops=1) Recheck Cond: (account_id = 765) Filter: ((first_name)::text ~~* 'rahul'::text) Rows Removed by Filter: 28978 Heap Blocks: exact=8948 -> Bitmap Index Scan on index_patients_on_account_id (cost=0.00..960.11 rows=31424 width=0) (actual time=4.569..4.569 rows=29003 loops=1) Index Cond: (account_id = 765) Planning Time: 0.774 ms Execution Time: 100.251 ms
I feel I need a multi column index, not sure about how to go about creating it.