1 CREATE EXTENSION bloom;
6 INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
7 CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3);
9 SET enable_bitmapscan=off;
10 SET enable_indexscan=off;
11 SELECT count(*) FROM tst WHERE i = 7;
17 SELECT count(*) FROM tst WHERE t = '5';
23 SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
29 SET enable_seqscan=off;
30 SET enable_bitmapscan=on;
31 SET enable_indexscan=on;
32 EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7;
34 -------------------------------------------
36 -> Bitmap Heap Scan on tst
38 -> Bitmap Index Scan on bloomidx
42 EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE t = '5';
44 -------------------------------------------
46 -> Bitmap Heap Scan on tst
47 Recheck Cond: (t = '5'::text)
48 -> Bitmap Index Scan on bloomidx
49 Index Cond: (t = '5'::text)
52 EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
54 ---------------------------------------------------------
56 -> Bitmap Heap Scan on tst
57 Recheck Cond: ((i = 7) AND (t = '5'::text))
58 -> Bitmap Index Scan on bloomidx
59 Index Cond: ((i = 7) AND (t = '5'::text))
62 SELECT count(*) FROM tst WHERE i = 7;
68 SELECT count(*) FROM tst WHERE t = '5';
74 SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
81 INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
83 SELECT count(*) FROM tst WHERE i = 7;
89 SELECT count(*) FROM tst WHERE t = '5';
95 SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
101 DELETE FROM tst WHERE i > 1 OR t = '5';
103 INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
104 SELECT count(*) FROM tst WHERE i = 7;
110 SELECT count(*) FROM tst WHERE t = '5';
116 SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
123 SELECT count(*) FROM tst WHERE i = 7;
129 SELECT count(*) FROM tst WHERE t = '5';
135 SELECT count(*) FROM tst WHERE i = 7 AND t = '5';
141 -- Try an unlogged table too
142 CREATE UNLOGGED TABLE tstu (
146 INSERT INTO tstu SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i;
147 CREATE INDEX bloomidxu ON tstu USING bloom (i, t) WITH (col2 = 4);
148 SET enable_seqscan=off;
149 SET enable_bitmapscan=on;
150 SET enable_indexscan=on;
151 EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7;
153 --------------------------------------------
155 -> Bitmap Heap Scan on tstu
156 Recheck Cond: (i = 7)
157 -> Bitmap Index Scan on bloomidxu
161 EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE t = '5';
163 --------------------------------------------
165 -> Bitmap Heap Scan on tstu
166 Recheck Cond: (t = '5'::text)
167 -> Bitmap Index Scan on bloomidxu
168 Index Cond: (t = '5'::text)
171 EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7 AND t = '5';
173 ---------------------------------------------------------
175 -> Bitmap Heap Scan on tstu
176 Recheck Cond: ((i = 7) AND (t = '5'::text))
177 -> Bitmap Index Scan on bloomidxu
178 Index Cond: ((i = 7) AND (t = '5'::text))
181 SELECT count(*) FROM tstu WHERE i = 7;
187 SELECT count(*) FROM tstu WHERE t = '5';
193 SELECT count(*) FROM tstu WHERE i = 7 AND t = '5';
199 RESET enable_seqscan;
200 RESET enable_bitmapscan;
201 RESET enable_indexscan;
202 -- Run amvalidator function on our opclasses
203 SELECT opcname, amvalidate(opc.oid)
204 FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod
205 WHERE amname = 'bloom'
208 ----------+------------