compress_compbloom_manual_config.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.
---------------------------------------------------------------------
-- Manual config change between chunk compressions
---------------------------------------------------------------------
CREATE TABLE mixed_avail_manual(ts timestamptz, a int, b int, c int, seg int);
SELECT create_hypertable('mixed_avail_manual', by_range('ts', interval '1 day'));
 create_hypertable 
-------------------
 (1,t)

-- Initial config: bloom(a,b)
ALTER TABLE mixed_avail_manual SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom(a,b)'
);
INSERT INTO mixed_avail_manual
SELECT ts, (i % 10), (i % 5), (i % 3), 1
FROM generate_series('2024-01-01'::timestamptz, '2024-01-03', interval '1 hour') ts,
     generate_series(1, 100) i;
-- Compress first chunk with bloom(a,b)
SELECT compress_chunk(c) FROM show_chunks('mixed_avail_manual') c LIMIT 1;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

SELECT COUNT(*) FROM mixed_avail_manual WHERE a = 1 AND b = 2;
 count 
-------
     0

SELECT COUNT(*) FROM mixed_avail_manual WHERE a = 1 AND c = 2;
 count 
-------
   147

SELECT COUNT(*) FROM mixed_avail_manual WHERE b = 1 AND c = 2;
 count 
-------
   294

-- Before config changes
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE a = 1 AND b = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=0.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.c
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.c
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
               Vectorized Filter: ((a = 1) AND (b = 2))
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=0.00 loops=1)
                     Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_7035_a_b, TEST-HASHES::bigint[])
                     Rows Removed by Filter: 2
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.c
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_2_chunk (actual rows=0.00 loops=1)
               Filter: ((a = 1) AND (b = 2))
               Rows Removed by Filter: 2400
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.c
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=0.00 loops=1)
               Filter: ((a = 1) AND (b = 2))
               Rows Removed by Filter: 900

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE a = 1 AND c = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=147.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.b
   ->  Sort (actual rows=48.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.b
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=48.00 loops=1)
               Vectorized Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 1552
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=2.00 loops=1)
   ->  Sort (actual rows=72.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.b
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_2_chunk (actual rows=72.00 loops=1)
               Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 2328
   ->  Sort (actual rows=27.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.b
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=27.00 loops=1)
               Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 873

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE b = 1 AND c = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=294.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.a
   ->  Sort (actual rows=96.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.a
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=96.00 loops=1)
               Vectorized Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 1504
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=2.00 loops=1)
   ->  Sort (actual rows=144.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.a
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_2_chunk (actual rows=144.00 loops=1)
               Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 2256
   ->  Sort (actual rows=54.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.a
         Sort Method: quicksort 
         ->  Seq Scan on _hyper_1_3_chunk (actual rows=54.00 loops=1)
               Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 846

-- Change config: bloom(a,c)
ALTER TABLE mixed_avail_manual SET (
    timescaledb.compress_index = 'bloom(a,c)'
);
NOTICE:  updated compression settings will only apply to future compressions
-- Compress second chunk with bloom(a,c)
SELECT compress_chunk(c) FROM show_chunks('mixed_avail_manual') c OFFSET 1 LIMIT 1;
NOTICE:  chunk "_hyper_1_1_chunk" is already converted to columnstore
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk

-- Change config: bloom(b,c)
ALTER TABLE mixed_avail_manual SET (
    timescaledb.compress_index = 'bloom(b,c)'
);
NOTICE:  updated compression settings will only apply to future compressions
-- Compress third chunk with bloom(b,c)
SELECT compress_chunk(c) FROM show_chunks('mixed_avail_manual') c OFFSET 2;
NOTICE:  chunk "_hyper_1_1_chunk" is already converted to columnstore
NOTICE:  chunk "_hyper_1_2_chunk" is already converted to columnstore
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_3_chunk

SELECT COUNT(*) FROM mixed_avail_manual WHERE a = 1 AND b = 2;
 count 
-------
     0

SELECT COUNT(*) FROM mixed_avail_manual WHERE a = 1 AND c = 2;
 count 
-------
   147

SELECT COUNT(*) FROM mixed_avail_manual WHERE b = 1 AND c = 2;
 count 
-------
   294

-- After config changes
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE a = 1 AND b = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=0.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.c
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.c
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
               Vectorized Filter: ((a = 1) AND (b = 2))
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=0.00 loops=1)
                     Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_7035_a_b, TEST-HASHES::bigint[])
                     Rows Removed by Filter: 2
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.c
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=0.00 loops=1)
               Vectorized Filter: ((a = 1) AND (b = 2))
               Rows Removed by Filter: 2400
               ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
   ->  Sort (actual rows=0.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.c
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=0.00 loops=1)
               Vectorized Filter: ((a = 1) AND (b = 2))
               Rows Removed by Filter: 900
               ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=1.00 loops=1)

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE a = 1 AND c = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=147.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.b
   ->  Sort (actual rows=48.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.b
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=48.00 loops=1)
               Vectorized Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 1552
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=2.00 loops=1)
   ->  Sort (actual rows=72.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.b
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=72.00 loops=1)
               Vectorized Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 2328
               ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
                     Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_e8f0_a_c, TEST-HASHES::bigint[])
   ->  Sort (actual rows=27.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.b
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=27.00 loops=1)
               Vectorized Filter: ((a = 1) AND (c = 2))
               Rows Removed by Filter: 873
               ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=1.00 loops=1)

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_manual WHERE b = 1 AND c = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
 Custom Scan (ChunkAppend) on mixed_avail_manual (actual rows=294.00 loops=1)
   Order: mixed_avail_manual.ts, mixed_avail_manual.a
   ->  Sort (actual rows=96.00 loops=1)
         Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.a
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=96.00 loops=1)
               Vectorized Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 1504
               ->  Seq Scan on compress_hyper_2_4_chunk (actual rows=2.00 loops=1)
   ->  Sort (actual rows=144.00 loops=1)
         Sort Key: _hyper_1_2_chunk.ts, _hyper_1_2_chunk.a
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=144.00 loops=1)
               Vectorized Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 2256
               ->  Seq Scan on compress_hyper_2_5_chunk (actual rows=3.00 loops=1)
   ->  Sort (actual rows=54.00 loops=1)
         Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.a
         Sort Method: quicksort 
         ->  Custom Scan (ColumnarScan) on _hyper_1_3_chunk (actual rows=54.00 loops=1)
               Vectorized Filter: ((b = 1) AND (c = 2))
               Rows Removed by Filter: 846
               ->  Seq Scan on compress_hyper_2_6_chunk (actual rows=1.00 loops=1)
                     Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_7ba1_b_c, TEST-HASHES::bigint[])

DROP TABLE IF EXISTS mixed_avail_manual CASCADE;