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 <[email protected]>
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
>
>
>
>