On Fri, Jul 19, 2019 at 8:13 AM mayank rupareliya <mayankjr03@xxxxxxxxx> wrote:
Well, you haven't shown us the execution plan, so it's hard to check whyit 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.
"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. "
