From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: mayank rupareliya <mayankjr03@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Searching in varchar column having 100M records
Date: Fri, 19 Jul 2019 20:39:46 +0200
Message-ID: <20190719183946.ls5vmus7ukhbl63w@development> (raw)
In-Reply-To: <CACX+qbynqp2nN3S8VQAEx9rpbQ-4xs-WVpuSe_aPreiYA3OXmw@mail.gmail.com>
References: <CACX+qbyQJsdzwVx0bVWp-rYXu-TdPVHCAJ+vb02ZnJtCXOofCw@mail.gmail.com>
<d901d1d8-209f-0c8d-46bf-204d7c901173@archidevsys.co.nz>
<CACX+qby5i0NwW7+mjpNisziAZoQ3WPqv1TTVPyWxpOazar_PnQ@mail.gmail.com>
<20190718124109.2k22rkcrhviee5po@development>
<CACX+qbynqp2nN3S8VQAEx9rpbQ-4xs-WVpuSe_aPreiYA3OXmw@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, mayankjr03@gmail.com, pgsql-performance@lists.postgresql.org
Subject: Re: Searching in varchar column having 100M records
In-Reply-To: <20190719183946.ls5vmus7ukhbl63w@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox