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