Searching in varchar column having 100M records

From: mayank rupareliya <mayankjr03(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Searching in varchar column having 100M records
Date: 2019-07-17 11:03:41
Message-ID: CACX+qbyQJsdzwVx0bVWp-rYXu-TdPVHCAJ+vb02ZnJtCXOofCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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(at)aaa(dot)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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sergei Kornilov 2019-07-17 11:53:20 Re: Searching in varchar column having 100M records
Previous Message Vladimir Ryabtsev 2019-07-17 01:52:08 Re: Filtering on an enum field in a foreign table