compress_composite_bloom_debug.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.
\c :TEST_DBNAME :ROLE_SUPERUSER
-----------------------------------------------------------
-- Debug Function Setup
-----------------------------------------------------------
CREATE OR REPLACE FUNCTION ts_bloom1_composite_debug_hash(
    composite_value anyelement
) RETURNS int8
AS :TSL_MODULE_PATHNAME, 'ts_bloom1_composite_debug_hash'
LANGUAGE C IMMUTABLE PARALLEL SAFE;
-----------------------------------------------------------
-- Hash Signature Stability Tests
-----------------------------------------------------------
-- Binary compatibility tests
-- INT4 + INT4
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 2::int4)) AS hash_1_2;
       hash_1_2       
----------------------
 -8269931708899725482

SELECT ts_bloom1_composite_debug_hash(ROW(2::int4, 1::int4)) AS hash_2_1;
       hash_2_1       
----------------------
 -6811200433784778399

-- Order matters
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 1::int4)) AS hash_1_1;
      hash_1_1       
---------------------
 4901182463118210331

SELECT ts_bloom1_composite_debug_hash(ROW(100::int4, 200::int4)) AS hash_100_200;
    hash_100_200     
---------------------
 -985178163871232840

-- NULL handling
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, NULL::int4)) AS hash_1_null;
     hash_1_null      
----------------------
 -4087059175533773721

SELECT ts_bloom1_composite_debug_hash(ROW(NULL::int4, 1::int4)) AS hash_null_1;
     hash_null_1     
---------------------
 3190298981888668881

-- NULL position matters
SELECT ts_bloom1_composite_debug_hash(ROW(NULL::int4, NULL::int4)) AS hash_null_null;
    hash_null_null    
----------------------
 -6642147184848171498

-- Deterministic
-- INT4 + TEXT
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 'hello'::text)) AS hash_int_text;
    hash_int_text    
---------------------
 1882521017801179394

SELECT ts_bloom1_composite_debug_hash(ROW('hello'::text, 1::int4)) AS hash_text_int;
    hash_text_int    
---------------------
 7678076970108472495

-- Type order matters
-- TEXT + TEXT
SELECT ts_bloom1_composite_debug_hash(ROW('hello'::text, 'world'::text)) AS hash_hello_world;
   hash_hello_world   
----------------------
 -3643056055927828098

SELECT ts_bloom1_composite_debug_hash(ROW('world'::text, 'hello'::text)) AS hash_world_hello;
  hash_world_hello   
---------------------
 2877725537500410663

-- INT8 + INT8
SELECT ts_bloom1_composite_debug_hash(ROW(1::int8, 2::int8)) AS hash_bigint_1_2;
   hash_bigint_1_2    
----------------------
 -8269931708899725482

-- FLOAT8 + FLOAT8
SELECT ts_bloom1_composite_debug_hash(ROW(3.14::float8, 2.71::float8)) AS hash_pi_e;
      hash_pi_e      
---------------------
 1298558980710853543

-- DATE + INT4
SELECT ts_bloom1_composite_debug_hash(ROW('2025-01-01'::date, 123::int4)) AS hash_date_int;
    hash_date_int    
---------------------
 1942430286969053094

-- TIMESTAMPTZ + INT4
SELECT ts_bloom1_composite_debug_hash(ROW('2025-01-01 12:00:00+00'::timestamptz, 456::int4)) AS hash_ts_int;
     hash_ts_int     
---------------------
 5390614616176477665

-- UUID + INT4
SELECT ts_bloom1_composite_debug_hash(ROW('c9757a73-7632-462e-bcfa-d5d9659e498f'::uuid, 789::int4)) AS hash_uuid_int;
    hash_uuid_int    
---------------------
 -606148817205698442

-- Triples
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 'test'::text, 3.14::float8)) AS hash_triple_1;
    hash_triple_1     
----------------------
 -6185554987398458301

SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 'test'::text, 2.71::float8)) AS hash_triple_2;
   hash_triple_2    
--------------------
 768561903953156722

-- Triples with NULL
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, NULL::text, 3.14::float8)) AS hash_triple_null_middle;
 hash_triple_null_middle 
-------------------------
    -3234125659019344963

-----------------------------------------------------------
-- Actual Filtering Tests - Basic Setup
-----------------------------------------------------------
-- Create table with known data distribution
CREATE TABLE composite_filter_test(
    ts timestamptz NOT NULL,
    device_id int,
    sensor_type text,
    value float,
    segmentby_col int
);
SELECT create_hypertable('composite_filter_test', by_range('ts'));
 create_hypertable 
-------------------
 (1,t)

ALTER TABLE composite_filter_test SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'segmentby_col',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom(device_id,sensor_type)'
);
-- Insert data with DISTINCT patterns per segment
-- Segment 1: device_id=1, sensor_type='temp' ONLY
INSERT INTO composite_filter_test
SELECT '2024-01-01'::timestamptz + i * interval '1 minute',
       1, 'temp', random() * 100, 1
FROM generate_series(1, 1000) i;
-- Segment 2: device_id=2, sensor_type='humidity' ONLY
INSERT INTO composite_filter_test
SELECT '2024-01-01'::timestamptz + i * interval '1 minute',
       2, 'humidity', random() * 100, 2
FROM generate_series(1, 1000) i;
-- Segment 3: device_id=3, sensor_type='pressure' ONLY
INSERT INTO composite_filter_test
SELECT '2024-01-01'::timestamptz + i * interval '1 minute',
       3, 'pressure', random() * 100, 3
FROM generate_series(1, 1000) i;
-- Segment 4: Mixed data (multiple combinations)
INSERT INTO composite_filter_test
SELECT '2024-01-01'::timestamptz + i * interval '1 minute',
       (i % 3) + 1,
       CASE (i % 3) WHEN 0 THEN 'temp' WHEN 1 THEN 'humidity' ELSE 'pressure' END,
       random() * 100,
       4
FROM generate_series(1, 900) i;
SELECT compress_chunk(c) FROM show_chunks('composite_filter_test') c;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_1_1_chunk

VACUUM ANALYZE composite_filter_test;
-- Create uncompressed reference for false negative testing
CREATE TABLE composite_filter_ref AS SELECT * FROM composite_filter_test;
-----------------------------------------------------------
-- Verify Composite Bloom Column Exists
-----------------------------------------------------------
SELECT cc.schema_name || '.' || cc.table_name AS chunk
FROM _timescaledb_catalog.chunk uc, timescaledb_information.chunks tc, _timescaledb_catalog.chunk cc
WHERE uc.table_name = tc.chunk_name
  AND cc.id = uc.compressed_chunk_id
  AND tc.hypertable_name = 'composite_filter_test'
  AND tc.is_compressed
ORDER BY cc.table_name
LIMIT 1
\gset
\echo 'Chunk: ' :chunk
Chunk:  _timescaledb_internal.compress_hyper_2_2_chunk
SELECT attname AS composite_bloom_col
FROM pg_attribute
WHERE attrelid = :'chunk'::regclass
  AND attname LIKE '%device_id%sensor_type%'
  LIMIT 1
\gset
\echo 'Composite bloom column: ' :composite_bloom_col
Composite bloom column:  regress-test-bloom_d3fa_device_id_sensor_type
-----------------------------------------------------------
-- Filtering Effectiveness Tests
-----------------------------------------------------------
-- Query for data in Segment 1 only
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM composite_filter_test WHERE device_id = 1 AND sensor_type = 'temp';
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1300.00 loops=1)
   Vectorized Filter: ((device_id = 1) AND (sensor_type = 'temp'::text))
   Rows Removed by Filter: 600
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=2.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_d3fa_device_id_sensor_type, TEST-HASHES::bigint[])
         Rows Removed by Filter: 2

-- Expected behavior:
-- - Filter should show: bloom1_contains(composite_bloom, ROW(1, 'temp'::text))
-- - Should scan ~1000-1100 rows (segment 1 + maybe some from segment 4 due to false positives)
-- - Should NOT scan segments 2 and 3 (different device_id/sensor_type)
-- - Pruning: ~50-60% of segments
-- Query for data in Segment 2 only
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM composite_filter_test WHERE device_id = 2 AND sensor_type = 'humidity';
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1300.00 loops=1)
   Vectorized Filter: ((device_id = 2) AND (sensor_type = 'humidity'::text))
   Rows Removed by Filter: 600
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=2.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_d3fa_device_id_sensor_type, TEST-HASHES::bigint[])
         Rows Removed by Filter: 2

-- Expected: Similar pruning as Test 5.1
-- Query for non-existent combination
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM composite_filter_test WHERE device_id = 5 AND sensor_type = 'temp';
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
   Vectorized Filter: ((device_id = 5) AND (sensor_type = 'temp'::text))
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_d3fa_device_id_sensor_type, TEST-HASHES::bigint[])
         Rows Removed by Filter: 4

-- Expected:
-- - Should scan 0 or very few rows (bloom filter prunes all segments)
-- - Actual rows returned: 0
-- Non-existent pairing (device_id=1, sensor_type=humidity)
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM composite_filter_test WHERE device_id = 1 AND sensor_type = 'humidity';
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
   Vectorized Filter: ((device_id = 1) AND (sensor_type = 'humidity'::text))
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=0.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_d3fa_device_id_sensor_type, TEST-HASHES::bigint[])
         Rows Removed by Filter: 4

-- Expected:
-- Segment 4 data: (i%3)+1 paired with sensor via CASE
-- Pairs: (1,temp), (2,humidity), (3,pressure)
-- Query (1,humidity) doesn't exist → should return 0 rows
-----------------------------------------------------------
-- False Negative Prevention Tests
-----------------------------------------------------------
-- Test all expected combinations and verify counts match reference
WITH test_cases AS (
    SELECT device_id, sensor_type
    FROM (VALUES
        (1, 'temp'),        -- Segment 1: ~1000 rows
        (2, 'humidity'),    -- Segment 2: ~1000 rows
        (3, 'pressure'),    -- Segment 3: ~1000 rows
        (1, 'humidity'),    -- Segment 4: ~300 rows
        (2, 'pressure'),    -- Segment 4: ~300 rows
        (3, 'temp'),        -- Segment 4: ~300 rows
        (4, 'temp'),        -- Non-existent: 0 rows
        (1, 'pressure'),    -- Segment 4: ~300 rows
        (2, 'temp')         -- Segment 4: ~300 rows
    ) AS t(device_id, sensor_type)
)
SELECT
    device_id,
    sensor_type,
    (SELECT COUNT(*) FROM composite_filter_test
     WHERE device_id = tc.device_id AND sensor_type = tc.sensor_type) AS compressed_count,
    (SELECT COUNT(*) FROM composite_filter_ref
     WHERE device_id = tc.device_id AND sensor_type = tc.sensor_type) AS reference_count,
    (SELECT COUNT(*) FROM composite_filter_test
     WHERE device_id = tc.device_id AND sensor_type = tc.sensor_type) =
    (SELECT COUNT(*) FROM composite_filter_ref
     WHERE device_id = tc.device_id AND sensor_type = tc.sensor_type) AS match
FROM test_cases tc
ORDER BY device_id, sensor_type;
 device_id | sensor_type | compressed_count | reference_count | match 
-----------+-------------+------------------+-----------------+-------
         1 | humidity    |                0 |               0 | t
         1 | pressure    |                0 |               0 | t
         1 | temp        |             1300 |            1300 | t
         2 | humidity    |             1300 |            1300 | t
         2 | pressure    |                0 |               0 | t
         2 | temp        |                0 |               0 | t
         3 | pressure    |             1300 |            1300 | t
         3 | temp        |                0 |               0 | t
         4 | temp        |                0 |               0 | t

-- All match values should be TRUE
-----------------------------------------------------------
-- NULL Handling Tests
-----------------------------------------------------------
CREATE TABLE null_test(
    ts int,
    a int,
    b text,
    seg int
);
SELECT create_hypertable('null_test', 'ts');
   create_hypertable    
------------------------
 (3,public,null_test,t)

ALTER TABLE null_test SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom(a,b)'
);
-- Insert various NULL combinations
INSERT INTO null_test VALUES (1, NULL, NULL, 1);
INSERT INTO null_test VALUES (2, NULL, 'test', 1);
INSERT INTO null_test VALUES (3, 1, NULL, 1);
INSERT INTO null_test VALUES (4, 2, 'test', 1);
INSERT INTO null_test VALUES (5, NULL, 'test', 1);
SELECT compress_chunk(c) FROM show_chunks('null_test') c;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_3_3_chunk

-- Test NULL queries
SELECT COUNT(*) FROM null_test WHERE a IS NULL AND b IS NULL;
 count 
-------
     1

SELECT COUNT(*) FROM null_test WHERE a IS NULL AND b = 'test';
 count 
-------
     2

SELECT COUNT(*) FROM null_test WHERE a = 1 AND b IS NULL;
 count 
-------
     1

SELECT COUNT(*) FROM null_test WHERE a = 2 AND b = 'test';
 count 
-------
     1

-- Verify no false negatives
CREATE TABLE null_test_ref AS SELECT * FROM null_test;
WITH test_cases AS (
    SELECT a, b FROM (VALUES
        (NULL, NULL),
        (NULL, 'test'),
        (1, NULL),
        (2, 'test')
    ) AS t(a, b)
)
SELECT
    COALESCE(a::text, 'NULL') AS a,
    COALESCE(b, 'NULL') AS b,
    (SELECT COUNT(*) FROM null_test WHERE (a IS NOT DISTINCT FROM tc.a) AND (b IS NOT DISTINCT FROM tc.b)) AS compressed,
    (SELECT COUNT(*) FROM null_test_ref WHERE (a IS NOT DISTINCT FROM tc.a) AND (b IS NOT DISTINCT FROM tc.b)) AS reference,
    (SELECT COUNT(*) FROM null_test WHERE (a IS NOT DISTINCT FROM tc.a) AND (b IS NOT DISTINCT FROM tc.b)) =
    (SELECT COUNT(*) FROM null_test_ref WHERE (a IS NOT DISTINCT FROM tc.a) AND (b IS NOT DISTINCT FROM tc.b)) AS match
FROM test_cases tc;
  a   |  b   | compressed | reference | match 
------+------+------------+-----------+-------
 NULL | NULL |          1 |         1 | t
 NULL | test |          2 |         2 | t
 1    | NULL |          1 |         1 | t
 2    | test |          1 |         1 | t

-- All match values should be TRUE
-----------------------------------------------------------
-- More Type Pairs
-----------------------------------------------------------
-- INT2 + INT2 (smallint)
SELECT ts_bloom1_composite_debug_hash(ROW(1::int2, 2::int2));
 ts_bloom1_composite_debug_hash 
--------------------------------
           -8269931708899725482

-- INT4 + INT8 (mixed integer sizes)
SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 2::int8));
 ts_bloom1_composite_debug_hash 
--------------------------------
           -8269931708899725482

-- FLOAT4 + FLOAT8 (mixed float sizes)
SELECT ts_bloom1_composite_debug_hash(ROW(1.5::float4, 2.5::float8));
 ts_bloom1_composite_debug_hash 
--------------------------------
           -8062175379131696515

-- VARCHAR + TEXT (string types)
SELECT ts_bloom1_composite_debug_hash(ROW('hello'::varchar, 'world'::text));
 ts_bloom1_composite_debug_hash 
--------------------------------
           -3643056055927828098

-- TIMESTAMP + TIMESTAMPTZ
SELECT ts_bloom1_composite_debug_hash(ROW('2025-01-01 12:00:00'::timestamp, '2025-01-01 12:00:00+00'::timestamptz));
 ts_bloom1_composite_debug_hash 
--------------------------------
            3614883595137105983

-- BOOL + INT4
SELECT ts_bloom1_composite_debug_hash(ROW(true::bool, 1::int4));
 ts_bloom1_composite_debug_hash 
--------------------------------
            1292015507012526368

-----------------------------------------------------------
-- 3 or more fields
-----------------------------------------------------------
SELECT ts_bloom1_composite_debug_hash(ROW(42::int4, 'answer'::text, 3.14159::float8)) AS hash_triple;
     hash_triple      
----------------------
 -8542448666174421461

SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 2::int4, 3::int4, 4::int4)) AS hash_quad;
      hash_quad       
----------------------
 -1407394388838765122

SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 2::int4, 3::int4, 4::int4, 5::int4)) AS hash_quint;
     hash_quint      
---------------------
 4133756361080909164

SELECT ts_bloom1_composite_debug_hash(ROW(1::int4, 2::int4, 3::int4, 4::int4, 5::int4, 6::int4, 7::int4, 8::int4)) AS hash_octet;
     hash_octet      
---------------------
 4290262262381336723

-----------------------------------------------------------
-- Triple Column Tests
-----------------------------------------------------------
CREATE TABLE test_triple(ts int, a int, b text, c float8, seg int);
SELECT create_hypertable('test_triple', 'ts');
    create_hypertable     
--------------------------
 (5,public,test_triple,t)

ALTER TABLE test_triple SET (
    timescaledb.compress,
    timescaledb.compress_segmentby = 'seg',
    timescaledb.compress_orderby = 'ts',
    timescaledb.compress_index = 'bloom(a,b,c)'
);
INSERT INTO test_triple
SELECT ts,
       ts % 5,
       CASE ts % 3 WHEN 0 THEN 'x' WHEN 1 THEN 'y' ELSE 'z' END,
       (ts % 4)::float8,
       ts % 2
FROM generate_series(1, 1000) ts;
SELECT compress_chunk(c) FROM show_chunks('test_triple') c;
             compress_chunk             
----------------------------------------
 _timescaledb_internal._hyper_5_5_chunk

-- Test various triple combinations
SELECT COUNT(*) FROM test_triple WHERE a = 1 AND b = 'x' AND c = 2.0;
 count 
-------
    17

SELECT COUNT(*) FROM test_triple WHERE a = 2 AND b = 'y' AND c = 1.0;
 count 
-------
    17

CREATE TABLE test_triple_ref AS SELECT * FROM test_triple;
-- Verify comprehensive
WITH test_cases AS (
    SELECT a, b, c FROM (VALUES
        (1, 'x', 2.0),
        (2, 'y', 1.0),
        (0, 'z', 0.0),
        (4, 'x', 3.0)
    ) AS t(a, b, c)
)
SELECT
    a, b, c,
    (SELECT COUNT(*) FROM test_triple WHERE a = tc.a AND b = tc.b AND c = tc.c) AS compressed,
    (SELECT COUNT(*) FROM test_triple_ref WHERE a = tc.a AND b = tc.b AND c = tc.c) AS reference,
    (SELECT COUNT(*) FROM test_triple WHERE a = tc.a AND b = tc.b AND c = tc.c) =
    (SELECT COUNT(*) FROM test_triple_ref WHERE a = tc.a AND b = tc.b AND c = tc.c) AS match
FROM test_cases tc;
 a | b |  c  | compressed | reference | match 
---+---+-----+------------+-----------+-------
 1 | x | 2.0 |         17 |        17 | t
 2 | y | 1.0 |         17 |        17 | t
 0 | z | 0.0 |         17 |        17 | t
 4 | x | 3.0 |         17 |        17 | t

-----------------------------------------------------------
-- Cleanup
-----------------------------------------------------------
DROP TABLE IF EXISTS composite_filter_test CASCADE;
DROP TABLE IF EXISTS composite_filter_ref CASCADE;
DROP TABLE IF EXISTS null_test CASCADE;
DROP TABLE IF EXISTS null_test_ref CASCADE;
DROP TABLE IF EXISTS test_triple CASCADE;
DROP TABLE IF EXISTS test_triple_ref CASCADE;