My table is having data like below with 100M records (contains all dummy data). I am having btree index on column ("field").
While searching for any text from that column takes longer (more than 1 minute).| user Id | field |
| d848f466-5e12-46e7-acf4-e12aff592241 | Northern Arkansas College |
| 24c32757-e6a8-4dbd-aac7-1efd867156ce | female |
| 6e225c57-c1d1-48a5-b9aa-513223efc81b | 1.0, 3.67, 3.67, 4.67, 7.0, 3.0 |
| 088c6342-a240-45a7-9d12-e0e707292031 | Weber |
| b05088cf-cba6-4bd7-8f8f-1469226874d0 | addd#$e@aaa.com |
Table and index are created using following query.
create table fields(user_id varchar(64), field varchar(64));
CREATE INDEX index_field ON public.fields USING btree (field);
Search Query:
EXPLAIN (ANALYZE, BUFFERS) select * from fields where field='Mueller';
Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=55) (actual time=88.017..65358.548 rows=31882 loops=1)
Recheck Cond: ((field)::text = 'Mueller'::text)
Heap Blocks: exact=31403
Buffers: shared hit=2 read=31492
-> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586 width=0) (actual time=55.960..55.960 rows=31882 loops=1)
Index Cond: ((field)::text = 'Mueller'::text)
Buffers: shared read=91
Planning Time: 0.331 ms
Execution Time: 65399.314 ms
Any suggestions for improvement?
Best Regards,
Mayank
