Clustering against an index

If you want to lookup things in this data set based on the values in the v column, the fundamental issue here has already been described. Each page of data has about 226 rows in it, which you can expect to have around 22 rows that contain each value present. So there's no way to pick a value that only shows up in a subset of the table data blocks.

If there is a particular value you do want your data to be organized around, it's possible to do so using the CLUSTER command, described in more detail next:

CLUSTER t USING i;
ANALYZE t;  

Since autovacuum is off, this is followed by a manual ANALYZE to make sure the change in structure of the table is noted. In particular, the statistics for the data will now reflect ...

Get PostgreSQL 10 High Performance now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.