Instantly share code, notes, and snippets.

@vlsi /01_init.sql
Last active Sep 8, 2016

Embed
What would you like to do?
PostgreSQL index only scan for expression testing
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
-- 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)
-- 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
-- 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment