On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya <mayankj...@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. 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. "
