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 that the structure of the table is highly correlated with the v field.

Now, if you execute a query that only looks at a portion of the table based on the value field, that can be executed efficiently using an index scan:

EXPLAIN ANALYZE...