1 CREATE EXTENSION pg_bigm;
3 SHOW pg_bigm.last_update;
9 SET pg_bigm.last_update = '2013.09.18';
10 ERROR: parameter "pg_bigm.last_update" cannot be changed
11 SET standard_conforming_strings = on;
12 SET escape_string_warning = off;
13 -- tests for likequery
14 SELECT likequery (NULL);
20 SELECT likequery ('');
26 SELECT likequery ('aBc023#*^&');
32 SELECT likequery ('ポスグレの全文検索');
34 ----------------------
38 SELECT likequery ('\_%');
44 SELECT likequery ('pg_bigmは検索性能を200%向上させました。');
46 ---------------------------------------------
47 %pg\_bigmは検索性能を200\%向上させました。%
50 -- tests for show_bigm
51 SELECT show_bigm (NULL);
57 SELECT show_bigm ('');
63 SELECT show_bigm ('i');
69 SELECT show_bigm ('ab');
75 SELECT show_bigm ('aBc023$&^');
77 -------------------------------------
78 {" a",$&,&^,02,23,3$,Bc,"^ ",aB,c0}
81 SELECT show_bigm ('\_%');
83 -----------------------
87 SELECT show_bigm ('pg_bigm improves performance by 200%');
89 ---------------------------------------------------------------------------------------------------------------------------------
90 {" 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 "}
93 SELECT show_bigm ('木');
99 SELECT show_bigm ('検索');
105 SELECT show_bigm ('インデックスを作成');
107 -------------------------------------------------------
108 {を作,イン,クス,スを,ック,デッ,ンデ,作成,"成 "," イ"}
111 SELECT show_bigm ('pg_bigmは検索性能を200%向上させました');
113 ------------------------------------------------------------------------------------------------------------
114 {させ,した,せま,"た ",は検,まし,を2,上さ,向上,性能,検索,索性,能を," p",%向,0%,00,20,_b,bi,g_,gm,ig,mは,pg}
117 -- tests for creation of full-text search index
118 CREATE TABLE test_bigm (doc text, tag text);
119 CREATE INDEX test_bigm_idx ON test_bigm USING gin (doc gin_bigm_ops);
120 INSERT INTO test_bigm VALUES ('pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL', 'pg_trgm');
121 INSERT INTO test_bigm VALUES ('pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL', 'pg_bigm');
122 INSERT INTO test_bigm VALUES ('pg_bigm has improved the full text search performance by 200%','pg_bigm performance');
123 INSERT INTO test_bigm VALUES ('You can create an index for full text search by using GIN index.', 'full text search');
124 INSERT INTO test_bigm VALUES ('\dx displays list of installed extensions', 'meta command');
125 INSERT INTO test_bigm VALUES ('\w FILE outputs the current query buffer to the file specified', 'meta command');
126 INSERT INTO test_bigm VALUES ('pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール', 'pg_trgm');
127 INSERT INTO test_bigm VALUES ('pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール', 'pg_bigm');
128 INSERT INTO test_bigm VALUES ('pg_bigmは検索性能を200%向上させました。', 'pg_bigm 検索性能');
129 INSERT INTO test_bigm VALUES ('GINインデックスを利用して全文検索用のインデックスを作成します。', '全文検索');
130 INSERT INTO test_bigm VALUES ('And she tore the dress in anger');
131 INSERT INTO test_bigm VALUES ('She sells sea shells on the sea shore');
132 INSERT INTO test_bigm VALUES ('Those orchids are very special to her');
133 INSERT INTO test_bigm VALUES ('Did you not see the wet floor sign?');
134 INSERT INTO test_bigm VALUES ('The stylist refused them politely');
135 INSERT INTO test_bigm VALUES ('You will get into deep trouble for staying out late');
136 INSERT INTO test_bigm VALUES ('He is awaiting trial');
137 INSERT INTO test_bigm VALUES ('It was a trivial mistake');
138 INSERT INTO test_bigm VALUES ('ここは東京都');
139 INSERT INTO test_bigm VALUES ('東京と京都に行く');
140 -- tests pg_gin_pending_stats
141 SELECT * FROM pg_gin_pending_stats('test_bigm_idx');
148 SELECT * FROM pg_gin_pending_stats('test_bigm_idx');
154 -- tests for full-text search
155 SET enable_seqscan = off;
156 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('a');
158 ------------------------------------------
159 Bitmap Heap Scan on test_bigm
160 Recheck Cond: (doc ~~ '%a%'::text)
161 -> Bitmap Index Scan on test_bigm_idx
162 Index Cond: (doc ~~ '%a%'::text)
165 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('am');
167 -------------------------------------------
168 Bitmap Heap Scan on test_bigm
169 Recheck Cond: (doc ~~ '%am%'::text)
170 -> Bitmap Index Scan on test_bigm_idx
171 Index Cond: (doc ~~ '%am%'::text)
174 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('GIN');
176 --------------------------------------------
177 Bitmap Heap Scan on test_bigm
178 Recheck Cond: (doc ~~ '%GIN%'::text)
179 -> Bitmap Index Scan on test_bigm_idx
180 Index Cond: (doc ~~ '%GIN%'::text)
183 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('bigm');
185 ---------------------------------------------
186 Bitmap Heap Scan on test_bigm
187 Recheck Cond: (doc ~~ '%bigm%'::text)
188 -> Bitmap Index Scan on test_bigm_idx
189 Index Cond: (doc ~~ '%bigm%'::text)
192 SELECT doc FROM test_bigm WHERE doc LIKE likequery (NULL);
197 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('');
202 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('%');
204 ---------------------------------------------------------------
205 pg_bigm has improved the full text search performance by 200%
206 pg_bigmは検索性能を200%向上させました。
209 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('\');
211 ----------------------------------------------------------------
212 \dx displays list of installed extensions
213 \w FILE outputs the current query buffer to the file specified
216 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('_');
218 -------------------------------------------------------------------------------
219 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
220 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
221 pg_bigm has improved the full text search performance by 200%
222 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
223 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
224 pg_bigmは検索性能を200%向上させました。
227 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('\dx');
229 -------------------------------------------
230 \dx displays list of installed extensions
233 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('pg_bigm');
235 -------------------------------------------------------------------------------
236 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
237 pg_bigm has improved the full text search performance by 200%
238 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
239 pg_bigmは検索性能を200%向上させました。
242 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('200%');
244 ---------------------------------------------------------------
245 pg_bigm has improved the full text search performance by 200%
246 pg_bigmは検索性能を200%向上させました。
249 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('w');
251 ----------------------------------------------------------------
252 \w FILE outputs the current query buffer to the file specified
253 Did you not see the wet floor sign?
254 You will get into deep trouble for staying out late
256 It was a trivial mistake
259 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('by');
261 ------------------------------------------------------------------
262 pg_bigm has improved the full text search performance by 200%
263 You can create an index for full text search by using GIN index.
266 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('GIN');
268 ------------------------------------------------------------------
269 You can create an index for full text search by using GIN index.
270 GINインデックスを利用して全文検索用のインデックスを作成します。
273 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('tool');
278 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('Tool');
280 -------------------------------------------------------------------------------
281 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
282 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
285 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('performance');
287 ---------------------------------------------------------------
288 pg_bigm has improved the full text search performance by 200%
291 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('使');
293 -------------------------------------------
294 Bitmap Heap Scan on test_bigm
295 Recheck Cond: (doc ~~ '%使%'::text)
296 -> Bitmap Index Scan on test_bigm_idx
297 Index Cond: (doc ~~ '%使%'::text)
300 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('検索');
302 ---------------------------------------------
303 Bitmap Heap Scan on test_bigm
304 Recheck Cond: (doc ~~ '%検索%'::text)
305 -> Bitmap Index Scan on test_bigm_idx
306 Index Cond: (doc ~~ '%検索%'::text)
309 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('ツール');
311 -----------------------------------------------
312 Bitmap Heap Scan on test_bigm
313 Recheck Cond: (doc ~~ '%ツール%'::text)
314 -> Bitmap Index Scan on test_bigm_idx
315 Index Cond: (doc ~~ '%ツール%'::text)
318 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE likequery ('全文検索');
320 -------------------------------------------------
321 Bitmap Heap Scan on test_bigm
322 Recheck Cond: (doc ~~ '%全文検索%'::text)
323 -> Bitmap Index Scan on test_bigm_idx
324 Index Cond: (doc ~~ '%全文検索%'::text)
327 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('使');
329 ----------------------------------------------------------------
330 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
331 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
334 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('検索');
336 -----------------------------------------------------------------
337 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
338 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
339 pg_bigmは検索性能を200%向上させました。
340 GINインデックスを利用して全文検索用のインデックスを作成します。
343 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('ツール');
345 ----------------------------------------------------------------
346 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
347 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
350 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('インデックスを作成');
352 -----------------------------------------------------------------
353 GINインデックスを利用して全文検索用のインデックスを作成します。
356 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('3-gramの全文検索');
358 ----------------------------------------------------------------
359 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
362 -- check that the search results don't change if enable_recheck is disabled
363 -- in order to check that index full search is NOT executed
364 SET pg_bigm.enable_recheck = off;
365 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('w');
367 ----------------------------------------------------------------
368 \w FILE outputs the current query buffer to the file specified
369 Did you not see the wet floor sign?
370 You will get into deep trouble for staying out late
372 It was a trivial mistake
375 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('by');
377 ------------------------------------------------------------------
378 pg_bigm has improved the full text search performance by 200%
379 You can create an index for full text search by using GIN index.
382 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('使');
384 ----------------------------------------------------------------
385 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
386 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
389 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('検索');
391 -----------------------------------------------------------------
392 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
393 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
394 pg_bigmは検索性能を200%向上させました。
395 GINインデックスを利用して全文検索用のインデックスを作成します。
398 SET pg_bigm.enable_recheck = on;
399 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE '%bigm%';
401 ---------------------------------------------
402 Bitmap Heap Scan on test_bigm
403 Recheck Cond: (doc ~~ '%bigm%'::text)
404 -> Bitmap Index Scan on test_bigm_idx
405 Index Cond: (doc ~~ '%bigm%'::text)
408 SELECT doc FROM test_bigm WHERE doc LIKE '%Tool%';
410 -------------------------------------------------------------------------------
411 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
412 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
415 SELECT doc FROM test_bigm WHERE doc LIKE '%検索%';
417 -----------------------------------------------------------------
418 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
419 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
420 pg_bigmは検索性能を200%向上させました。
421 GINインデックスを利用して全文検索用のインデックスを作成します。
424 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE '%\%';
426 ------------------------------------------
427 Bitmap Heap Scan on test_bigm
428 Recheck Cond: (doc ~~ '%\%'::text)
429 -> Bitmap Index Scan on test_bigm_idx
430 Index Cond: (doc ~~ '%\%'::text)
433 SELECT doc FROM test_bigm WHERE doc LIKE '%\%';
435 ---------------------------------------------------------------
436 pg_bigm has improved the full text search performance by 200%
439 EXPLAIN (COSTS off) SELECT doc FROM test_bigm WHERE doc LIKE 'pg\___gm%';
441 ------------------------------------------------
442 Bitmap Heap Scan on test_bigm
443 Recheck Cond: (doc ~~ 'pg\___gm%'::text)
444 -> Bitmap Index Scan on test_bigm_idx
445 Index Cond: (doc ~~ 'pg\___gm%'::text)
448 SELECT doc FROM test_bigm WHERE doc LIKE 'pg\___gm%';
450 -------------------------------------------------------------------------------
451 pg_trgm - Tool that provides 3-gram full text search capability in PostgreSQL
452 pg_bigm - Tool that provides 2-gram full text search capability in PostgreSQL
453 pg_bigm has improved the full text search performance by 200%
454 pg_trgm - PostgreSQLで3-gramの全文検索を使えるようにするツール
455 pg_bigm - PostgreSQLで2-gramの全文検索を使えるようにするツール
456 pg_bigmは検索性能を200%向上させました。
459 -- tests with standard_conforming_strings disabled
460 SET standard_conforming_strings = off;
461 SELECT likequery('\\_%');
467 SELECT show_bigm('\\_%');
469 -----------------------
470 {" \\","% ","\\_",_%}
473 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('\\');
475 ----------------------------------------------------------------
476 \dx displays list of installed extensions
477 \w FILE outputs the current query buffer to the file specified
480 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('\\dx');
482 -------------------------------------------
483 \dx displays list of installed extensions
486 SELECT doc FROM test_bigm WHERE doc LIKE likequery ('200%');
488 ---------------------------------------------------------------
489 pg_bigm has improved the full text search performance by 200%
490 pg_bigmは検索性能を200%向上させました。
493 -- tests for drop of pg_bigm
494 DROP EXTENSION pg_bigm CASCADE;
495 NOTICE: drop cascades to index test_bigm_idx
496 SELECT likequery('test');
497 ERROR: function likequery(unknown) does not exist
498 LINE 1: SELECT likequery('test');
500 HINT: No function matches the given name and argument types. You might need to add explicit type casts.