Searching in varchar column having 100M records

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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-e12aff592241Northern Arkansas College
24c32757-e6a8-4dbd-aac7-1efd867156cefemale
6e225c57-c1d1-48a5-b9aa-513223efc81b1.0, 3.67, 3.67, 4.67, 7.0, 3.0
088c6342-a240-45a7-9d12-e0e707292031Weber
b05088cf-cba6-4bd7-8f8f-1469226874d0addd#$e@xxxxxxx


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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux