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;
10 SET extra_float_digits TO 0;
11 -- tests for pg_bigm.last_update
12 SHOW pg_bigm.last_update;
18 SET pg_bigm.last_update = '2013.09.18';
19 ERROR: parameter "pg_bigm.last_update" cannot be changed
20 -- tests for likequery
21 SELECT likequery(NULL);
33 SELECT likequery(' ');
39 SELECT likequery('aBc023#*^&');
45 SELECT likequery('\_%');
51 -- tests for show_bigm
52 SELECT show_bigm(NULL);
64 SELECT show_bigm('i');
70 SELECT show_bigm('ab');
76 SELECT show_bigm('aBc023$&^');
78 -------------------------------------
79 {" a",$&,&^,02,23,3$,Bc,"^ ",aB,c0}
82 SELECT show_bigm('\_%');
84 -----------------------
88 SELECT show_bigm(' ');
94 SELECT show_bigm('pg_bigm improves performance by 200%');
96 ---------------------------------------------------------------------------------------------------------------------------------
97 {" 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 "}
100 -- tests for creation of full-text search index
101 CREATE TABLE test_bigm (col1 text, col2 text);
102 CREATE INDEX test_bigm_idx ON test_bigm
103 USING gin (col1 gin_bigm_ops, col2 gin_bigm_ops);
104 \copy test_bigm from 'data/bigm.csv' with csv
105 -- tests pg_gin_pending_stats
106 -- exclude pages column from the return values of only this call of
107 -- pg_gin_pending_stats(), in order to stabilize the result of
108 -- this regression test whatever block size is used in PostgreSQL server.
109 SELECT tuples FROM pg_gin_pending_stats('test_bigm_idx');
116 SELECT * FROM pg_gin_pending_stats('test_bigm_idx');
122 SELECT * FROM pg_gin_pending_stats('test_bigm');
123 ERROR: relation "test_bigm" is not a GIN index
124 CREATE INDEX test_bigm_btree ON test_bigm USING btree (col2);
125 SELECT * FROM pg_gin_pending_stats('test_bigm_btree');
126 ERROR: relation "test_bigm_btree" is not a GIN index
127 DROP INDEX test_bigm_btree;
128 -- tests for full-text search
129 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('a');
131 -------------------------------------------
132 Bitmap Heap Scan on test_bigm
133 Recheck Cond: (col1 ~~ '%a%'::text)
134 -> Bitmap Index Scan on test_bigm_idx
135 Index Cond: (col1 ~~ '%a%'::text)
138 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('am');
140 --------------------------------------------
141 Bitmap Heap Scan on test_bigm
142 Recheck Cond: (col1 ~~ '%am%'::text)
143 -> Bitmap Index Scan on test_bigm_idx
144 Index Cond: (col1 ~~ '%am%'::text)
147 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('XML');
149 ---------------------------------------------
150 Bitmap Heap Scan on test_bigm
151 Recheck Cond: (col1 ~~ '%XML%'::text)
152 -> Bitmap Index Scan on test_bigm_idx
153 Index Cond: (col1 ~~ '%XML%'::text)
156 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
158 ----------------------------------------------
159 Bitmap Heap Scan on test_bigm
160 Recheck Cond: (col1 ~~ '%bigm%'::text)
161 -> Bitmap Index Scan on test_bigm_idx
162 Index Cond: (col1 ~~ '%bigm%'::text)
165 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery(NULL);
170 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('');
175 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('%');
177 ---------------------------------------------------------------
178 Sets the similarity threshold used by the =% operator.
179 pg_bigm has improved the full text search performance by 200%
182 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\');
184 ----------------------------------------------------------------
185 Sets whether "\'" is allowed in string literals.
186 \dx displays list of installed extensions
187 \w FILE outputs the current query buffer to the file specified
190 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('_');
192 --------------------------------------------------------------------------------
193 Allows archiving of WAL files using archive_command.
194 Sets the minimum concurrent open transactions before performing commit_delay.
195 Shows the last update date of pg_bigm.
196 Sets the size reserved for pg_stat_activity.query, in bytes.
197 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
198 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
199 pg_bigm has improved the full text search performance by 200%
202 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\dx');
204 -------------------------------------------
205 \dx displays list of installed extensions
208 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pg_bigm');
210 --------------------------------------------------------------------------------
211 Shows the last update date of pg_bigm.
212 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
213 pg_bigm has improved the full text search performance by 200%
216 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('200%');
218 ---------------------------------------------------------------
219 pg_bigm has improved the full text search performance by 200%
222 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery(' ');
224 --------------------------------------------------------------------------------
225 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
226 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
229 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Y');
231 ------------------------------------------------------------------
232 Generates debugging output for LISTEN and NOTIFY.
233 You can create an index for full text search by using GIN index.
234 You will get into deep trouble for staying out late
237 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pi');
239 --------------------------------------------------------------
240 Vacuum cost amount available before napping, for autovacuum.
241 Vacuum cost amount available before napping.
244 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('GIN');
246 ------------------------------------------------------------------------------
247 Sets the maximum allowed result for exact search by GIN.
248 Sets the maximum number of bi-gram keys allowed to use for GIN index search.
249 You can create an index for full text search by using GIN index.
252 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('gin');
254 ------------------------------------------------------------
255 Generates debugging output for LISTEN and NOTIFY.
256 Enables logging of recovery-related debugging information.
259 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Tool');
261 --------------------------------------------------------------------------------
262 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
263 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
266 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('performance');
268 ---------------------------------------------------------------
269 Writes executor performance statistics to the server log.
270 Writes parser performance statistics to the server log.
271 Writes planner performance statistics to the server log.
272 Writes cumulative performance statistics to the server log.
273 pg_bigm has improved the full text search performance by 200%
276 -- check that the search results don't change if enable_recheck is disabled
277 -- in order to check that index full search is NOT executed
278 SET pg_bigm.enable_recheck = off;
279 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('Y');
281 ------------------------------------------------------------------
282 Generates debugging output for LISTEN and NOTIFY.
283 You can create an index for full text search by using GIN index.
284 You will get into deep trouble for staying out late
287 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('pi');
289 --------------------------------------------------------------
290 Vacuum cost amount available before napping, for autovacuum.
291 Vacuum cost amount available before napping.
294 SET pg_bigm.enable_recheck = on;
295 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE '%bigm%';
297 ----------------------------------------------
298 Bitmap Heap Scan on test_bigm
299 Recheck Cond: (col1 ~~ '%bigm%'::text)
300 -> Bitmap Index Scan on test_bigm_idx
301 Index Cond: (col1 ~~ '%bigm%'::text)
304 SELECT col1 FROM test_bigm WHERE col1 LIKE '%Tool%';
306 --------------------------------------------------------------------------------
307 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
308 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
311 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE '%\%';
313 -------------------------------------------
314 Bitmap Heap Scan on test_bigm
315 Recheck Cond: (col1 ~~ '%\%'::text)
316 -> Bitmap Index Scan on test_bigm_idx
317 Index Cond: (col1 ~~ '%\%'::text)
320 SELECT col1 FROM test_bigm WHERE col1 LIKE '%\%';
322 ---------------------------------------------------------------
323 Sets the similarity threshold used by the =% operator.
324 pg_bigm has improved the full text search performance by 200%
327 EXPLAIN (COSTS off) SELECT col1 FROM test_bigm WHERE col1 LIKE 'pg\___gm%';
329 -------------------------------------------------
330 Bitmap Heap Scan on test_bigm
331 Recheck Cond: (col1 ~~ 'pg\___gm%'::text)
332 -> Bitmap Index Scan on test_bigm_idx
333 Index Cond: (col1 ~~ 'pg\___gm%'::text)
336 SELECT col1 FROM test_bigm WHERE col1 LIKE 'pg\___gm%';
338 --------------------------------------------------------------------------------
339 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
340 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
341 pg_bigm has improved the full text search performance by 200%
344 -- tests for pg_bigm.enable_recheck
345 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('trial');
347 ----------------------
351 SET pg_bigm.enable_recheck = off;
352 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('trial');
354 -------------------------------------------------------------------------------------------------------------------------
355 Whether to defer a read-only serializable transaction until it can be executed with no possible serialization failures.
357 It was a trivial mistake
360 -- tests for pg_bigm.gin_key_limit
361 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
367 SET pg_bigm.gin_key_limit = 6;
368 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
374 SET pg_bigm.gin_key_limit = 5;
375 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
381 SET pg_bigm.gin_key_limit = 4;
382 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
388 SET pg_bigm.gin_key_limit = 3;
389 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
395 SET pg_bigm.gin_key_limit = 2;
396 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
402 SET pg_bigm.gin_key_limit = 1;
403 SELECT count(*) FROM test_bigm WHERE col1 LIKE likequery('she tore');
409 SET pg_bigm.enable_recheck = on;
410 SET pg_bigm.gin_key_limit = 0;
411 -- tests with standard_conforming_strings disabled
412 SET standard_conforming_strings = off;
413 SELECT likequery('\\_%');
419 SELECT show_bigm('\\_%');
421 -----------------------
422 {" \\","% ","\\_",_%}
425 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\\');
427 ----------------------------------------------------------------
428 Sets whether "\'" is allowed in string literals.
429 \dx displays list of installed extensions
430 \w FILE outputs the current query buffer to the file specified
433 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('\\dx');
435 -------------------------------------------
436 \dx displays list of installed extensions
439 SELECT col1 FROM test_bigm WHERE col1 LIKE likequery('200%');
441 ---------------------------------------------------------------
442 pg_bigm has improved the full text search performance by 200%
445 -- tests for full text search with multi-column index
446 -- keyword exists only in col1. Query on col2 must not return any rows.
447 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col2 LIKE likequery('queries');
449 -------------------------------------------------
450 Bitmap Heap Scan on test_bigm
451 Recheck Cond: (col2 ~~ '%queries%'::text)
452 -> Bitmap Index Scan on test_bigm_idx
453 Index Cond: (col2 ~~ '%queries%'::text)
456 SELECT * FROM test_bigm WHERE col2 LIKE likequery('queries');
461 -- keyword exists only in col2. All rows with keyword in col2 are returned.
462 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col2 LIKE likequery('meta');
464 ----------------------------------------------
465 Bitmap Heap Scan on test_bigm
466 Recheck Cond: (col2 ~~ '%meta%'::text)
467 -> Bitmap Index Scan on test_bigm_idx
468 Index Cond: (col2 ~~ '%meta%'::text)
471 SELECT * FROM test_bigm WHERE col2 LIKE likequery('meta');
473 ----------------------------------------------------------------+--------------
474 \dx displays list of installed extensions | meta command
475 \w FILE outputs the current query buffer to the file specified | meta command
478 -- keyword exists in both columns. Query on col1 must not return rows with keyword in col2 only.
479 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
481 ----------------------------------------------
482 Bitmap Heap Scan on test_bigm
483 Recheck Cond: (col1 ~~ '%bigm%'::text)
484 -> Bitmap Index Scan on test_bigm_idx
485 Index Cond: (col1 ~~ '%bigm%'::text)
488 SELECT * FROM test_bigm WHERE col1 LIKE likequery('bigm');
490 --------------------------------------------------------------------------------+---------------------
491 Shows the last update date of pg_bigm. | pg_bigm.last_update
492 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL | pg_bigm
493 pg_bigm has improved the full text search performance by 200% | pg_bigm performance
496 -- tests for bigm_similarity
497 SELECT bigm_similarity('wow', NULL);
503 SELECT bigm_similarity('wow', '');
509 SELECT bigm_similarity('wow', 'WOWa ');
515 SELECT bigm_similarity('wow', ' WOW ');
521 SELECT bigm_similarity('wow', ' wow ');
527 SELECT bigm_similarity('---', '####---');
533 -- tests for text similarity serach
534 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'a';
536 ------------------------------------------
537 Bitmap Heap Scan on test_bigm
538 Recheck Cond: (col1 =% 'a'::text)
539 -> Bitmap Index Scan on test_bigm_idx
540 Index Cond: (col1 =% 'a'::text)
543 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'am';
545 ------------------------------------------
546 Bitmap Heap Scan on test_bigm
547 Recheck Cond: (col1 =% 'am'::text)
548 -> Bitmap Index Scan on test_bigm_idx
549 Index Cond: (col1 =% 'am'::text)
552 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'XML';
554 -------------------------------------------
555 Bitmap Heap Scan on test_bigm
556 Recheck Cond: (col1 =% 'XML'::text)
557 -> Bitmap Index Scan on test_bigm_idx
558 Index Cond: (col1 =% 'XML'::text)
561 EXPLAIN (COSTS off) SELECT * FROM test_bigm WHERE col1 =% 'bigm';
563 --------------------------------------------
564 Bitmap Heap Scan on test_bigm
565 Recheck Cond: (col1 =% 'bigm'::text)
566 -> Bitmap Index Scan on test_bigm_idx
567 Index Cond: (col1 =% 'bigm'::text)
570 SELECT col1 FROM test_bigm WHERE col1 =% NULL;
575 SELECT col1 FROM test_bigm WHERE col1 =% '';
580 SELECT col1 FROM test_bigm WHERE col1 =% '%';
582 --------------------------------------------------------
583 Sets the similarity threshold used by the =% operator.
586 SELECT col1 FROM test_bigm WHERE col1 =% '\\';
588 -------------------------------------------
589 \dx displays list of installed extensions
592 SELECT col1 FROM test_bigm WHERE col1 =% '_';
597 SELECT col1 FROM test_bigm WHERE col1 =% '\\dx';
599 --------------------------------------------------------------
600 Shows the maximum number of index keys.
601 Recheck that heap tuples fetched from index match the query.
602 \dx displays list of installed extensions
605 SELECT col1 FROM test_bigm WHERE col1 =% '200%';
607 ---------------------------------------------------------------
608 Sets the similarity threshold used by the =% operator.
609 pg_bigm has improved the full text search performance by 200%
612 SELECT col1 FROM test_bigm WHERE col1 =% ' ';
617 SELECT count(*), min(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE col1 =% 'Y';
623 SELECT count(*), max(bigm_similarity(col1, 'Y')) FROM test_bigm WHERE NOT col1 =% 'Y';
629 SELECT count(*), min(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE col1 =% 'pi';
635 SELECT count(*), max(bigm_similarity(col1, 'pi')) FROM test_bigm WHERE NOT col1 =% 'pi';
641 SET pg_bigm.similarity_limit = 0.06;
642 SELECT count(*), min(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE col1 =% 'GIN';
648 SELECT count(*), max(bigm_similarity(col1, 'GIN')) FROM test_bigm WHERE NOT col1 =% 'GIN';
654 SELECT count(*), min(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE col1 =% 'gin';
660 SELECT count(*), max(bigm_similarity(col1, 'gin')) FROM test_bigm WHERE NOT col1 =% 'gin';
666 SELECT count(*), min(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE col1 =% 'Tool';
672 SELECT count(*), max(bigm_similarity(col1, 'Tool')) FROM test_bigm WHERE NOT col1 =% 'Tool';
678 SELECT count(*), min(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE col1 =% 'performance';
684 SELECT count(*), max(bigm_similarity(col1, 'performance')) FROM test_bigm WHERE NOT col1 =% 'performance';
690 -- tests for drop of pg_bigm
691 DROP EXTENSION pg_bigm CASCADE;
692 NOTICE: drop cascades to index test_bigm_idx
693 SELECT likequery('test');
694 ERROR: function likequery(unknown) does not exist
695 LINE 1: SELECT likequery('test');
697 HINT: No function matches the given name and argument types. You might need to add explicit type casts.