-
Notifications
You must be signed in to change notification settings - Fork 1.1k
Expand file tree
/
Copy pathcompress_compbloom_index_drop.out
More file actions
108 lines (102 loc) · 6.01 KB
/
Copy pathcompress_compbloom_index_drop.out
File metadata and controls
108 lines (102 loc) · 6.01 KB
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
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
-- 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.
---------------------------------------------------------------------
-- Some chunks don't have a composite bloom index after index change
---------------------------------------------------------------------
CREATE TABLE mixed_avail_drop(ts timestamptz, a int, b int, seg int);
SELECT create_hypertable('mixed_avail_drop', by_range('ts', interval '1 day'));
create_hypertable
-------------------
(1,t)
-- Create index
CREATE INDEX idx_ab ON mixed_avail_drop (a, b);
ALTER TABLE mixed_avail_drop SET (
timescaledb.compress,
timescaledb.compress_segmentby = 'seg',
timescaledb.compress_orderby = 'ts'
);
-- Insert data for 3 days (3 chunks)
INSERT INTO mixed_avail_drop
SELECT ts, (i % 10), (i % 5), 1
FROM generate_series('2024-01-01'::timestamptz, '2024-01-03', interval '1 hour') ts,
generate_series(1, 100) i;
-- Compress first 2 chunks (will have composite bloom from index)
SELECT compress_chunk(c) FROM show_chunks('mixed_avail_drop') c LIMIT 2;
compress_chunk
----------------------------------------
_timescaledb_internal._hyper_1_1_chunk
_timescaledb_internal._hyper_1_2_chunk
-- Before index drop
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_drop WHERE a = 1 AND b = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
Custom Scan (ChunkAppend) on mixed_avail_drop (actual rows=0.00 loops=1)
Order: mixed_avail_drop.ts, mixed_avail_drop.seg
-> Sort (actual rows=0.00 loops=1)
Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.seg
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[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_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.seg
Sort Method: quicksort
-> Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=0.00 loops=1)
Vectorized Filter: ((a = 1) AND (b = 2))
-> Seq Scan on compress_hyper_2_5_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[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_b, TEST-HASHES::bigint[]))
Rows Removed by Filter: 3
-> Sort (actual rows=0.00 loops=1)
Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.seg
Sort Method: quicksort
-> Index Scan using _hyper_1_3_chunk_idx_ab on _hyper_1_3_chunk (actual rows=0.00 loops=1)
Index Cond: ((a = 1) AND (b = 2))
-- Drop index
DROP INDEX idx_ab;
-- Compress last chunk (no composite bloom, index is gone)
SELECT compress_chunk(c) FROM show_chunks('mixed_avail_drop') 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
-- Query should work on all chunks
SELECT COUNT(*) FROM mixed_avail_drop WHERE a = 1 AND b = 2;
count
-------
0
-- After index drop
EXPLAIN (ANALYZE, BUFFERS OFF, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM mixed_avail_drop WHERE a = 1 AND b = 2
ORDER BY 1,2,3,4;
--- QUERY PLAN ---
Custom Scan (ChunkAppend) on mixed_avail_drop (actual rows=0.00 loops=1)
Order: mixed_avail_drop.ts, mixed_avail_drop.seg
-> Sort (actual rows=0.00 loops=1)
Sort Key: _hyper_1_1_chunk.ts, _hyper_1_1_chunk.seg
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[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_a, TEST-HASHES::bigint[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_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.seg
Sort Method: quicksort
-> Custom Scan (ColumnarScan) on _hyper_1_2_chunk (actual rows=0.00 loops=1)
Vectorized Filter: ((a = 1) AND (b = 2))
-> Seq Scan on compress_hyper_2_5_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[]) AND _timescaledb_functions.bloom1_contains_any_hashes(regress-test-bloom_b, TEST-HASHES::bigint[]))
Rows Removed by Filter: 3
-> Sort (actual rows=0.00 loops=1)
Sort Key: _hyper_1_3_chunk.ts, _hyper_1_3_chunk.seg
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)
DROP TABLE IF EXISTS mixed_avail_drop CASCADE;