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: Thu, 18 Jul 2019 14:41:09 +0200
Message-ID: <20190718124109.2k22rkcrhviee5po@development> (raw)
In-Reply-To: <CACX+qby5i0NwW7+mjpNisziAZoQ3WPqv1TTVPyWxpOazar_PnQ@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>
On Thu, Jul 18, 2019 at 05:21:49PM +0530, mayank rupareliya wrote:
>*Please recheck with track_io_timing = on in configuration. explain
>(analyze,buffers) with this option will report how many time we spend
>during i/o*
>
>*> Buffers: shared hit=2 read=31492*
>
>*31492 blocks / 65 sec ~ 480 IOPS, not bad if you are using HDD*
>
>*Your query reads table data from disks (well, or from OS cache). You need
>more RAM for shared_buffers or disks with better performance.*
>
>
>Thanks Sergei..
>*track_io_timing = on helps.. Following is the result after changing that
>config.*
>
>Aggregate (cost=10075.78..10075.79 rows=1 width=8) (actual
>time=63088.198..63088.199 rows=1 loops=1)
> Buffers: shared read=31089
> I/O Timings: read=61334.252
> -> Bitmap Heap Scan on fields (cost=72.61..10069.32 rows=2586 width=0)
>(actual time=69.509..63021.448 rows=31414 loops=1)
> Recheck Cond: ((field)::text = 'Klein'::text)
> Heap Blocks: exact=30999
> Buffers: shared read=31089
> I/O Timings: read=61334.252
> -> Bitmap Index Scan on index_field (cost=0.00..71.96 rows=2586
>width=0) (actual time=58.671..58.671 rows=31414 loops=1)
> Index Cond: ((field)::text = 'Klein'::text)
> Buffers: shared read=90
> I/O Timings: read=45.316
>Planning Time: 66.435 ms
>Execution Time: 63088.774 ms
>
How did that help? It only gives you insight that it's really the I/O that
takes time. You need to reduce that, somehow.
>
>*So try something like*
>
>* CREATE INDEX ios_idx ON table (field, user_id);*
>
>*and make sure the table is vacuumed often enough (so that the visibility*
>*map is up to date).*
>
>Thanks Tomas... I tried this and result improved but not much.
>
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.
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: <20190718124109.2k22rkcrhviee5po@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