diff options
| -rw-r--r-- | contrib/bloom/expected/bloom.out | 60 | ||||
| -rw-r--r-- | contrib/bloom/sql/bloom.sql | 18 |
2 files changed, 62 insertions, 16 deletions
diff --git a/contrib/bloom/expected/bloom.out b/contrib/bloom/expected/bloom.out index 5e8269faf35..71700efd5ae 100644 --- a/contrib/bloom/expected/bloom.out +++ b/contrib/bloom/expected/bloom.out @@ -3,7 +3,7 @@ CREATE TABLE tst ( i int4, t text ); -INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,100000) i; +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); SET enable_seqscan=on; SET enable_bitmapscan=off; @@ -11,19 +11,19 @@ SET enable_indexscan=off; SELECT count(*) FROM tst WHERE i = 7; count ------- - 10000 + 200 (1 row) SELECT count(*) FROM tst WHERE t = '5'; count ------- - 6264 + 112 (1 row) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; count ------- - 588 + 13 (1 row) SET enable_seqscan=off; @@ -62,61 +62,93 @@ EXPLAIN (COSTS OFF) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; SELECT count(*) FROM tst WHERE i = 7; count ------- - 10000 + 200 (1 row) SELECT count(*) FROM tst WHERE t = '5'; count ------- - 6264 + 112 (1 row) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; count ------- - 588 + 13 (1 row) DELETE FROM tst; -INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,100000) i; +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 ------- - 10000 + 200 (1 row) SELECT count(*) FROM tst WHERE t = '5'; count ------- - 6264 + 112 (1 row) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; count ------- - 588 + 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 ------- - 10000 + 200 (1 row) SELECT count(*) FROM tst WHERE t = '5'; count ------- - 6264 + 112 (1 row) SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; count ------- - 588 + 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) + diff --git a/contrib/bloom/sql/bloom.sql b/contrib/bloom/sql/bloom.sql index f9d0ad45d98..e9174828fe6 100644 --- a/contrib/bloom/sql/bloom.sql +++ b/contrib/bloom/sql/bloom.sql @@ -5,7 +5,7 @@ CREATE TABLE tst ( t text ); -INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,100000) i; +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); SET enable_seqscan=on; @@ -29,13 +29,21 @@ SELECT count(*) FROM tst WHERE t = '5'; SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; DELETE FROM tst; -INSERT INTO tst SELECT i%10, substr(md5(i::text), 1, 1) FROM generate_series(1,100000) i; +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; SELECT count(*) FROM tst WHERE t = '5'; SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; +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; +SELECT count(*) FROM tst WHERE t = '5'; +SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; + VACUUM FULL tst; SELECT count(*) FROM tst WHERE i = 7; @@ -45,3 +53,9 @@ SELECT count(*) FROM tst WHERE i = 7 AND t = '5'; 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; |