XyV5DSv8

1drop table if exists record;
2drop table if exists record_type;
3create extension if not exists pgcrypto;
4
5-- attributes of each record type including a parameter used in the indexes
6create table record_type (
7    id bigint not null primary key,
8    index_n_chars integer not null,
9    name text unique
10);
11
12insert into record_type (id,name,index_n_chars) values (1,'type1',2),(2,'type2',3);
13
14-- table for the actual records
15create table record (
16    id bigint generated by default as identity,
17    type_id bigint not null,
18    record_text text not null,
19    foreign key (type_id) references record_type(id)
20);
21
22-- generate some random data for record_types 1 and 2
23insert into record (type_id,record_text)
24select ((random()*i)::bigint%2)+1,gen_random_uuid()
25from generate_series(1,300000) g(i);
26
27analyze;
28
29set max_parallel_workers_per_gather = 0;
30
31-- partial index on each record type using a function index with a record_type specific parameter (i.e. 2,3)
32-- partial indexes work well with other queries in the application
33create index i1 on record((left(record_text,2))) where type_id = 1;
34create index i2 on record((left(record_text,3))) where type_id = 2;
35
36create or replace function testing() returns integer as
37$$
38    select 2;
39$$ language sql immutable strict;
40
41-- bitmap index scan on i1 with the expected index condition
42explain analyze select count(*) from record where type_id = 1 and left(record_text,2) = 'aa';
43explain analyze select count(*) from record where type_id = 1 and left(record_text,testing()) = 'aa';
44
45-- doesn't use the expected index condition
46explain analyze select count(*) from record where type_id = 1 and left(record_text,(select 2)) = 'aa';
47
48-- appears what I would have to generate dynamically to use the indexes to do these two searches in the same query using the indexes
49-- on my system this is 10-50x faster than the query using joins below
50explain analyze
51select type_id,count(*) from (
52    select * from record
53    where type_id = 1
54    and left(record_text,2) = 'aa'
55    union all
56    select * from record
57    where type_id = 2
58    and left(record_text,3) = 'aab'
59) q
60group by type_id;
61
62
63-- ultimately the type of query I would like to write using the indexes to find records matching the two searches
64explain analyze
65select r.type_id,count(*)
66from record r
67inner join record_type ri on r.type_id = ri.id
68inner join (
69    -- query 'aa' on record type1 and 'aab' on record type2
70    select v.type_id,v.query
71    from ( values (1,'aa'),(2,'aab') ) v(type_id,query)
72) q on q.type_id = r.type_id
73where left(record_text,index_n_chars) = q.query
74group by r.type_id;