Re: Searching in varchar column having 100M records

*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

*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.

Thanks Andreas, David, Gavin

*Any particular reason for using varchar instead of text, for field?* No

use UUID for the user_id. Agreed

*Regards,Mayank*

On Thu, Jul 18, 2019 at 4:25 AM Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
wrote:

> On 17/07/2019 23:03, mayank rupareliya wrote:
> [...]
> > Table and index are created using following query.
> >
> > create table fields(user_id varchar(64), field varchar(64));
> > CREATE INDEX index_field ON public.fields USING btree (field);
>
> [...]
>
> Any particular reason for using varchar instead of text, for field?
>
> Also, as Andreas pointed out, use UUID for the user_id.
>
>
> Cheers,
> Gavin
>
>
>
>