SHARE
TWEET

Untitled

a guest Apr 24th, 2019 68 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. drop table if exists record;
  2. drop table if exists record_type;
  3. create extension if not exists pgcrypto;
  4.  
  5. -- attributes of each record type including a parameter used in the indexes
  6. create table record_type (
  7.     id bigint not null primary key,
  8.     index_n_chars integer not null,
  9.     name text unique
  10. );
  11.  
  12. insert into record_type (id,name,index_n_chars) values (1,'type1',2),(2,'type2',3);
  13.  
  14. -- table for the actual records
  15. create 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
  23. insert into record (type_id,record_text)
  24. select ((random()*i)::bigint%2)+1,gen_random_uuid()
  25. from generate_series(1,300000) g(i);
  26.  
  27. analyze;
  28.  
  29. set 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
  33. create index i1 on record((left(record_text,2))) where type_id = 1;
  34. create index i2 on record((left(record_text,3))) where type_id = 2;
  35.  
  36. create 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
  42. explain analyze select count(*) from record where type_id = 1 and left(record_text,2) = 'aa';
  43. explain analyze select count(*) from record where type_id = 1 and left(record_text,testing()) = 'aa';
  44.  
  45. -- doesn't use the expected index condition
  46. explain 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
  50. explain analyze
  51. select 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
  60. group 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
  64. explain analyze
  65. select r.type_id,count(*)
  66. from record r
  67. inner join record_type ri on r.type_id = ri.id
  68. inner 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
  73. where left(record_text,index_n_chars) = q.query
  74. group by r.type_id;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top