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