In one of my earlier emails [1], I mentioned that there seems to be a problem with how the cost for index only scans is being calculated.
[1]
https://www.postgresql.org/message-id/CANugjhsnh0OBMOYc7qKcC%2BZsVvAXCeF7QiidLuFvg6zmHy1C7A%40mail.gmail.comMy concern is that there seems to be a bigger disconnect between the cost of index only scan and the execution time. Having tested this on 3 different systems, docker, laptop and a server with RAID 5 SSD configured, at the threshold where index only scan cost exceeds that of sequential scan, index only is still around 30% faster than the sequential scan.
My initial hunch was that perhaps we need to consider a different approach when considering cost for index only scan. However, the solution seems somewhat simple.
cost_index function in costsize.c, in case of indexonlyscan, multiplies the number of pages fetched by a factor of (1.0 - baserel->allvisfrac) which is then used to calculate the max_IO_cost and min_IO_cost.
This is very similar to the cost estimate methods for indexes internally call genericostesimate function. This function primarily gets the number of pages for the indexes and multiplies that with random page cost (spc_random_page_cost) to get the total disk access cost.
I believe that in case of index only scan, we should adjust the spc_random_page_cost in context of baserel->allvisfrac so that it accounts for random pages for only the fraction that needs to be read for the relation and excludes that the index page fetches.
With the attached POC for this approach (based on the current master branch), index only scan which was previously bailing out at much earlier, approximately around when 50% of the index/table was being scanned. With the attached patch, this percentage goes upto 70%. The execution time for index only still remains well below that of the sequential scan, however, this is a significant improvement IMHO.
Following is the simple SQL that I was using to see how this patch impacts that indexonlyscan costing and execution time for the scan. You may tweak the table size or the number of rows being scanned for your environment..
SQL:
-----
CREATE TABLE index_test AS (SELECT GENERATE_SERIES(1, 1000000)::int id, 'hello world' AS name);
CREATE INDEX on index_test(id);
VACUUM ANALYZE index_test;
EXPLAIN ANALYZE SELECT COUNT(*) FROM index_test WHERE id < 7000000;
--
