From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | mayank rupareliya <mayankjr03(at)gmail(dot)com> |
Cc: | Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Searching in varchar column having 100M records |
Date: | 2019-07-19 17:33:41 |
Message-ID: | CAHOFxGqTH7J6N4CXdhEk84HJJBoYf3O49UER6w+aic5P0nD9oQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya <mayankjr03(at)gmail(dot)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.
https://www.postgresql.org/docs/current/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-ASYNC-BEHAVIOR
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. "
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2019-07-19 18:39:46 | Re: Searching in varchar column having 100M records |
Previous Message | mayank rupareliya | 2019-07-19 14:13:26 | Re: Searching in varchar column having 100M records |