How to speed up Postgresql Index Scan?

Question

PostgreSQL

Hello.
There is a table with 400 records, it contains an integer field, there is an index on this field .
You need to execute a query select * from table where field=value.
Here is the query plan:

Index Scan using index_table_on_field on table  (cost=0.57..19452.86 rows=6304 width=58) (actual time=261.061..2025.559 rows=332 loops=1)
   Index Cond: (value = 12345678)
 Total runtime: 2025.644 ms
(3 rows)

When executed again, the query is already in shared_buffers and runs much faster (a few ms).
How to speed up the initial execution of a query with an arbitrary value so that the data for any value is instantly found?
Why is the value of actual time so high in the query plan?

Answer 1

the output of the result at the first request rests on the speed of reading from the disk, the size of the index, and the so-called index bloat.
show (from psql):
1) \di+ index_table_on_field
2) select * from pgstattuple('index_table_on_field')
3) and briefly a few words about the disk subsystem (controller?, disk type, load average by sar -d)

Answer 2

Hello, sorry for the delay:
lesovsky:
1) There are indexes for the required field, I can’t show it now - I’m recreating the index after vacuum full
2) the following command gives an error ((
3) Such disks: www.hetzner.de/en/hosting/produkte_rootserver /ex60 - about sar - I don't know how to use it, Timosha is not installed
now:
1) I don't take all the lines, I use limit 5 first, then page by page limit 15 offset ...
2) Even when selecting select field from table where field =12345678, there is Index Only Scan instead of Scan - but still the search is long, and not nearly instant
3) See point 2.
4) I will look at the CLUSTER documentation. There is one more field in the table similar to the one you are looking for, the situation is similar with it (you also need a selection for this field). If you don't mind, briefly describe the benefits of CLUSTER.
5) Unfortunately, the table is constantly growing, there are up to 10 million unique field values ​​and the rows for each field are constantly growing, I want to give users instant information on any value, because for this I created an index!
PS shared_buffers how much to set, I now have several GB (I don’t remember exactly how much), and in total the memory on the machine is 48GB
PPS I kept the table for a month, gradually increased the volume, REINDEX and VACUUM didn’t do it at all
Data arrives evenly and is not deleted at all (99% INSERT 1% UPDATE)
I will be glad for any recommendations and directions for development)
I made VACUUM FULL and DROP / CREATE INDEX - now access to an arbitrary player_id value takes no more than 2 seconds, but it's still a lot. Repeated access to the player_id requested earlier takes already 0.5ms (sitting in shared_buffers).
Timosha:
1) I wrote a little wrong. I have 400 rows in my table. They have about 10k different player_id field values. I'm doing queries like "select * from table where player_id=123456789 limit 50 offset 150". Here, the main load goes to the where condition , therefore, when filtering with the condition where player_id=123456789, a maximum of 5-10k rows remains.
2) The main problem table participates. Relation_size - 36gb, Total_relation_size - 71gb, There are 4 btree indexes on Integer columns. All columns in the table are integer 2-4 bits (as needed).
4) The next step will be clustering
5) I increased it to 16gb - I'll check it after rebooting
Timosha:
First I'll try clustering and then the option with a partial index
lesovsky:
index sizes in the previous block
with offsets there are no problems
with this before - I made an intermediate table with order results via row_select - got integer numbers - row positions and now instead of limit 150 offset 1500 do where between 1500 and 1650 - very helpful