diff options
Diffstat (limited to 'contrib/bloom/expected')
| -rw-r--r-- | contrib/bloom/expected/bloom.out | 230 |
1 files changed, 230 insertions, 0 deletions
diff --git a/contrib/bloom/expected/bloom.out b/contrib/bloom/expected/bloom.out new file mode 100644 index 0000000..dae12a7 --- /dev/null +++ b/contrib/bloom/expected/bloom.out @@ -0,0 +1,230 @@ +CREATE EXTENSION bloom; +CREATE TABLE tst ( + i int4, + t text +); +INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i; +CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (col1 = 3); +ALTER INDEX bloomidx SET (length=80); +SET enable_seqscan=on; +SET enable_bitmapscan=off; +SET enable_indexscan=off; +SELECT count(*) FROM tst WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tst WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +SET enable_seqscan=off; +SET enable_bitmapscan=on; +SET enable_indexscan=on; +EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7; + QUERY PLAN +------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tst + Recheck Cond: (i = 7) + -> Bitmap Index Scan on bloomidx + Index Cond: (i = 7) +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE t = '5'; + QUERY PLAN +------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tst + Recheck Cond: (t = '5'::text) + -> Bitmap Index Scan on bloomidx + Index Cond: (t = '5'::text) +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tst + Recheck Cond: ((i = 7) AND (t = '5'::text)) + -> Bitmap Index Scan on bloomidx + Index Cond: ((i = 7) AND (t = '5'::text)) +(5 rows) + +SELECT count(*) FROM tst WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tst WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +DELETE FROM tst; +INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i; +VACUUM ANALYZE tst; +SELECT count(*) FROM tst WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tst WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +DELETE FROM tst WHERE i > 1 OR t = '5'; +VACUUM tst; +INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i; +SELECT count(*) FROM tst WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tst WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +VACUUM FULL tst; +SELECT count(*) FROM tst WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tst WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +-- Try an unlogged table too +CREATE UNLOGGED TABLE tstu ( + i int4, + t text +); +INSERT INTO tstu SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,2000) i; +CREATE INDEX bloomidxu ON tstu USING bloom (i, t) WITH (col2 = 4); +SET enable_seqscan=off; +SET enable_bitmapscan=on; +SET enable_indexscan=on; +EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tstu + Recheck Cond: (i = 7) + -> Bitmap Index Scan on bloomidxu + Index Cond: (i = 7) +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE t = '5'; + QUERY PLAN +-------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tstu + Recheck Cond: (t = '5'::text) + -> Bitmap Index Scan on bloomidxu + Index Cond: (t = '5'::text) +(5 rows) + +EXPLAIN (COSTS OFF) SELECT count(*) FROM tstu WHERE i = 7 AND t = '5'; + QUERY PLAN +--------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on tstu + Recheck Cond: ((i = 7) AND (t = '5'::text)) + -> Bitmap Index Scan on bloomidxu + Index Cond: ((i = 7) AND (t = '5'::text)) +(5 rows) + +SELECT count(*) FROM tstu WHERE i = 7; + count +------- + 200 +(1 row) + +SELECT count(*) FROM tstu WHERE t = '5'; + count +------- + 112 +(1 row) + +SELECT count(*) FROM tstu WHERE i = 7 AND t = '5'; + count +------- + 13 +(1 row) + +RESET enable_seqscan; +RESET enable_bitmapscan; +RESET enable_indexscan; +-- Run amvalidator function on our opclasses +SELECT opcname, amvalidate(opc.oid) +FROM pg_opclass opc JOIN pg_am am ON am.oid = opcmethod +WHERE amname = 'bloom' +ORDER BY 1; + opcname | amvalidate +----------+------------ + int4_ops | t + text_ops | t +(2 rows) + +-- +-- relation options +-- +DROP INDEX bloomidx; +CREATE INDEX bloomidx ON tst USING bloom (i, t) WITH (length=7, col1=4); +SELECT reloptions FROM pg_class WHERE oid = 'bloomidx'::regclass; + reloptions +------------------- + {length=7,col1=4} +(1 row) + +-- check for min and max values +\set VERBOSITY terse +CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (length=0); +ERROR: value 0 out of bounds for option "length" +CREATE INDEX bloomidx2 ON tst USING bloom (i, t) WITH (col1=0); +ERROR: value 0 out of bounds for option "col1" |