Re: Searching in varchar column having 100M records

[mayank rupareliya <mayankjr03@xxxxxxxxx>]

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@xxxxxxxxxxxxxxxxx> 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