rebuild_sparse_index.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.
-- Test rebuild_sparse_index function
CREATE OR REPLACE FUNCTION show_compressed_columns(chunk_regclass regclass)
RETURNS TABLE(attname name, typname name) AS $$
    SELECT a.attname, t.typname
    FROM pg_attribute a
    JOIN pg_type t ON a.atttypid = t.oid
    WHERE a.attrelid = (
        SELECT cs.compress_relid
        FROM _timescaledb_catalog.compression_settings cs
        WHERE cs.relid = chunk_regclass
    )
    AND a.attname LIKE '_ts_meta_%'
    AND a.attnum > 0
    AND NOT a.attisdropped
    ORDER BY a.attname;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION show_chunk_index(chunk_regclass regclass)
RETURNS jsonb AS $$
    SELECT cs.index
    FROM _timescaledb_catalog.compression_settings cs
    WHERE cs.relid = chunk_regclass;
$$ LANGUAGE sql;
CREATE TABLE rsi_test(
    time timestamptz NOT NULL,
    device int,
    sensor int,
    temp float8,
    val int
);
SELECT create_hypertable('rsi_test', 'time', chunk_time_interval => INTERVAL '1 day');
   create_hypertable   
-----------------------
 (1,public,rsi_test,t)

INSERT INTO rsi_test
SELECT t, (i % 5) + 1, (i % 3) + 1, random() * 100, (i * 7) % 13
FROM generate_series('2024-01-01 01:00'::timestamptz, '2024-01-01 23:59', '1 minute') t,
     generate_series(1, 3) i;
SELECT relid::text AS chunk1
FROM _timescaledb_catalog.chunk
WHERE hypertable_id = (SELECT id FROM _timescaledb_catalog.hypertable
                        WHERE table_name = 'rsi_test')
ORDER BY id LIMIT 1 \gset
-- Test 1: initial compression with bloom + minmax
ALTER TABLE rsi_test SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("device"), minmax("temp")'
);
SELECT compress_chunk(:'chunk1');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT show_chunk_index(:'chunk1'::regclass);
                                                                                                                show_chunk_index                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"type": "bloom", "column": "device", "source": "config"}, {"type": "minmax", "column": "temp", "source": "config"}, {"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]

SELECT * FROM show_compressed_columns(:'chunk1'::regclass);
          attname          |   typname   
---------------------------+-------------
 _ts_meta_count            | int4
 _ts_meta_max_1            | timestamptz
 _ts_meta_min_1            | timestamptz
 regress-test-bloom_device | bloom1
 _ts_meta_v2_first_time    | timestamptz
 _ts_meta_v2_last_time     | timestamptz
 _ts_meta_v2_max_temp      | float8
 _ts_meta_v2_min_temp      | float8

-- Test 2: settings match, should skip
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);
NOTICE:  sparse index settings for chunk "_timescaledb_internal._hyper_1_1_chunk" already match hypertable, skipping (use force => true to override)
 rebuild_sparse_index 
----------------------
 

-- Test 3: swap bloom("device") for bloom("device","sensor"), keep minmax("temp")
ALTER TABLE rsi_test SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("device","sensor"), minmax("temp")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT show_chunk_index(:'chunk1'::regclass);
                                                                                                                show_chunk_index                                                                                                                
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"type": "bloom", "column": "device", "source": "config"}, {"type": "minmax", "column": "temp", "source": "config"}, {"type": "minmax", "column": "time", "source": "orderby"}, {"type": "firstlast", "column": "time", "source": "orderby"}]

SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'chunk1'::regclass);
                attname                |   typname   
---------------------------------------+-------------
 _ts_meta_count                        | int4
 _ts_meta_max_1                        | timestamptz
 _ts_meta_min_1                        | timestamptz
 regress-test-bloom_2b4d_device_sensor | bloom1
 _ts_meta_v2_first_time                | timestamptz
 _ts_meta_v2_last_time                 | timestamptz
 _ts_meta_v2_max_temp                  | float8
 _ts_meta_v2_min_temp                  | float8

-- Test 4: remove all sparse indexes
ALTER TABLE rsi_test SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = ''
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'chunk1'::regclass);
        attname         |   typname   
------------------------+-------------
 _ts_meta_count         | int4
 _ts_meta_max_1         | timestamptz
 _ts_meta_min_1         | timestamptz
 _ts_meta_v2_first_time | timestamptz
 _ts_meta_v2_last_time  | timestamptz

-- Test 5: add back bloom + minmax + composite bloom
ALTER TABLE rsi_test SET (
    timescaledb.compress,
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("device"), minmax("temp"), bloom("device","val")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'chunk1'::regclass);
              attname               |   typname   
------------------------------------+-------------
 _ts_meta_count                     | int4
 _ts_meta_max_1                     | timestamptz
 _ts_meta_min_1                     | timestamptz
 regress-test-bloom_device          | bloom1
 regress-test-bloom_e076_device_val | bloom1
 _ts_meta_v2_first_time             | timestamptz
 _ts_meta_v2_last_time              | timestamptz
 _ts_meta_v2_max_temp               | float8
 _ts_meta_v2_min_temp               | float8

-- Test 6: force rebuild when settings already match
SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass, force => true);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'chunk1'::regclass);
              attname               |   typname   
------------------------------------+-------------
 _ts_meta_count                     | int4
 _ts_meta_max_1                     | timestamptz
 _ts_meta_min_1                     | timestamptz
 regress-test-bloom_device          | bloom1
 regress-test-bloom_e076_device_val | bloom1
 _ts_meta_v2_first_time             | timestamptz
 _ts_meta_v2_last_time              | timestamptz
 _ts_meta_v2_max_temp               | float8
 _ts_meta_v2_min_temp               | float8

-- Test 7: uncompressed chunk, should skip
SELECT decompress_chunk(:'chunk1');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT _timescaledb_functions.rebuild_sparse_index(:'chunk1'::regclass);
NOTICE:  chunk "_timescaledb_internal._hyper_1_1_chunk" is uncompressed or frozen, skipping
 rebuild_sparse_index 
----------------------
 

DROP TABLE rsi_test CASCADE;
-- Data integrity tests: verify rebuilt sparse index values match the
-- original compression output (file diff) and that bloom filters
-- correctly identify present and absent values.
\set TEST_BASE_NAME rebuild_sparse_index
CREATE TABLE rsi_integrity(
    time timestamptz NOT NULL,
    device text NOT NULL,
    temp float8,
    humidity int,
    label text,
    reading uuid
);
SELECT create_hypertable('rsi_integrity', 'time', chunk_time_interval => INTERVAL '1 day');
     create_hypertable      
----------------------------
 (2,public,rsi_integrity,t)

-- NULLs at row 5000/5001 to stress firstlast NULL handling at batch boundaries
SELECT setseed(0.42);
 setseed 
---------
 

INSERT INTO rsi_integrity
SELECT
    '2024-01-01'::timestamptz + ((row_num - 1) * interval '10 seconds'),
    'd' || ((row_num % 5) + 1),
    CASE WHEN row_num % 5000 IN (0, 1) THEN NULL ELSE random() * 100 END,
    CASE WHEN row_num % 5000 IN (0, 1) THEN NULL ELSE (random() * 200)::int END,
    CASE WHEN row_num % 5000 IN (0, 1) THEN NULL ELSE 'label_' || (row_num % 7) END,
    CASE WHEN row_num % 5000 IN (0, 1) THEN NULL ELSE gen_random_uuid() END
FROM generate_series(1, 15000) row_num;
SELECT relid::text AS ichunk1
FROM _timescaledb_catalog.chunk
WHERE hypertable_id = (SELECT id FROM _timescaledb_catalog.hypertable WHERE table_name = 'rsi_integrity')
ORDER BY id LIMIT 1 \gset
SELECT relid::text AS ichunk2
FROM _timescaledb_catalog.chunk
WHERE hypertable_id = (SELECT id FROM _timescaledb_catalog.hypertable WHERE table_name = 'rsi_integrity')
ORDER BY id LIMIT 1 OFFSET 1 \gset
SELECT format('%s/results/%s_original.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') AS "ORIGINAL",
       format('%s/results/%s_rebuilt.out', :'TEST_OUTPUT_DIR', :'TEST_BASE_NAME') AS "REBUILT"
\gset
-- Dump _ts_meta_v2_* columns sorted by name (avoids column reorder after drop+add)
CREATE OR REPLACE FUNCTION dump_sparse_metadata(chunk_regclass regclass)
RETURNS SETOF text AS $$
DECLARE
    compressed_relid oid;
    col_list text;
BEGIN
    SELECT cs.compress_relid INTO compressed_relid
    FROM _timescaledb_catalog.compression_settings cs
    WHERE cs.relid = chunk_regclass;

    SELECT string_agg(quote_ident(a.attname), ', ' ORDER BY a.attname) INTO col_list
    FROM pg_attribute a
    WHERE a.attrelid = compressed_relid
      AND a.attname LIKE '_ts_meta_v2_%'
      AND a.attnum > 0
      AND NOT a.attisdropped;

    RETURN QUERY EXECUTE format(
        'SELECT row(%s)::text FROM %s ORDER BY _ts_meta_min_1',
        col_list, compressed_relid::regclass);
END;
$$ LANGUAGE plpgsql;
-- Test 8: minmax integrity (compress, save, drop+rebuild, save, diff)
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'minmax("temp"), minmax("humidity"), minmax("label"), minmax("reading")'
);
SELECT compress_chunk(:'ichunk1');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT compress_chunk(:'ichunk2');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

SELECT comp_ns.nspname AS ic_schema1, comp_cl.relname AS ic_table1
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk1'::regclass \gset
SELECT comp_ns.nspname AS ic_schema2, comp_cl.relname AS ic_table2
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk2'::regclass \gset
\o :ORIGINAL
SELECT * FROM dump_sparse_metadata(:'ichunk1'::regclass);
SELECT * FROM dump_sparse_metadata(:'ichunk2'::regclass);
\o
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = ''
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk2'::regclass);
 rebuild_sparse_index 
----------------------
 

ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'minmax("temp"), minmax("humidity"), minmax("label"), minmax("reading")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk2'::regclass);
 rebuild_sparse_index 
----------------------
 

\o :REBUILT
SELECT * FROM dump_sparse_metadata(:'ichunk1'::regclass);
SELECT * FROM dump_sparse_metadata(:'ichunk2'::regclass);
\o
SELECT format('\! diff -u --label "minmax original" --label "minmax rebuilt" %s %s',
              :'ORIGINAL', :'REBUILT') AS "DIFF_CMD" \gset
:DIFF_CMD
SELECT decompress_chunk(:'ichunk1');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT decompress_chunk(:'ichunk2');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

-- Test 9: firstlast integrity (compress, save, drop+rebuild, save, diff)
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'firstlast("temp"), firstlast("humidity"), firstlast("label"), firstlast("reading")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(:'ichunk1');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT compress_chunk(:'ichunk2');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

-- re-fetch compressed chunk names after recompress
SELECT comp_ns.nspname AS ic_schema1, comp_cl.relname AS ic_table1
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk1'::regclass \gset
SELECT comp_ns.nspname AS ic_schema2, comp_cl.relname AS ic_table2
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk2'::regclass \gset
\o :ORIGINAL
SELECT * FROM dump_sparse_metadata(:'ichunk1'::regclass);
SELECT * FROM dump_sparse_metadata(:'ichunk2'::regclass);
\o
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = ''
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk2'::regclass);
 rebuild_sparse_index 
----------------------
 

ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'firstlast("temp"), firstlast("humidity"), firstlast("label"), firstlast("reading")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk2'::regclass);
 rebuild_sparse_index 
----------------------
 

\o :REBUILT
SELECT * FROM dump_sparse_metadata(:'ichunk1'::regclass);
SELECT * FROM dump_sparse_metadata(:'ichunk2'::regclass);
\o
SELECT format('\! diff -u --label "firstlast original" --label "firstlast rebuilt" %s %s',
              :'ORIGINAL', :'REBUILT') AS "DIFF_CMD" \gset
:DIFF_CMD
SELECT decompress_chunk(:'ichunk1');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT decompress_chunk(:'ichunk2');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_2_4_chunk

-- Test 10: single-column bloom integrity (drop, rebuild, check contains)
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("humidity"), bloom("label")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(:'ichunk1');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT comp_ns.nspname AS ic_schema1, comp_cl.relname AS ic_table1
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk1'::regclass \gset
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = ''
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'ichunk1'::regclass);
        attname         |   typname   
------------------------+-------------
 _ts_meta_count         | int4
 _ts_meta_max_1         | timestamptz
 _ts_meta_min_1         | timestamptz
 _ts_meta_v2_first_time | timestamptz
 _ts_meta_v2_last_time  | timestamptz

ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("humidity"), bloom("label")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

-- look up bloom column names (prefix varies between test/production builds)
SELECT a.attname AS bloom_humidity
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = (:'ic_schema1' || '.' || :'ic_table1')::regclass
  AND t.typname = 'bloom1' AND a.attname LIKE '%humidity'
  AND NOT a.attisdropped LIMIT 1 \gset
SELECT a.attname AS bloom_label
FROM pg_attribute a
JOIN pg_type t ON a.atttypid = t.oid
WHERE a.attrelid = (:'ic_schema1' || '.' || :'ic_table1')::regclass
  AND t.typname = 'bloom1' AND a.attname LIKE '%label'
  AND NOT a.attisdropped LIMIT 1 \gset
-- present values (expect true)
SELECT _timescaledb_functions.bloom1_contains(:"bloom_humidity", 42) AS val_42,
       _timescaledb_functions.bloom1_contains(:"bloom_humidity", 100) AS val_100,
       _timescaledb_functions.bloom1_contains(:"bloom_humidity", 7) AS val_7
FROM :ic_schema1.:ic_table1 WHERE device = 'd1' LIMIT 1;
 val_42 | val_100 | val_7 
--------+---------+-------
 t      | t       | t

-- absent values (expect false)
SELECT _timescaledb_functions.bloom1_contains(:"bloom_humidity", -999) AS val_neg999,
       _timescaledb_functions.bloom1_contains(:"bloom_humidity", 999) AS val_999,
       _timescaledb_functions.bloom1_contains(:"bloom_humidity", 12345) AS val_12345
FROM :ic_schema1.:ic_table1 WHERE device = 'd1' LIMIT 1;
 val_neg999 | val_999 | val_12345 
------------+---------+-----------
 f          | f       | f

-- present labels (expect true)
SELECT _timescaledb_functions.bloom1_contains(:"bloom_label", 'label_0'::text) AS label_0,
       _timescaledb_functions.bloom1_contains(:"bloom_label", 'label_3'::text) AS label_3,
       _timescaledb_functions.bloom1_contains(:"bloom_label", 'label_6'::text) AS label_6
FROM :ic_schema1.:ic_table1 WHERE device = 'd2' LIMIT 1;
 label_0 | label_3 | label_6 
---------+---------+---------
 t       | t       | t

-- absent labels (expect false)
SELECT _timescaledb_functions.bloom1_contains(:"bloom_label", 'nonexistent'::text) AS nope1,
       _timescaledb_functions.bloom1_contains(:"bloom_label", 'zzz_never'::text) AS nope2,
       _timescaledb_functions.bloom1_contains(:"bloom_label", 'NOPE'::text) AS nope3
FROM :ic_schema1.:ic_table1 WHERE device = 'd2' LIMIT 1;
 nope1 | nope2 | nope3 
-------+-------+-------
 f     | f     | f

-- Sparse index integrity: the (device, first_time, last_time) btree must be
-- able to find every batch for every device after rebuild_sparse_index, not
-- just the ones whose metadata rewrite happened to be a HOT update. Forced
-- index/bitmap scans must return the same per-device batch counts as an
-- unindexed scan.
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT device, count(*) FROM :ic_schema1.:ic_table1 GROUP BY device ORDER BY device;
 device | count 
--------+-------
 d1     |     2
 d2     |     2
 d3     |     2
 d4     |     2
 d5     |     2

RESET enable_indexscan;
RESET enable_bitmapscan;
SET enable_seqscan = off;
SELECT device, count(*) FROM :ic_schema1.:ic_table1 GROUP BY device ORDER BY device;
 device | count 
--------+-------
 d1     |     2
 d2     |     2
 d3     |     2
 d4     |     2
 d5     |     2

RESET enable_seqscan;
SELECT decompress_chunk(:'ichunk1');
            decompress_chunk            
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

-- Test 11: composite bloom integrity (drop, rebuild, verify via explain)
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("humidity","label")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT compress_chunk(:'ichunk1');
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_2_3_chunk

SELECT comp_ns.nspname AS ic_schema1, comp_cl.relname AS ic_table1
FROM _timescaledb_catalog.compression_settings cs
JOIN pg_class comp_cl ON cs.compress_relid = comp_cl.oid
JOIN pg_namespace comp_ns ON comp_cl.relnamespace = comp_ns.oid
WHERE cs.relid = :'ichunk1'::regclass \gset
ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = ''
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'ichunk1'::regclass);
        attname         |   typname   
------------------------+-------------
 _ts_meta_count         | int4
 _ts_meta_max_1         | timestamptz
 _ts_meta_min_1         | timestamptz
 _ts_meta_v2_first_time | timestamptz
 _ts_meta_v2_last_time  | timestamptz

ALTER TABLE rsi_integrity SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'device',
    timescaledb.compress_orderby = 'time',
    timescaledb.compress_index = 'bloom("humidity","label")'
);
NOTICE:  updated compression settings will only apply to future compressions
SELECT _timescaledb_functions.rebuild_sparse_index(:'ichunk1'::regclass);
 rebuild_sparse_index 
----------------------
 

SELECT * FROM show_compressed_columns(:'ichunk1'::regclass);
                attname                 |   typname   
----------------------------------------+-------------
 _ts_meta_count                         | int4
 _ts_meta_max_1                         | timestamptz
 _ts_meta_min_1                         | timestamptz
 regress-test-bloom_577c_humidity_label | bloom1
 _ts_meta_v2_first_time                 | timestamptz
 _ts_meta_v2_last_time                  | timestamptz

-- composite bloom should appear in the plan filter
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM rsi_integrity WHERE device = 'd1' AND humidity = 42 AND label = 'label_0';
--- QUERY PLAN ---
 Append (actual rows=0.00 loops=1)
   ->  Custom Scan (ColumnarScan) on _hyper_2_3_chunk (actual rows=0.00 loops=1)
         Vectorized Filter: ((humidity = 42) AND (label = 'label_0'::text))
         ->  Index Scan using _hyper_2_3_chunk_compressed_device__ts_meta_v2_first_time___idx on _hyper_2_3_chunk_compressed (actual rows=0.00 loops=1)
               Index Cond: (device = 'd1'::text)
               Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_577c_humidity_label, TEST-HASHES::bigint[])
               Rows Removed by Filter: 2
   ->  Seq Scan on _hyper_2_4_chunk (actual rows=0.00 loops=1)
         Filter: ((device = 'd1'::text) AND (humidity = 42) AND (label = 'label_0'::text))
         Rows Removed by Filter: 8640
   ->  Seq Scan on _hyper_2_5_chunk (actual rows=0.00 loops=1)
         Filter: ((device = 'd1'::text) AND (humidity = 42) AND (label = 'label_0'::text))
         Rows Removed by Filter: 600

DROP TABLE rsi_integrity CASCADE;
DROP FUNCTION show_compressed_columns(regclass);
DROP FUNCTION show_chunk_index(regclass);
DROP FUNCTION dump_sparse_metadata(regclass);