Hi, I've tried your indexonlypatch5.patch against REL9_6_BETA3. Here are some results.
TL;DR: 1) <<where type=42 and upper(vc) like '%ABC%'>> does not support index-only scan for index (type, upper(vc) varchar_pattern_ops). 3) <<(... where type=42 offset 0) where upper_vc like '%ABC%'>> does trigger index-only scan. IOS reduces number of buffers from 977 to 17 and that is impressive. Can IOS be used for simple query like #1 as well? Here are the details. drop table vlsi; create table vlsi(type numeric, vc varchar(500)); insert into vlsi(type,vc) select round(x/1000), md5('||x)||md5('||x+1)||md5(''||x+2) from generate_series(1,1000000) as s(x); create index type_vc__vlsi on vlsi(type, upper(vc) varchar_pattern_ops); vacuum analyze vlsi; 0) Smoke test (index only scan works when selecting indexed expression): explain (analyze, buffers) select type, upper(vc) from vlsi where type=42; Index Only Scan using type_vc__vlsi on vlsi (cost=0.55..67.97 rows=971 width=36) (actual time=0.012..0.212 rows=1000 loops=1) Index Cond: (type = '42'::numeric) Heap Fetches: 0 Buffers: shared hit=17 Planning time: 0.112 ms Execution time: 0.272 ms 1) When trying to apply "like condition", index only scan does not work. Note: "buffers hit" becomes 977 instead of 17. explain (analyze, buffers) select type, upper(vc) from vlsi where type=42 and upper(vc) like '%ABC%'; Index Scan using type_vc__vlsi on vlsi (cost=0.55..1715.13 rows=20 width=36) (actual time=0.069..1.343 rows=23 loops=1) Index Cond: (type = '42'::numeric) Filter: (upper((vc)::text) ~~ '%ABC%'::text) Rows Removed by Filter: 977 Buffers: shared hit=939 Planning time: 0.104 ms Execution time: 1.358 ms Mere "subquery" does not help: still no index-only scan 2) explain (analyze, buffers) select * from (select type, upper(vc) upper_vc from vlsi where type=42) as x where upper_vc like '%ABC%'; Index Scan using type_vc__vlsi on vlsi (cost=0.55..1715.13 rows=20 width=36) (actual time=0.068..1.344 rows=23 loops=1) Index Cond: (type = '42'::numeric) Filter: (upper((vc)::text) ~~ '%ABC%'::text) Rows Removed by Filter: 977 Buffers: shared hit=939 Planning time: 0.114 ms Execution time: 1.357 ms 3) "offset 0" trick does help: explain (analyze, buffers) select * from (select type, upper(vc) upper_vc from vlsi where type=42 offset 0) as x where upper_vc like '%ABC%'; Subquery Scan on x (cost=0.55..80.11 rows=39 width=36) (actual time=0.033..0.488 rows=23 loops=1) Filter: (x.upper_vc ~~ '%ABC%'::text) Rows Removed by Filter: 977 Buffers: shared hit=17 -> Index Only Scan using type_vc__vlsi on vlsi (cost=0.55..67.97 rows=971 width=36) (actual time=0.015..0.210 rows=1000 loops=1) Index Cond: (type = '42'::numeric) Heap Fetches: 0 Buffers: shared hit=17 Planning time: 0.086 ms Execution time: 0.503 ms Vladimir