function-index-test-case.sql

drop table if exists record;
drop table if exists record_type;
create extension if not exists pgcrypto;

-- attributes of each record type including a parameter used in the indexes
create table record_type (
    id bigint not null primary key,
    index_n_chars integer not null,
    name text unique
);

insert into record_type (id,name,index_n_chars) values (1,'type1',2),(2,'type2',3);

-- table for the actual records
create table record (
    id bigint generated by default as identity,
    type_id bigint not null,
    record_text text not null,
    foreign key (type_id) references record_type(id)
);

-- generate some random data for record_types 1 and 2
insert into record (type_id,record_text)
select ((random()*i)::bigint%2)+1,gen_random_uuid()
from generate_series(1,300000) g(i);

analyze;

set max_parallel_workers_per_gather = 0;

-- partial index on each record type using a function index with a record_type specific parameter (i.e. 2,3)
-- partial indexes work well with other queries in the application
create index i1 on record((left(record_text,2))) where type_id = 1;
create index i2 on record((left(record_text,3))) where type_id = 2;

create or replace function testing() returns integer as
$$
    select 2;
$$ language sql immutable strict;

-- bitmap index scan on i1 with the expected index condition
explain analyze select count(*) from record where type_id = 1 and left(record_text,2) = 'aa';
explain analyze select count(*) from record where type_id = 1 and left(record_text,testing()) = 'aa';

-- doesn't use the expected index condition
explain analyze select count(*) from record where type_id = 1 and left(record_text,(select 2)) = 'aa';

-- appears what I would have to generate dynamically to use the indexes to do these two searches in the same query using the indexes
-- on my system this is 10-50x faster than the query using joins below
explain analyze
select type_id,count(*) from (
    select * from record
    where type_id = 1
    and left(record_text,2) = 'aa'
    union all
    select * from record
    where type_id = 2
    and left(record_text,3) = 'aab'
) q
group by type_id;


-- ultimately the type of query I would like to write using the indexes to find records matching the two searches
explain analyze
select r.type_id,count(*)
from record r
inner join record_type ri on r.type_id = ri.id
inner join (
    -- query 'aa' on record type1 and 'aab' on record type2
    select v.type_id,v.query
    from ( values (1,'aa'),(2,'aab') ) v(type_id,query)
) q on q.type_id = r.type_id
where left(record_text,index_n_chars) = q.query
group by r.type_id;