timescaledb/tsl/test/expected/compress_compbloom_hash_pushdown.out at 73d67a4f9ed588f42453ef4716f039704475e166 · timescale/timescaledb · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
-- 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.
-- all tests here will frst run with hash pushdown disabled and then
-- enabled for comparison
create table hash_pushdown_test(a int, b int, c int, d int)
with (
    tsdb.hypertable,
    tsdb.compress,
    tsdb.partition_column = 'c',
    tsdb.segmentby = '',
    tsdb.orderby = 'd',
    tsdb.compress_index = 'bloom(a), bloom(a,b)'
);
insert into hash_pushdown_test select x, x, x, x from generate_series(1, 10000) x;
select count(compress_chunk(x)) from show_chunks('hash_pushdown_test') x;
 count
-------
     1

vacuum full analyze hash_pushdown_test;
-- single bloom
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a = 1;
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=1.00 loops=1)
   Vectorized Filter: (a = 1)
   Rows Removed by Filter: 999
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=1.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[])
         Rows Removed by Filter: 9

-- composite bloom
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a = 1 and b = 2;
--- QUERY PLAN ---
 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_2_chunk (actual rows=0.00 loops=1)
         Filter: (_timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_7035_a_b, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[]))
         Rows Removed by Filter: 10

-- saop with single bloom
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a = any(array[1,2,3]);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=3.00 loops=1)
   Vectorized Filter: (a = ANY ('{1,2,3}'::integer[]))
   Rows Removed by Filter: 997
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=1.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[])
         Rows Removed by Filter: 9

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a IN (1,2,3);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=3.00 loops=1)
   Vectorized Filter: (a = ANY ('{1,2,3}'::integer[]))
   Rows Removed by Filter: 997
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=1.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[])
         Rows Removed by Filter: 9

-- saop with composite bloom
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a = any(array[1,2,3]) and b = any(array[4,5,6]);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
   Vectorized Filter: ((a = ANY ('{1,2,3}'::integer[])) AND (b = ANY ('{4,5,6}'::integer[])))
   Rows Removed by Filter: 1000
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=1.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[])
         Rows Removed by Filter: 9

EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
select * from hash_pushdown_test where a IN (1,2,3) and b IN (4,5,6);
--- QUERY PLAN ---
 Custom Scan (ColumnarScan) on _hyper_1_1_chunk (actual rows=0.00 loops=1)
   Vectorized Filter: ((a = ANY ('{1,2,3}'::integer[])) AND (b = ANY ('{4,5,6}'::integer[])))
   Rows Removed by Filter: 1000
   ->  Seq Scan on compress_hyper_2_2_chunk (actual rows=1.00 loops=1)
         Filter: _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[])
         Rows Removed by Filter: 9

-----------------------------------
-- cleanup
-----------------------------------
drop table if exists hash_pushdown_test;