Re: Searching in varchar column having 100M records - Mailing list pgsql-performance

From Michael Lewis
Subject Re: Searching in varchar column having 100M records
Date
Msg-id CAHOFxGqTH7J6N4CXdhEk84HJJBoYf3O49UER6w+aic5P0nD9oQ@mail.gmail.com
Whole thread Raw
In response to Re: Searching in varchar column having 100M records  (mayank rupareliya <mayankjr03@gmail.com>)
List pgsql-performance


On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya <mayankjr03@gmail.com> wrote:
Well, you haven't shown us the execution plan, so it's hard to check why
it did not help much and give you further advice.

This is the latest query execution with explain after adding indexing on both columns.

Aggregate  (cost=174173.57..174173.58 rows=1 width=8) (actual time=65087.657..65087.658 rows=1 loops=1)
  ->  Bitmap Heap Scan on fields  (cost=1382.56..174042.61 rows=52386 width=0) (actual time=160.340..65024.533 rows=31857 loops=1)
        Recheck Cond: ((field)::text = 'Champlin'::text)
        Heap Blocks: exact=31433
        ->  Bitmap Index Scan on index_field  (cost=0.00..1369.46 rows=52386 width=0) (actual time=125.078..125.079 rows=31857 loops=1)
              Index Cond: ((field)::text = 'Champlin'::text)
Planning Time: 8.595 ms
Execution Time: 65093.508 ms



Are you on a solid state drive? If so, have you tried setting effective_io_concurrency to 200 or 300 and checking performance? Given nearly all of the execution time is doing a bitmap heap scan, I wonder about adjusting this.

effective_io_concurrency
"The allowed range is 1 to 1000, or zero to disable issuance of asynchronous I/O requests. Currently, this setting only affects bitmap heap scans."
"The default is 1 on supported systems, otherwise 0. " 

pgsql-performance by date:

Previous
From: mayank rupareliya
Date:
Subject: Re: Searching in varchar column having 100M records
Next
From: Tomas Vondra
Date:
Subject: Re: Searching in varchar column having 100M records