Another suggestion, try to cluster the table using the index for the "field" column, then analyze. If you're on a spinning disk it will help if you sort your search "field" during bulk insert. --
regards marie g. bacuno ii On Fri, Jul 19, 2019 at 11:39 AM Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > On Fri, Jul 19, 2019 at 07:43:26PM +0530, mayank rupareliya 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 > > > > That very clearly does not use the index-only scan, so it's not > surprising it's not any faster. You need to find out why the planner > makes that decision. > > regards > > -- > Tomas Vondra http://www.2ndQuadrant.com > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > >
