Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement