Re: Searching in varchar column having 100M records

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.vondra@xxxxxxxxxxxxxxx> 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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux