compress_compbloom_basics.out

-- This file and its contents are licensed under the Timescale License.
-- Please see the included NOTICE for copyright information and
-- LICENSE-TIMESCALE for a copy of the license.
CREATE VIEW settings AS SELECT * FROM _timescaledb_catalog.compression_settings ORDER BY upper(relid::text) COLLATE "C";
CREATE VIEW metacols AS select relname,attname,count(*) from pg_attribute a, pg_class c where c.oid=a.attrelid and attname like '%_ts_meta%' and relname like '%chunk' GROUP BY 1,2 ORDER BY relname::text COLLATE "C", attname::text COLLATE "C";
CREATE VIEW compressedcols AS select relname,attname,c.oid as reloid,attnum from pg_attribute a, pg_class c where c.oid=a.attrelid and relname like '%compress_hyper_%' order by c.oid asc, a.attnum asc;
create table sparse(
    ts int,
    o bigint,
    value float,
    boo bigint,
    big1 bigint,
    big2 bigint,
    sby bigint,
    small1 smallint,
    small2 int2,
    num numeric,
    nowts timestamptz,
    hello bytea);
select create_hypertable('sparse', 'ts', create_default_indexes=>false);
  create_hypertable  
---------------------
 (1,public,sparse,t)

insert into sparse select x, x, x, x, x, x, x, x%4, x%4, x::numeric, now()::timestamptz, md5(x::text)::bytea from generate_series(1, 10000) x;
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(big2),bloom(value),bloom(value,big1,big2),bloom(o,big2),bloom(big1,big2),bloom(boo,big1),bloom(small1,small2),bloom(num,nowts),bloom(num,hello)');
select count(compress_chunk(x)) from show_chunks('sparse') x;
 count 
-------
     1

vacuum analyze sparse;
-- smoke tests
select min(big1), max(big2) from sparse where value < 100 and value > 10;
 min | max 
-----+-----
  11 |  99

select relname,attname from metacols order by 1,2;
         relname          |                 attname                 
--------------------------+-----------------------------------------
 compress_hyper_2_2_chunk | _ts_meta_count
 compress_hyper_2_2_chunk | _ts_meta_max_1
 compress_hyper_2_2_chunk | _ts_meta_max_2
 compress_hyper_2_2_chunk | _ts_meta_min_1
 compress_hyper_2_2_chunk | _ts_meta_min_2
 compress_hyper_2_2_chunk | regress-test-bloom_27f1_big1_big2
 compress_hyper_2_2_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_2_2_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_2_2_chunk | regress-test-bloom_5c7a_value_big1_big2
 compress_hyper_2_2_chunk | regress-test-bloom_9774_o_big2
 compress_hyper_2_2_chunk | regress-test-bloom_big1
 compress_hyper_2_2_chunk | regress-test-bloom_big2
 compress_hyper_2_2_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_2_2_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_2_2_chunk | regress-test-bloom_value

select index from settings where relid = (select oid from pg_class where relname = 'sparse') and index is not null group by 1 order by 1;
                                                                                                                                                                                                                                                                                                                                                                                            index                                                                                                                                                                                                                                                                                                                                                                                            
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"type": "bloom", "column": "big1", "source": "config"}, {"type": "bloom", "column": "big2", "source": "config"}, {"type": "bloom", "column": "value", "source": "config"}, {"type": "bloom", "column": ["value", "big1", "big2"], "source": "config"}, {"type": "bloom", "column": ["o", "big2"], "source": "config"}, {"type": "bloom", "column": ["big1", "big2"], "source": "config"}, {"type": "bloom", "column": ["boo", "big1"], "source": "config"}, {"type": "bloom", "column": ["small1", "small2"], "source": "config"}, {"type": "bloom", "column": ["num", "nowts"], "source": "config"}, {"type": "bloom", "column": ["num", "hello"], "source": "config"}, {"type": "minmax", "column": "o", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]

select distinct(attname) from compressedcols where relname = 'sparse' order by 1 asc;
 attname 
---------

-- show plan
explain (buffers off, costs off) select * from sparse where value = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: (value = '1'::double precision)
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_value, TEST-HASHES::bigint[])

explain (buffers off, costs off) select * from sparse where o = 1 and big2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: ((o = 1) AND (big2 = 1))
   ->  Index Scan using compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx on compress_hyper_2_2_chunk
         Index Cond: ((_ts_meta_min_1 <= 1) AND (_ts_meta_max_1 >= 1))
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[]))

explain (buffers off, costs off) select * from sparse where num = 1 and hello = md5('1')::bytea;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Filter: ((num = '1'::numeric) AND (hello = '\x6334636134323338613062393233383230646363353039613666373538343962'::bytea))
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_c250_num_hello, TEST-HASHES::bigint[])

explain (buffers off, costs off) select * from sparse where small1 = 1 and small2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: ((small1 = 1) AND (small2 = 1))
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_cab4_small1_small2, TEST-HASHES::bigint[])

explain (buffers off, costs off) select * from sparse where num = 1 and nowts = now()::timestamptz;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on sparse
   Chunks excluded during startup: 0
   ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk
         Filter: (num = '1'::numeric)
         Vectorized Filter: (nowts = now())
         ->  Seq Scan on compress_hyper_2_2_chunk
               Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_4328_num_nowts, TEST-HASHES::bigint[])

explain (buffers off, costs off) select * from sparse where o in (1,2) and big2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: ((o = ANY ('{1,2}'::bigint[])) AND (big2 = 1))
   ->  Bitmap Heap Scan on compress_hyper_2_2_chunk
         Recheck Cond: (((_ts_meta_min_1 <= '1'::bigint) AND (_ts_meta_max_1 >= '1'::bigint)) OR ((_ts_meta_min_1 <= '2'::bigint) AND (_ts_meta_max_1 >= '2'::bigint)))
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[])
         ->  BitmapOr
               ->  Bitmap Index Scan on compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx
                     Index Cond: ((_ts_meta_min_1 <= '1'::bigint) AND (_ts_meta_max_1 >= '1'::bigint))
               ->  Bitmap Index Scan on compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx
                     Index Cond: ((_ts_meta_min_1 <= '2'::bigint) AND (_ts_meta_max_1 >= '2'::bigint))

explain (buffers off, costs off) select * from sparse where o = 1 and big2 in (1,2);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: ((big2 = ANY ('{1,2}'::bigint[])) AND (o = 1))
   ->  Index Scan using compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx on compress_hyper_2_2_chunk
         Index Cond: ((_ts_meta_min_1 <= 1) AND (_ts_meta_max_1 >= 1))
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[])

explain (buffers off, costs off) select * from sparse where o in (1,2) and big2 in (1,2);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Vectorized Filter: ((o = ANY ('{1,2}'::bigint[])) AND (big2 = ANY ('{1,2}'::bigint[])))
   ->  Bitmap Heap Scan on compress_hyper_2_2_chunk
         Recheck Cond: (((_ts_meta_min_1 <= '1'::bigint) AND (_ts_meta_max_1 >= '1'::bigint)) OR ((_ts_meta_min_1 <= '2'::bigint) AND (_ts_meta_max_1 >= '2'::bigint)))
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[])
         ->  BitmapOr
               ->  Bitmap Index Scan on compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx
                     Index Cond: ((_ts_meta_min_1 <= '1'::bigint) AND (_ts_meta_max_1 >= '1'::bigint))
               ->  Bitmap Index Scan on compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx
                     Index Cond: ((_ts_meta_min_1 <= '2'::bigint) AND (_ts_meta_max_1 >= '2'::bigint))

-- segmentby = bloom
explain (buffers off, costs off) select * from sparse where big1 = sby and value = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Filter: (big1 = sby)
   Vectorized Filter: (value = '1'::double precision)
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_value, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big1, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse where o = sby and big2 = sby;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Filter: ((o = sby) AND (sby = big2))
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: ((_ts_meta_min_1 <= sby) AND (_ts_meta_max_1 >= sby) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(sby, sby))]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse where o = 1 and big2 = sby;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Filter: (big2 = sby)
   Vectorized Filter: (o = 1)
   ->  Index Scan using compress_hyper_2_2_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx on compress_hyper_2_2_chunk
         Index Cond: ((_ts_meta_min_1 <= 1) AND (_ts_meta_max_1 >= 1))
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(1, sby))]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse where o = sby and big2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk
   Filter: (o = sby)
   Vectorized Filter: (big2 = 1)
   ->  Seq Scan on compress_hyper_2_2_chunk
         Filter: ((_ts_meta_min_1 <= sby) AND (_ts_meta_max_1 >= sby) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(sby, 1))]))

-- create a sister table where the same composite blooms should be auto created
create table sparse_sister as select * from sparse where ts < -1;
select create_hypertable('sparse_sister', 'ts', create_default_indexes=>false);
     create_hypertable      
----------------------------
 (3,public,sparse_sister,t)

alter table sparse_sister set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby');
insert into sparse_sister select * from sparse;
create index on sparse_sister(value,big1,big2);
create index on sparse_sister(o,big2);
create index on sparse_sister(big1,big2);
create index on sparse_sister(boo,big1);
create index on sparse_sister(small1,small2);
create index on sparse_sister(num,nowts);
create index on sparse_sister(num,hello);
select count(compress_chunk(x)) from show_chunks('sparse_sister') x;
 count 
-------
     1

vacuum analyze sparse_sister;
-- smoke tests
select min(big1), max(big2) from sparse_sister where value < 100 and value > 10;
 min | max 
-----+-----
  11 |  99

select relname,attname from metacols order by 1,2;
         relname          |                 attname                 
--------------------------+-----------------------------------------
 compress_hyper_2_2_chunk | _ts_meta_count
 compress_hyper_2_2_chunk | _ts_meta_max_1
 compress_hyper_2_2_chunk | _ts_meta_max_2
 compress_hyper_2_2_chunk | _ts_meta_min_1
 compress_hyper_2_2_chunk | _ts_meta_min_2
 compress_hyper_2_2_chunk | regress-test-bloom_27f1_big1_big2
 compress_hyper_2_2_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_2_2_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_2_2_chunk | regress-test-bloom_5c7a_value_big1_big2
 compress_hyper_2_2_chunk | regress-test-bloom_9774_o_big2
 compress_hyper_2_2_chunk | regress-test-bloom_big1
 compress_hyper_2_2_chunk | regress-test-bloom_big2
 compress_hyper_2_2_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_2_2_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_2_2_chunk | regress-test-bloom_value
 compress_hyper_4_4_chunk | _ts_meta_count
 compress_hyper_4_4_chunk | _ts_meta_max_1
 compress_hyper_4_4_chunk | _ts_meta_max_2
 compress_hyper_4_4_chunk | _ts_meta_min_1
 compress_hyper_4_4_chunk | _ts_meta_min_2
 compress_hyper_4_4_chunk | regress-test-bloom_27f1_big1_big2
 compress_hyper_4_4_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_4_4_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_4_4_chunk | regress-test-bloom_9774_o_big2
 compress_hyper_4_4_chunk | regress-test-bloom_big1
 compress_hyper_4_4_chunk | regress-test-bloom_big2
 compress_hyper_4_4_chunk | regress-test-bloom_boo
 compress_hyper_4_4_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_4_4_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_4_4_chunk | regress-test-bloom_hello
 compress_hyper_4_4_chunk | _ts_meta_v2_max_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_max_num
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_max_value
 compress_hyper_4_4_chunk | _ts_meta_v2_min_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_min_num
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_min_value

select index from settings where relid = (select oid from pg_class where relname = 'sparse_sister') and index is not null group by 1 order by 1;
                                                       index                                                       
-------------------------------------------------------------------------------------------------------------------
 [{"type": "minmax", "column": "o", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]

select distinct(attname) from compressedcols where relname = 'sparse_sister' order by 1 asc;
 attname 
---------

-- show plan
explain (buffers off, costs off) select * from sparse_sister where value = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Vectorized Filter: (value = '1'::double precision)
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: ((_ts_meta_v2_min_value <= '1'::double precision) AND (_ts_meta_v2_max_value >= '1'::double precision))

explain (buffers off, costs off) select * from sparse_sister where o = 1 and big2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Vectorized Filter: ((o = 1) AND (big2 = 1))
   ->  Index Scan using compress_hyper_4_4_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx on compress_hyper_4_4_chunk
         Index Cond: ((_ts_meta_min_1 <= 1) AND (_ts_meta_max_1 >= 1))
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[]))

explain (buffers off, costs off) select * from sparse_sister where num = 1 and hello = md5('1')::bytea;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Filter: ((num = '1'::numeric) AND (hello = '\x6334636134323338613062393233383230646363353039613666373538343962'::bytea))
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_c250_num_hello, TEST-HASHES::bigint[]) AND (_ts_meta_v2_min_num <= '1'::numeric) AND (_ts_meta_v2_max_num >= '1'::numeric) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_hello, TEST-HASHES::bigint[]))

explain (buffers off, costs off) select * from sparse_sister where small1 = 1 and small2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Vectorized Filter: ((small1 = 1) AND (small2 = 1))
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_cab4_small1_small2, TEST-HASHES::bigint[]) AND (_ts_meta_v2_min_small1 <= 1) AND (_ts_meta_v2_max_small1 >= 1) AND (_ts_meta_v2_min_small2 <= 1) AND (_ts_meta_v2_max_small2 >= 1))

explain (buffers off, costs off) select * from sparse_sister where num = 1 and nowts = now()::timestamptz;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on sparse_sister
   Chunks excluded during startup: 0
   ->  Custom Scan (ColumnarScan) on _hyper_3_3_chunk
         Filter: (num = '1'::numeric)
         Vectorized Filter: (nowts = now())
         ->  Seq Scan on compress_hyper_4_4_chunk
               Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_4328_num_nowts, TEST-HASHES::bigint[]) AND (_ts_meta_v2_min_num <= '1'::numeric) AND (_ts_meta_v2_max_num >= '1'::numeric) AND (_ts_meta_v2_min_nowts <= now()) AND (_ts_meta_v2_max_nowts >= now()))

-- segmentby = bloom
explain (buffers off, costs off) select * from sparse_sister where big1 = sby and value = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Filter: (big1 = sby)
   Vectorized Filter: (value = '1'::double precision)
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: ((_ts_meta_v2_min_value <= '1'::double precision) AND (_ts_meta_v2_max_value >= '1'::double precision) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big1, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse_sister where o = sby and big2 = sby;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Filter: ((o = sby) AND (sby = big2))
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: ((_ts_meta_min_1 <= sby) AND (_ts_meta_max_1 >= sby) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(sby, sby))]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse_sister where o = 1 and big2 = sby;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Filter: (big2 = sby)
   Vectorized Filter: (o = 1)
   ->  Index Scan using compress_hyper_4_4_chunk_sby__ts_meta_min_1__ts_meta_max_1__idx on compress_hyper_4_4_chunk
         Index Cond: ((_ts_meta_min_1 <= 1) AND (_ts_meta_max_1 >= 1))
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(1, sby))]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, ARRAY[_timescaledb_functions.bloom1_hash(sby)]))

explain (buffers off, costs off) select * from sparse_sister where o = sby and big2 = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_3_3_chunk
   Filter: (o = sby)
   Vectorized Filter: (big2 = 1)
   ->  Seq Scan on compress_hyper_4_4_chunk
         Filter: ((_ts_meta_min_1 <= sby) AND (_ts_meta_max_1 >= sby) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_big2, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_9774_o_big2, ARRAY[_timescaledb_functions.bloom1_hash(ROW(sby, 1))]))

-- renaming a column that participates in a composite bloom filter
alter table sparse rename big2 to xxl;
select relname,attname from metacols order by 1,2;
         relname          |                attname                 
--------------------------+----------------------------------------
 compress_hyper_2_2_chunk | _ts_meta_count
 compress_hyper_2_2_chunk | _ts_meta_max_1
 compress_hyper_2_2_chunk | _ts_meta_max_2
 compress_hyper_2_2_chunk | _ts_meta_min_1
 compress_hyper_2_2_chunk | _ts_meta_min_2
 compress_hyper_2_2_chunk | regress-test-bloom_07a7_value_big1_xxl
 compress_hyper_2_2_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_2_2_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_2_2_chunk | regress-test-bloom_43c0_o_xxl
 compress_hyper_2_2_chunk | regress-test-bloom_b99a_big1_xxl
 compress_hyper_2_2_chunk | regress-test-bloom_big1
 compress_hyper_2_2_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_2_2_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_2_2_chunk | regress-test-bloom_value
 compress_hyper_2_2_chunk | regress-test-bloom_xxl
 compress_hyper_4_4_chunk | _ts_meta_count
 compress_hyper_4_4_chunk | _ts_meta_max_1
 compress_hyper_4_4_chunk | _ts_meta_max_2
 compress_hyper_4_4_chunk | _ts_meta_min_1
 compress_hyper_4_4_chunk | _ts_meta_min_2
 compress_hyper_4_4_chunk | regress-test-bloom_27f1_big1_big2
 compress_hyper_4_4_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_4_4_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_4_4_chunk | regress-test-bloom_9774_o_big2
 compress_hyper_4_4_chunk | regress-test-bloom_big1
 compress_hyper_4_4_chunk | regress-test-bloom_big2
 compress_hyper_4_4_chunk | regress-test-bloom_boo
 compress_hyper_4_4_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_4_4_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_4_4_chunk | regress-test-bloom_hello
 compress_hyper_4_4_chunk | _ts_meta_v2_max_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_max_num
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_max_value
 compress_hyper_4_4_chunk | _ts_meta_v2_min_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_min_num
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_min_value

select index from settings where relid = (select oid from pg_class where relname = 'sparse') and index is not null group by 1 order by 1;
                                                                                                                                                                                                                                                                                                                                                                                          index                                                                                                                                                                                                                                                                                                                                                                                          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"type": "bloom", "column": "big1", "source": "config"}, {"type": "bloom", "column": "xxl", "source": "config"}, {"type": "bloom", "column": "value", "source": "config"}, {"type": "bloom", "column": ["value", "big1", "xxl"], "source": "config"}, {"type": "bloom", "column": ["o", "xxl"], "source": "config"}, {"type": "bloom", "column": ["big1", "xxl"], "source": "config"}, {"type": "bloom", "column": ["boo", "big1"], "source": "config"}, {"type": "bloom", "column": ["small1", "small2"], "source": "config"}, {"type": "bloom", "column": ["num", "nowts"], "source": "config"}, {"type": "bloom", "column": ["num", "hello"], "source": "config"}, {"type": "minmax", "column": "o", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]

select distinct(attname) from compressedcols where relname = 'sparse' order by 1 asc;
 attname 
---------

-- dropping a column that participates in a composite bloom filter
alter table sparse drop column xxl;
select relname,attname from metacols order by 1,2;
         relname          |                attname                
--------------------------+---------------------------------------
 compress_hyper_2_2_chunk | _ts_meta_count
 compress_hyper_2_2_chunk | _ts_meta_max_1
 compress_hyper_2_2_chunk | _ts_meta_max_2
 compress_hyper_2_2_chunk | _ts_meta_min_1
 compress_hyper_2_2_chunk | _ts_meta_min_2
 compress_hyper_2_2_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_2_2_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_2_2_chunk | regress-test-bloom_big1
 compress_hyper_2_2_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_2_2_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_2_2_chunk | regress-test-bloom_value
 compress_hyper_4_4_chunk | _ts_meta_count
 compress_hyper_4_4_chunk | _ts_meta_max_1
 compress_hyper_4_4_chunk | _ts_meta_max_2
 compress_hyper_4_4_chunk | _ts_meta_min_1
 compress_hyper_4_4_chunk | _ts_meta_min_2
 compress_hyper_4_4_chunk | regress-test-bloom_27f1_big1_big2
 compress_hyper_4_4_chunk | regress-test-bloom_2cb5_boo_big1
 compress_hyper_4_4_chunk | regress-test-bloom_4328_num_nowts
 compress_hyper_4_4_chunk | regress-test-bloom_9774_o_big2
 compress_hyper_4_4_chunk | regress-test-bloom_big1
 compress_hyper_4_4_chunk | regress-test-bloom_big2
 compress_hyper_4_4_chunk | regress-test-bloom_boo
 compress_hyper_4_4_chunk | regress-test-bloom_c250_num_hello
 compress_hyper_4_4_chunk | regress-test-bloom_cab4_small1_small2
 compress_hyper_4_4_chunk | regress-test-bloom_hello
 compress_hyper_4_4_chunk | _ts_meta_v2_max_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_max_num
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_max_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_max_value
 compress_hyper_4_4_chunk | _ts_meta_v2_min_nowts
 compress_hyper_4_4_chunk | _ts_meta_v2_min_num
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small1
 compress_hyper_4_4_chunk | _ts_meta_v2_min_small2
 compress_hyper_4_4_chunk | _ts_meta_v2_min_value

select index from settings where relid = (select oid from pg_class where relname = 'sparse') and index is not null group by 1 order by 1;
                                                                                                                                                                                                                                                        index                                                                                                                                                                                                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"type": "bloom", "column": "big1", "source": "config"}, {"type": "bloom", "column": "value", "source": "config"}, {"type": "bloom", "column": ["boo", "big1"], "source": "config"}, {"type": "bloom", "column": ["small1", "small2"], "source": "config"}, {"type": "bloom", "column": ["num", "nowts"], "source": "config"}, {"type": "bloom", "column": ["num", "hello"], "source": "config"}, {"type": "minmax", "column": "o", "source": "orderby"}, {"type": "minmax", "column": "ts", "source": "orderby"}]

select distinct(attname) from compressedcols where relname = 'sparse' order by 1 asc;
 attname 
---------

-- droppping an orderby column should fail
\set ON_ERROR_STOP 0
alter table sparse drop column o;
ERROR:  cannot drop orderby or segmentby column from a hypertable with columnstore enabled
-- a segmentby column cannot be part of composite key
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(sby,value)');
ERROR:  the segmentby column "sby" can not have sparse indexes
-- a segmentby column cannot be part of single col bloom filter
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(sby)');
ERROR:  the segmentby column "sby" can not have sparse indexes
-- an orderby column cannot be part of a single col bloom filter
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(o)');
ERROR:  the orderby column "o" cannot have a bloom sparse index 
-- an orderby column cannot be part of a single col bloom filter
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(boo,o),bloom(o)');
ERROR:  the orderby column "o" cannot have a bloom sparse index 
-- an orderby column cannot be part of a single col bloom filter
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(o),bloom(o,boo)');
ERROR:  the orderby column "o" cannot have a bloom sparse index 
\set ON_ERROR_STOP 1
-- an orderby column _can_ be part of a composite bloom filter
alter table sparse set (
    timescaledb.compress,
    timescaledb.order_by='o',
    timescaledb.segment_by='sby',
    timescaledb.compress_index = 'bloom(big1),bloom(value),bloom(boo,big1),bloom(o,boo)');
NOTICE:  updated compression settings will only apply to future compressions
DROP TABLE IF EXISTS sparse CASCADE;
DROP TABLE IF EXISTS sparse_sister CASCADE;