1 CREATE EXTENSION pg_bigm;
3 SET standard_conforming_strings = on;
4 SET escape_string_warning = off;
5 SET enable_seqscan = off;
6 SET pg_bigm.enable_recheck = on;
7 SET pg_bigm.gin_key_limit = 0;
8 SET pg_bigm.similarity_limit = 0.02;
9 -- tests for pg_bigm.last_update
10 SHOW pg_bigm.last_update;
16 SET pg_bigm.last_update = '2013.09.18';
17 ERROR: parameter "pg_bigm.last_update" cannot be changed
18 -- tests for likequery
19 SELECT likequery(NULL);
31 SELECT likequery(' ');
37 SELECT likequery('aBc023#*^&');
43 SELECT likequery('\_%');
49 -- tests for show_bigm
50 SELECT show_bigm(NULL);
62 SELECT show_bigm('i');
68 SELECT show_bigm('ab');
74 SELECT show_bigm('aBc023$&^');
76 -------------------------------------
77 {" a",$&,&^,02,23,3$,Bc,"^ ",aB,c0}
80 SELECT show_bigm('\_%');
82 -----------------------
86 SELECT show_bigm(' ');
92 SELECT show_bigm('pg_bigm improves performance by 200%');
94 ---------------------------------------------------------------------------------------------------------------------------------
95 {" 2"," b"," i"," p","% ",0%,00,20,_b,an,bi,by,ce,"e ",er,es,fo,g_,gm,ig,im,"m ",ma,mp,nc,or,ov,pe,pg,pr,rf,rm,ro,"s ",ve,"y "}
98 -- tests for creation of full-text search index
99 CREATE TABLE test_bigm (col1 text, col2 text);
100 CREATE INDEX test_bigm_idx ON test_bigm
101 USING gin (col1 gin_bigm_ops, col2 gin_bigm_ops);
102 \copy test_bigm from 'data/bigm.csv' with csv
103 -- tests pg_gin_pending_stats
104 SELECT * FROM pg_gin_pending_stats('test_bigm_idx');
111 SELECT * FROM pg_gin_pending_stats('test_bigm_idx');
117 SELECT * FROM pg_gin_pending_stats('test_bigm');
118 ERROR: relation "test_bigm" is not a GIN index
119 CREATE INDEX test_bigm_btree ON test_bigm USING btree (col2);
120 SELECT * FROM pg_gin_pending_stats('test_bigm_btree');
121 ERROR: relation "test_bigm_btree" is not a GIN index
122 DROP INDEX test_bigm_btree;
123 -- tests for full-text search
124 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('a');
126 -------------------------------------------
127 Bitmap Heap Scan on test_bigm
128 Recheck Cond: (col1 ~~ '%a%'::text)
129 -> Bitmap Index Scan on test_bigm_idx
130 Index Cond: (col1 ~~ '%a%'::text)
133 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('am');
135 --------------------------------------------
136 Bitmap Heap Scan on test_bigm
137 Recheck Cond: (col1 ~~ '%am%'::text)
138 -> Bitmap Index Scan on test_bigm_idx
139 Index Cond: (col1 ~~ '%am%'::text)
142 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('XML');
144 ---------------------------------------------
145 Bitmap Heap Scan on test_bigm
146 Recheck Cond: (col1 ~~ '%XML%'::text)
147 -> Bitmap Index Scan on test_bigm_idx
148 Index Cond: (col1 ~~ '%XML%'::text)
151 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
153 ----------------------------------------------
154 Bitmap Heap Scan on test_bigm
155 Recheck Cond: (col1 ~~ '%bigm%'::text)
156 -> Bitmap Index Scan on test_bigm_idx
157 Index Cond: (col1 ~~ '%bigm%'::text)
160 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery(NULL);
165 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('');
170 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('%');
172 ---------------------------------------------------------------
173 Sets the similarity threshold used by the =% operator.
174 pg_bigm has improved the full text search performance by 200%
177 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\');
179 ----------------------------------------------------------------
180 Sets whether "\'" is allowed in string literals.
181 \dx displays list of installed extensions
182 \w FILE outputs the current query buffer to the file specified
185 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('_');
187 --------------------------------------------------------------------------------
188 Allows archiving of WAL files using archive_command.
189 Sets the minimum concurrent open transactions before performing commit_delay.
190 Shows the last update date of pg_bigm.
191 Sets the size reserved for pg_stat_activity.query, in bytes.
192 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
193 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
194 pg_bigm has improved the full text search performance by 200%
197 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\dx');
199 -------------------------------------------
200 \dx displays list of installed extensions
203 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pg_bigm');
205 --------------------------------------------------------------------------------
206 Shows the last update date of pg_bigm.
207 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
208 pg_bigm has improved the full text search performance by 200%
211 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('200%');
213 ---------------------------------------------------------------
214 pg_bigm has improved the full text search performance by 200%
217 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery(' ');
219 --------------------------------------------------------------------------------
220 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
221 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
224 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Y');
226 ------------------------------------------------------------------
227 Generates debugging output for LISTEN and NOTIFY.
228 You can create an index for full text search by using GIN index.
229 You will get into deep trouble for staying out late
232 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pi');
234 --------------------------------------------------------------
235 Vacuum cost amount available before napping, for autovacuum.
236 Vacuum cost amount available before napping.
239 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('GIN');
241 ------------------------------------------------------------------------------
242 Sets the maximum allowed result for exact search by GIN.
243 Sets the maximum number of bi-gram keys allowed to use for GIN index search.
244 You can create an index for full text search by using GIN index.
247 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('gin');
249 ------------------------------------------------------------
250 Generates debugging output for LISTEN and NOTIFY.
251 Enables logging of recovery-related debugging information.
254 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Tool');
256 --------------------------------------------------------------------------------
257 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
258 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
261 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('performance');
263 ---------------------------------------------------------------
264 Writes executor performance statistics to the server log.
265 Writes parser performance statistics to the server log.
266 Writes planner performance statistics to the server log.
267 Writes cumulative performance statistics to the server log.
268 pg_bigm has improved the full text search performance by 200%
271 -- check that the search results don't change if enable_recheck is disabled
272 -- in order to check that index full search is NOT executed
273 SET pg_bigm.enable_recheck = off;
274 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Y');
276 ------------------------------------------------------------------
277 Generates debugging output for LISTEN and NOTIFY.
278 You can create an index for full text search by using GIN index.
279 You will get into deep trouble for staying out late
282 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pi');
284 --------------------------------------------------------------
285 Vacuum cost amount available before napping, for autovacuum.
286 Vacuum cost amount available before napping.
289 SET pg_bigm.enable_recheck = on;
290 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE '%bigm%';
292 ----------------------------------------------
293 Bitmap Heap Scan on test_bigm
294 Recheck Cond: (col1 ~~ '%bigm%'::text)
295 -> Bitmap Index Scan on test_bigm_idx
296 Index Cond: (col1 ~~ '%bigm%'::text)
299 SELECT col1 FROM test_bigm WHERE col1 LIKE '%Tool%';
301 --------------------------------------------------------------------------------
302 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
303 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
306 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE '%\%';
308 -------------------------------------------
309 Bitmap Heap Scan on test_bigm
310 Recheck Cond: (col1 ~~ '%\%'::text)
311 -> Bitmap Index Scan on test_bigm_idx
312 Index Cond: (col1 ~~ '%\%'::text)
315 SELECT col1 FROM test_bigm WHERE col1 LIKE '%\%';
317 ---------------------------------------------------------------
318 Sets the similarity threshold used by the =% operator.
319 pg_bigm has improved the full text search performance by 200%
322 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE 'pg\___gm%';
324 -------------------------------------------------
325 Bitmap Heap Scan on test_bigm
326 Recheck Cond: (col1 ~~ 'pg\___gm%'::text)
327 -> Bitmap Index Scan on test_bigm_idx
328 Index Cond: (col1 ~~ 'pg\___gm%'::text)
331 SELECT col1 FROM test_bigm WHERE col1 LIKE 'pg\___gm%';
333 --------------------------------------------------------------------------------
334 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
335 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
336 pg_bigm has improved the full text search performance by 200%
339 -- tests for pg_bigm.enable_recheck
340 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('trial');
342 ----------------------
346 SET pg_bigm.enable_recheck = off;
347 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('trial');
349 -------------------------------------------------------------------------------------------------------------------------
350 Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
352 It was a trivial mistake
355 -- tests for pg_bigm.gin_key_limit
356 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
362 SET pg_bigm.gin_key_limit = 6;
363 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
369 SET pg_bigm.gin_key_limit = 5;
370 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
376 SET pg_bigm.gin_key_limit = 4;
377 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
383 SET pg_bigm.gin_key_limit = 3;
384 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
390 SET pg_bigm.gin_key_limit = 2;
391 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
397 SET pg_bigm.gin_key_limit = 1;
398 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
404 SET pg_bigm.enable_recheck = on;
405 SET pg_bigm.gin_key_limit = 0;
406 -- tests with standard_conforming_strings disabled
407 SET standard_conforming_strings = off;
408 SELECT likequery('\\_%');
414 SELECT show_bigm('\\_%');
416 -----------------------
417 {" \\","% ","\\_",_%}
420 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\\');
422 ----------------------------------------------------------------
423 Sets whether "\'" is allowed in string literals.
424 \dx displays list of installed extensions
425 \w FILE outputs the current query buffer to the file specified
428 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\\dx');
430 -------------------------------------------
431 \dx displays list of installed extensions
434 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('200%');
436 ---------------------------------------------------------------
437 pg_bigm has improved the full text search performance by 200%
440 -- tests for full text search with multi-column index
441 -- keyword exists only in col1. Query on col2 must not return any rows.
442 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col2 LIKE likequery('queries');
444 -------------------------------------------------
445 Bitmap Heap Scan on test_bigm
446 Recheck Cond: (col2 ~~ '%queries%'::text)
447 -> Bitmap Index Scan on test_bigm_idx
448 Index Cond: (col2 ~~ '%queries%'::text)
451 SELECT * FROM test_bigm WHERE col2 LIKE likequery('queries');
456 -- keyword exists only in col2. All rows with keyword in col2 are returned.
457 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col2 LIKE likequery('meta');
459 ----------------------------------------------
460 Bitmap Heap Scan on test_bigm
461 Recheck Cond: (col2 ~~ '%meta%'::text)
462 -> Bitmap Index Scan on test_bigm_idx
463 Index Cond: (col2 ~~ '%meta%'::text)
466 SELECT * FROM test_bigm WHERE col2 LIKE likequery('meta');
468 ----------------------------------------------------------------+--------------
469 \dx displays list of installed extensions | meta command
470 \w FILE outputs the current query buffer to the file specified | meta command
473 -- keyword exists in both columns. Query on col1 must not return rows with keyword in col2 only.
474 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
476 ----------------------------------------------
477 Bitmap Heap Scan on test_bigm
478 Recheck Cond: (col1 ~~ '%bigm%'::text)
479 -> Bitmap Index Scan on test_bigm_idx
480 Index Cond: (col1 ~~ '%bigm%'::text)
483 SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
485 --------------------------------------------------------------------------------+---------------------
486 Shows the last update date of pg_bigm. | pg_bigm.last_update
487 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL | pg_bigm
488 pg_bigm has improved the full text search performance by 200% | pg_bigm performance
491 -- tests for bigm_similarity
492 SELECT bigm_similarity('wow', NULL);
498 SELECT bigm_similarity('wow', '');
504 SELECT bigm_similarity('wow', 'WOWa ');
510 SELECT bigm_similarity('wow', ' WOW ');
516 SELECT bigm_similarity('wow', ' wow ');
522 SELECT bigm_similarity('---', '####---');
528 -- tests for text similarity serach
529 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'a';
531 ------------------------------------------
532 Bitmap Heap Scan on test_bigm
533 Recheck Cond: (col1 =% 'a'::text)
534 -> Bitmap Index Scan on test_bigm_idx
535 Index Cond: (col1 =% 'a'::text)
538 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'am';
540 ------------------------------------------
541 Bitmap Heap Scan on test_bigm
542 Recheck Cond: (col1 =% 'am'::text)
543 -> Bitmap Index Scan on test_bigm_idx
544 Index Cond: (col1 =% 'am'::text)
547 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'XML';
549 -------------------------------------------
550 Bitmap Heap Scan on test_bigm
551 Recheck Cond: (col1 =% 'XML'::text)
552 -> Bitmap Index Scan on test_bigm_idx
553 Index Cond: (col1 =% 'XML'::text)
556 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'bigm';
558 --------------------------------------------
559 Bitmap Heap Scan on test_bigm
560 Recheck Cond: (col1 =% 'bigm'::text)
561 -> Bitmap Index Scan on test_bigm_idx
562 Index Cond: (col1 =% 'bigm'::text)
565 SELECT col1 FROM test_bigm WHERE col1 =% NULL;
570 SELECT col1 FROM test_bigm WHERE col1 =% '';
575 SELECT col1 FROM test_bigm WHERE col1 =% '%';
577 --------------------------------------------------------
578 Sets the similarity threshold used by the =% operator.
581 SELECT col1 FROM test_bigm WHERE col1 =% '\\';
583 -------------------------------------------
584 \dx displays list of installed extensions
587 SELECT col1 FROM test_bigm WHERE col1 =% '_';
592 SELECT col1 FROM test_bigm WHERE col1 =% '\\dx';
594 --------------------------------------------------------------
595 Shows the maximum number of index keys.
596 Recheck that heap tuples fetched from index match the query.
597 \dx displays list of installed extensions
600 SELECT col1 FROM test_bigm WHERE col1 =% '200%';
602 ---------------------------------------------------------------
603 Sets the similarity threshold used by the =% operator.
604 pg_bigm has improved the full text search performance by 200%
607 SELECT col1 FROM test_bigm WHERE col1 =% ' ';
612 SELECT count(*), min(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE col1 =% 'Y';
618 SELECT count(*), max(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE NOT col1 =% 'Y';
624 SELECT count(*), min(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE col1 =% 'pi';
630 SELECT count(*), max(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE NOT col1 =% 'pi';
636 SET pg_bigm.similarity_limit = 0.06;
637 SELECT count(*), min(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE col1 =% 'GIN';
643 SELECT count(*), max(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE NOT col1 =% 'GIN';
649 SELECT count(*), min(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE col1 =% 'gin';
655 SELECT count(*), max(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE NOT col1 =% 'gin';
661 SELECT count(*), min(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE col1 =% 'Tool';
667 SELECT count(*), max(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE NOT col1 =% 'Tool';
673 SELECT count(*), min(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE col1 =% 'performance';
679 SELECT count(*), max(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE NOT col1 =% 'performance';
685 -- tests for drop of pg_bigm
686 DROP EXTENSION pg_bigm CASCADE;
687 NOTICE: drop cascades to index test_bigm_idx
688 SELECT likequery('test');
689 ERROR: function likequery(unknown) does not exist
690 LINE 1: SELECT likequery('test');
692 HINT: No function matches the given name and argument types. You might need to add explicit type casts.