Searching in varchar column having 100M records

mayank rupareliya <[email protected]>
Newsgroups gmane.comp.db.postgresql.performance
Message-ID <CACX+qbyQJsdzwVx0bVWp-rYXu-TdPVHCAJ+vb02ZnJtCXOofCw@mail.gmail.com>
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#[email protected]


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
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.