On Fri, Jul 5, 2024 at 8:44 PM Peter Geoghegan <p...@bowt.ie> wrote: > CREATE INDEX test4_idx ON test4 USING btree(((extract(year from d))::int4),n); > > This performs much better. Now I see "DEBUG: skipping 1 index > attributes" when I run the query "EXPLAIN (ANALYZE, BUFFERS) SELECT > COUNT(*) FROM test4 WHERE n > 900_000_000", which indicates that the > optimization has in fact been used as expected. There are far fewer > buffers hit with this version of your test4, which also indicates that > the optimization has been effective.
Actually, with an index-only scan it is 281 buffer hits (including some small number of VM buffer hits) with the patch, versus 2736 buffer hits on master. So a big change to the number of index page accesses only. If you use a plain index scan for this, then the cost of random heap accesses totally dominates, so skip scan cannot possibly give much benefit. Even a similar bitmap scan requires 4425 distinct heap page accesses, which is significantly more than the total number of index pages in the index. 4425 heap pages is almost the entire table; the table consists of 4480 mainfork blocks. This is a very nonselective query. It's not at all surprising that this query (and others like it) hardly benefit at all, except when we can use an index-only scan (so that the cost of heap accesses doesn't totally dominate). -- Peter Geoghegan
