PostgreSQL index only scan for expression testing

01_init.sql

drop table vlsi;

create table vlsi(pk int4, type numeric, vc varchar(500), num numeric);

insert into vlsi(pk, type,vc,num) select s.x, round(x/1000), md5('||x)||md5('||x+1)||md5(''||x+2), mod(x, 1000)
  from generate_series(1,1000000) as s(x);

-- Several values exceeed 128 substr, so they require "table access"
insert into vlsi(pk, type,vc,num) select s.x+1000000, round(x/1000), lpad('a', 128, 'a')||'zxc'||s.x||'v', mod(x, 1000)
  from generate_series(1,10) as s(x);

create index type_vc__vlsi on vlsi(type, coalesce(upper(substr(vc,1,128)),num::varchar), pk);

vacuum analyze vlsi;

-- Below results are provided for
-- PostgreSQL 10devel on x86_64-apple-darwin15.6.0, compiled by Apple LLVM version 7.0.0 (clang-700.1.76), 64-bit
-- indexonlyscan6.patch on top of https://github.com/postgres/postgres/commit/19998730aea97137e2516af0516c683a1261ba1f

02_case1_works.sql

-- Simple check filter on expression works as expected
-- It manages to filter on a expression column

explain (analyze, buffers) select pk from vlsi where type=42 and coalesce(upper(substr(vc,1,128)),num::varchar) like '%ABC%';
                                                        QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Index Only Scan using type_vc__vlsi on vlsi  (cost=0.55..81.59 rows=1 width=4) (actual time=0.034..0.406 rows=23 loops=1)
   Index Cond: (type = '42'::numeric)
   Filter: ((COALESCE(upper(substr((vc)::text, 1, 128)), ((num)::character varying)::text)) ~~ '%ABC%'::text)
   Rows Removed by Filter: 977
   Heap Fetches: 0
   Buffers: shared hit=18
 Planning time: 0.168 ms
 Execution time: 0.423 ms
(8 rows)

02_case2_fails.sql

-- The idea behind the case is as follows: there is a small amount of rows that exceed 128 characters
-- Thus we cannot safely use index-only scan for those specific rows
-- The idea is to use index-only scan for those rows that are below 128, and use "table access" only for
-- those rows that have expression length of 128

-- Unfortunately, it fails with "ERROR:  variable not found in subplan target list"

explain (analyze, buffers) select pk
  from vlsi
  where type=0 and (
     coalesce(upper(substr(vc,1,128)),num::varchar) like '%ZXC1V%'
     or ( -- Remember, long values cannot be fetched from the index
     -- thus if index entry is 128 chars long, then check original value
      length(coalesce(upper(substr(vc,1,128)),num::varchar))=128
        and upper(vc) like '%ZXC1V'
     )
  );

ERROR:  variable not found in subplan target list

explain (analyze, buffers) select pk
  from vlsi v1
  where type=0 and (
     1=2--coalesce(upper(substr(vc,1,128)),num::varchar) like '%ZXC1V%'
     or ( -- Remember, long values cannot be fetched from the index
     -- thus if index entry is 128 chars long, then check original value
      1=1--length(coalesce(upper(substr(vc,1,128)),num::varchar))=128
      and exists (select 1 from vlsi v2 where v2.ctid=v1.ctid and upper(v2.vc) like '%ZXC1V')
     )
  );
ERROR:  variable not found in subplan target list

explain (analyze, buffers) select pk
  from vlsi v1
  where type=0 and exists (select 1 from vlsi v2 where v2.ctid=v1.ctid and upper(v2.vc) like '%ZXC1V');

ERROR:  variable not found in subplan target list

03_case3_suboptimal.sql

-- Technically speaking, fetching ctid out of index might be a bit off-topic for "index-only scan for expression" patch
-- However, ctid might help to resolve case2, so I list the case here

explain (analyze, buffers) select pk, ctid
  from vlsi v1
  where type=0;

-- It looks like index only scan cannot fetch ctid from the index :-/

                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Index Scan using type_vc__vlsi on vlsi v1  (cost=0.55..1714.74 rows=968 width=10) (actual time=0.020..0.436 rows=1009 loops=1)
   Index Cond: (type = '0'::numeric)
   Buffers: shared hit=953
 Planning time: 0.074 ms
 Execution time: 0.527 ms
(5 rows)