sql/pg_bigm.sql

   1 \pset null '(null)'
   2
   3 -- Load pg_bigm and ludia_funcs
   4 CREATE EXTENSION pg_bigm;
   5 CREATE EXTENSION ludia_funcs;
   6
   7 -- Set parameters for the tests
   8 SET client_min_messages TO LOG;
   9 SET standard_conforming_strings TO off;
  10 SET escape_string_warning TO off;
  11 SET work_mem = '4MB';
  12 SET maintenance_work_mem TO '512MB';
  13 SET enable_seqscan TO off;
  14 SET enable_bitmapscan TO on;
  15
  16 -- Test the case where the columns with CHAR data type were indexed
  17 -- with pg_bigm and pgs2norm function.
  18 CREATE TABLE char_tbl (col1 char(256), col2 char(256), col3 char(256));
  19 \copy char_tbl from data/test_tbl.txt
  20 CREATE INDEX char_tbl_idx ON char_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
  21                                                                    pgs2norm(col2) gin_bigm_ops,
  22                                                                    pgs2norm(col3) gin_bigm_ops)
  23                                                                    WITH (FASTUPDATE = off);
  24
  25 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM char_tbl
  26     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'))
  27     ORDER BY col1;
  28 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM char_tbl
  29     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'))
  30     ORDER BY col1;
  31 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM char_tbl
  32     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'))
  33     ORDER BY col1;
  34
  35 -- Test the case where the columns with VARCHAR data type were indexed
  36 -- with pg_bigm and pgs2norm function.
  37 CREATE TABLE varchar_tbl (col1 varchar(256), col2 varchar(256), col3 varchar(256));
  38 \copy varchar_tbl from data/test_tbl.txt
  39 CREATE INDEX varchar_tbl_idx ON varchar_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
  40                                                                            pgs2norm(col2) gin_bigm_ops,
  41                                                                            pgs2norm(col3) gin_bigm_ops)
  42                                                                            WITH (FASTUPDATE = off);
  43
  44 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM varchar_tbl
  45     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'))
  46     ORDER BY col1;
  47 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM varchar_tbl
  48     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'))
  49     ORDER BY col1;
  50 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM varchar_tbl
  51     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'))
  52     ORDER BY col1;
  53
  54 -- Test the case where the columns with TEXT data type were indexed
  55 -- with pg_bigm and pgs2norm function.
  56 CREATE TABLE text_tbl (col1 text, col2 text, col3 text);
  57 \copy text_tbl from data/test_tbl.txt
  58 CREATE INDEX text_tbl_idx ON text_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
  59                                                                   pgs2norm(col2) gin_bigm_ops,
  60                                                                   pgs2norm(col3) gin_bigm_ops)
  61                                                                   WITH (FASTUPDATE = off);
  62
  63 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM text_tbl
  64     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'))
  65     ORDER BY col1;
  66 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM text_tbl
  67     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'))
  68     ORDER BY col1;
  69 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM text_tbl
  70     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'))
  71     ORDER BY col1;
  72
  73 -- Test the case where condition has "AND".
  74 SELECT count(*) FROM text_tbl WHERE
  75     pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
  76     pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
  77     pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
  78 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
  79     pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
  80     pgs2norm(col1) LIKE likequery(pgs2norm('実装')) AND
  81     pgs2norm(col1) LIKE likequery(pgs2norm('場合'));
  82 SELECT count(*) FROM text_tbl WHERE
  83     pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
  84     pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
  85     pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
  86 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
  87     pgs2norm(col1) LIKE likequery(pgs2norm('解釈')) AND
  88     pgs2norm(col2) LIKE likequery(pgs2norm('ﾕｰｻ゛')) AND
  89     pgs2norm(col3) LIKE likequery(pgs2norm('ＳｑＬ'));
  90
  91 -- Test the case where condition has "OR".
  92 SELECT count(*) FROM text_tbl WHERE
  93     pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
  94     pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
  95     pgs2norm(col2) LIKE likequery(pgs2norm('  '));
  96 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
  97     pgs2norm(col2) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
  98     pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
  99     pgs2norm(col2) LIKE likequery(pgs2norm('  '));
 100 SELECT count(*) FROM text_tbl WHERE
 101     pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
 102     pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
 103     pgs2norm(col3) LIKE likequery(pgs2norm('  '));
 104 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
 105     pgs2norm(col1) LIKE likequery(pgs2norm('ﾊ゜ラメータ')) OR
 106     pgs2norm(col2) LIKE likequery(pgs2norm('㉜')) OR
 107     pgs2norm(col3) LIKE likequery(pgs2norm('  '));
 108
 109 -- Test the case where condition has "NOT".
 110 SELECT count(*) FROM text_tbl WHERE
 111     pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
 112     NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
 113     NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
 114 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
 115     pgs2norm(col3) LIKE likequery(pgs2norm('ludia')) AND
 116     NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
 117     NOT pgs2norm(col3) LIKE likequery(pgs2norm('Ⓟ'));
 118 SELECT count(*) FROM text_tbl WHERE
 119     pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
 120     NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
 121     NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
 122 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
 123     pgs2norm(col2) LIKE likequery(pgs2norm('ludia')) AND
 124     NOT pgs2norm(col3) LIKE likequery(pgs2norm('㊐')) AND
 125     NOT pgs2norm(col1) LIKE likequery(pgs2norm('Ⓟ'));
 126
 127 -- Test for text search with many conditions
 128 SELECT count(*) FROM text_tbl WHERE
 129     (pgs2norm(col1) LIKE likequery(pgs2norm('文字')) AND
 130         pgs2norm(col1) LIKE likequery(pgs2norm('無効')) AND
 131         pgs2norm(col1) NOT LIKE likequery(pgs2norm('設定'))) OR
 132         (pgs2norm(col2) LIKE likequery(pgs2norm('文字')) AND
 133         pgs2norm(col2) LIKE likequery(pgs2norm('無効')) AND
 134         pgs2norm(col2) NOT LIKE likequery(pgs2norm('設定'))) OR
 135         (pgs2norm(col3) LIKE likequery(pgs2norm('文字')) AND
 136         pgs2norm(col3) LIKE likequery(pgs2norm('無効')) AND
 137         pgs2norm(col3) NOT LIKE likequery(pgs2norm('設定')));
 138 EXPLAIN (costs off) SELECT count(*) FROM text_tbl WHERE
 139     (pgs2norm(col1) LIKE likequery(pgs2norm('文字')) AND
 140         pgs2norm(col1) LIKE likequery(pgs2norm('無効')) AND
 141         pgs2norm(col1) NOT LIKE likequery(pgs2norm('設定'))) OR
 142         (pgs2norm(col2) LIKE likequery(pgs2norm('文字')) AND
 143         pgs2norm(col2) LIKE likequery(pgs2norm('無効')) AND
 144         pgs2norm(col2) NOT LIKE likequery(pgs2norm('設定'))) OR
 145         (pgs2norm(col3) LIKE likequery(pgs2norm('文字')) AND
 146         pgs2norm(col3) LIKE likequery(pgs2norm('無効')) AND
 147         pgs2norm(col3) NOT LIKE likequery(pgs2norm('設定')));
 148
 149 -- Test for UPDATE and DELETE
 150 UPDATE text_tbl SET col1 = col2, col2 = col3, col3 = col1;
 151 UPDATE text_tbl SET col1 = col2, col2 = col3, col3 = col1;
 152 UPDATE text_tbl SET col1 = col2, col2 = col3, col3 = col1;
 153 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'v(株)', col1) FROM text_tbl
 154     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('v(株)'))
 155     ORDER BY col1;
 156 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, '②⓪', col1) FROM text_tbl
 157     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('②⓪'))
 158     ORDER BY col1;
 159 SELECT pgs2snippet1(1, 32, 1, '★', '★', 0, 'ｱﾌﾟﾘｹｰｼｮﾝ', col1) FROM text_tbl
 160     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱﾌﾟﾘｹｰｼｮﾝ'))
 161     ORDER BY col1;
 162
 163 -- The text search for updated or deleted records must return no results
 164 -- even when recheck is skipped on the text search.
 165 EXPLAIN (costs off) UPDATE text_tbl SET col1 =
 166     (select string_agg(chr(num), '') from generate_series(ascii('㋐'), ascii('㋾')) num)
 167     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('㊀'));
 168 UPDATE text_tbl SET col1 =
 169     (select string_agg(chr(num), '') from generate_series(ascii('㋐'), ascii('㋾')) num)
 170     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('㊀'));
 171 SELECT count(*) FROM text_tbl WHERE pgs2norm(col1) LIKE likequery(pgs2norm('㊀'));
 172 EXPLAIN (costs off) DELETE FROM text_tbl WHERE pgs2norm(col1) like likequery(pgs2norm('⑬'));
 173 DELETE FROM text_tbl WHERE pgs2norm(col1) like likequery(pgs2norm('⑬'));
 174 SELECT count(*) FROM text_tbl WHERE pgs2norm(col1) like likequery(pgs2norm('⑬'));
 175
 176 -- Test whether both seq scan and bitmap scan return the same results
 177 SET enable_seqscan to on;
 178 SET enable_bitmapscan to off;
 179 SELECT replace(col1, E'\t', '*') FROM text_tbl
 180     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田太郎'))
 181     ORDER BY col1;
 182 SELECT replace(col1, E'\t', '*') FROM text_tbl
 183     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田太郎'))
 184     ORDER BY col1;
 185 SELECT replace(col1, E'\t', '*') FROM text_tbl
 186     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田 太郎'))
 187     ORDER BY col1;
 188 SELECT replace(col1, E'\t', '*') FROM text_tbl
 189     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'東京都\t山田太郎'))
 190     ORDER BY col1;
 191 SELECT replace(col1, E'\t', '*') FROM text_tbl
 192     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田　太郎'))
 193     ORDER BY col1;
 194 SELECT replace(col1, E'\t', '*') FROM text_tbl
 195     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都山'))
 196     ORDER BY col1;
 197 SELECT replace(col1, E'\t', '*') FROM text_tbl
 198     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都 山田'))
 199     ORDER BY col1;
 200 SELECT replace(col1, E'\t', '*') FROM text_tbl
 201     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山田'))
 202     ORDER BY col1;
 203 SELECT replace(col1, E'\t', '*') FROM text_tbl
 204     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山'))
 205     ORDER BY col1;
 206 SELECT replace(col1, E'\t', '*') FROM text_tbl
 207     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('  山'))
 208     ORDER BY col1;
 209 SELECT replace(col1, E'\t', '*') FROM text_tbl
 210     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都  '))
 211     ORDER BY col1;
 212 SELECT replace(col1, E'\t', '*') FROM text_tbl
 213     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'都\t'))
 214     ORDER BY col1;
 215 SELECT count(*) FROM text_tbl
 216     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('　'));
 217 SELECT count(*) FROM text_tbl
 218     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(' '));
 219 SELECT replace(col1, E'\t', '*') FROM text_tbl
 220     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 221     ORDER BY col1;
 222 SELECT replace(col1, E'\t', '*') FROM text_tbl
 223     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(' 山 '))
 224     ORDER BY col1;
 225 SELECT replace(col1, E'\t', '*') FROM text_tbl
 226     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ポ'))
 227     ORDER BY col1;
 228 SELECT replace(col1, E'\t', '*') FROM text_tbl
 229     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ﾎﾟ'))
 230     ORDER BY col1;
 231 SELECT count(*) FROM text_tbl
 232     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('A'));
 233 SELECT count(*) FROM text_tbl
 234     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('a'));
 235 SELECT replace(col1, E'\t', '*') FROM text_tbl
 236     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('AA'))
 237     ORDER BY col1;
 238 SELECT replace(col1, E'\t', '*') FROM text_tbl
 239     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 240     ORDER BY col1;
 241 SELECT replace(col1, E'\t', '*') FROM text_tbl
 242     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 243     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 244     ORDER BY col1;
 245 SELECT replace(col1, E'\t', '*') FROM text_tbl
 246     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 247     AND pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 248     ORDER BY col1;
 249 SELECT replace(col1, E'\t', '*') FROM text_tbl
 250     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 251     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 252     AND pgs2norm(col1) LIKE likequery(pgs2norm('田'))
 253     ORDER BY col1;
 254 SELECT replace(col1, E'\t', '*') FROM text_tbl
 255     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 256     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 257     OR pgs2norm(col1) LIKE likequery(pgs2norm('ポ'))
 258     ORDER BY col1;
 259
 260 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 261     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田太郎'))
 262     ORDER BY col1;
 263 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 264     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田 太郎'))
 265     ORDER BY col1;
 266 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 267     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'東京都\t山田太郎'))
 268     ORDER BY col1;
 269 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 270     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山田'))
 271     ORDER BY col1;
 272 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 273     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('  山'))
 274     ORDER BY col1;
 275 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 276     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'都\t'))
 277     ORDER BY col1;
 278 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 279     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('　'))
 280     ORDER BY col1;
 281
 282 SET enable_seqscan to off;
 283 SET enable_bitmapscan to on;
 284 SELECT replace(col1, E'\t', '*') FROM text_tbl
 285     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田太郎'))
 286     ORDER BY col1;
 287 SELECT replace(col1, E'\t', '*') FROM text_tbl
 288     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田太郎'))
 289     ORDER BY col1;
 290 SELECT replace(col1, E'\t', '*') FROM text_tbl
 291     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田 太郎'))
 292     ORDER BY col1;
 293 SELECT replace(col1, E'\t', '*') FROM text_tbl
 294     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'東京都\t山田太郎'))
 295     ORDER BY col1;
 296 SELECT replace(col1, E'\t', '*') FROM text_tbl
 297     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田　太郎'))
 298     ORDER BY col1;
 299 SELECT replace(col1, E'\t', '*') FROM text_tbl
 300     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都山'))
 301     ORDER BY col1;
 302 SELECT replace(col1, E'\t', '*') FROM text_tbl
 303     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都 山田'))
 304     ORDER BY col1;
 305 SELECT replace(col1, E'\t', '*') FROM text_tbl
 306     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山田'))
 307     ORDER BY col1;
 308 SELECT replace(col1, E'\t', '*') FROM text_tbl
 309     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山'))
 310     ORDER BY col1;
 311 SELECT replace(col1, E'\t', '*') FROM text_tbl
 312     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('  山'))
 313     ORDER BY col1;
 314 SELECT replace(col1, E'\t', '*') FROM text_tbl
 315     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都  '))
 316     ORDER BY col1;
 317 SELECT replace(col1, E'\t', '*') FROM text_tbl
 318     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'都\t'))
 319     ORDER BY col1;
 320 SELECT count(*) FROM text_tbl
 321     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('　'));
 322 SELECT count(*) FROM text_tbl
 323     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(' '));
 324 SELECT replace(col1, E'\t', '*') FROM text_tbl
 325     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 326     ORDER BY col1;
 327 SELECT replace(col1, E'\t', '*') FROM text_tbl
 328     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(' 山 '))
 329     ORDER BY col1;
 330 SELECT replace(col1, E'\t', '*') FROM text_tbl
 331     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ポ'))
 332     ORDER BY col1;
 333 SELECT replace(col1, E'\t', '*') FROM text_tbl
 334     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ﾎﾟ'))
 335     ORDER BY col1;
 336 SELECT count(*) FROM text_tbl
 337     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('A'));
 338 SELECT count(*) FROM text_tbl
 339     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('a'));
 340 SELECT replace(col1, E'\t', '*') FROM text_tbl
 341     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('AA'))
 342     ORDER BY col1;
 343 SELECT replace(col1, E'\t', '*') FROM text_tbl
 344     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 345     ORDER BY col1;
 346 SELECT replace(col1, E'\t', '*') FROM text_tbl
 347     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 348     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 349     ORDER BY col1;
 350 SELECT replace(col1, E'\t', '*') FROM text_tbl
 351     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 352     AND pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 353     ORDER BY col1;
 354 SELECT replace(col1, E'\t', '*') FROM text_tbl
 355     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 356     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 357     AND pgs2norm(col1) LIKE likequery(pgs2norm('田'))
 358     ORDER BY col1;
 359 SELECT replace(col1, E'\t', '*') FROM text_tbl
 360     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 361     AND pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 362     OR pgs2norm(col1) LIKE likequery(pgs2norm('ポ'))
 363     ORDER BY col1;
 364
 365 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 366     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都山田太郎'))
 367     ORDER BY col1;
 368 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 369     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都 山田 太郎'))
 370     ORDER BY col1;
 371 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 372     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'東京都\t山田太郎'))
 373     ORDER BY col1;
 374 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 375     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('都 山田'))
 376     ORDER BY col1;
 377 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 378     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('  山'))
 379     ORDER BY col1;
 380 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 381     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(E'都\t'))
 382     ORDER BY col1;
 383 EXPLAIN (costs off) SELECT replace(col1, E'\t', '*') FROM text_tbl
 384     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('　'))
 385     ORDER BY col1;
 386
 387 -- Test whether recheck is skipped expectedly when keyword length is 1 or 2.
 388 -- We can judge that recheck is skipped successfully if the debug messages
 389 -- indicating the calls to pgs2norm() and pgs2malloc() are logged only once
 390 -- respectively. So this test must run with enable_debug enabled.
 391 -- Note that enable_debug must be set to 'terse' in order to stabilize the
 392 -- result of this regression test. Otherwise, i.e., when enable_debug is set to
 393 -- 'on', the contents dealt in function pgs2malloc() and pgs2norm() are logged,
 394 -- and the order of those debug messages varies depending on the block size
 395 -- in PostgreSQL server. Those detailed information are not necessary to
 396 -- judge whether the recheck is skipped or not.
 397 SET ludia_funcs.enable_debug TO terse;
 398 SELECT col1 FROM text_tbl
 399     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都')) ORDER BY col1;
 400 SELECT col1 FROM text_tbl
 401     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京都')) ORDER BY col1;
 402 SELECT col1 FROM text_tbl
 403     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('京')) ORDER BY col1;
 404 SELECT pgs2snippet1(1, 50, 1, '*', '*', 0, '東', col1) FROM text_tbl
 405     WHERE pgs2norm(col2) LIKE likequery(pgs2norm('東')) ORDER BY col1;
 406 SELECT col1 FROM text_tbl
 407     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山'))
 408     AND pgs2norm(col1) LIKE likequery(pgs2norm('京'));
 409 SELECT col1 FROM text_tbl
 410     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('山田'))
 411     AND pgs2norm(col1) LIKE likequery(pgs2norm('京都'));
 412 SELECT col1 FROM text_tbl
 413     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('東京都'))
 414     AND pgs2norm(col1) LIKE likequery(pgs2norm('太'));
 415 SET ludia_funcs.enable_debug TO off;
 416
 417 -- Test the case where a multi-column index is created on many columns
 418 CREATE TABLE mc31_tbl (col1 text, col2 char(256), col3 varchar(256), col4 text,
 419     col5 char(256), col6 varchar(256), col7 text, col8 char(256),
 420     col9 varchar(256), col10 text, col11 char(256), col12 varchar(256),
 421     col13 text, col14 char(256), col15 varchar(256), col16 text,
 422     col17 char(256), col18 varchar(256), col19 text, col20 char(256),
 423     col21 varchar(256), col22 text, col23 char(256), col24 varchar(256),
 424     col25 text, col26 char(256), col27 varchar(256), col28 text,
 425     col29 char(256), col30 varchar(256), col31 text);
 426 CREATE INDEX mc31_tbl_idx ON mc31_tbl USING gin (pgs2norm(col1) gin_bigm_ops,
 427     pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops,
 428     pgs2norm(col4) gin_bigm_ops, pgs2norm(col5) gin_bigm_ops,
 429     pgs2norm(col6) gin_bigm_ops, pgs2norm(col7) gin_bigm_ops,
 430     pgs2norm(col8) gin_bigm_ops, pgs2norm(col9) gin_bigm_ops,
 431     pgs2norm(col10) gin_bigm_ops, pgs2norm(col11) gin_bigm_ops,
 432     pgs2norm(col12) gin_bigm_ops, pgs2norm(col13) gin_bigm_ops,
 433     pgs2norm(col14) gin_bigm_ops, pgs2norm(col15) gin_bigm_ops,
 434     pgs2norm(col16) gin_bigm_ops, pgs2norm(col17) gin_bigm_ops,
 435     pgs2norm(col18) gin_bigm_ops, pgs2norm(col19) gin_bigm_ops,
 436     pgs2norm(col20) gin_bigm_ops, pgs2norm(col21) gin_bigm_ops,
 437     pgs2norm(col22) gin_bigm_ops, pgs2norm(col23) gin_bigm_ops,
 438     pgs2norm(col24) gin_bigm_ops, pgs2norm(col25) gin_bigm_ops,
 439     pgs2norm(col26) gin_bigm_ops, pgs2norm(col27) gin_bigm_ops,
 440     pgs2norm(col28) gin_bigm_ops, pgs2norm(col29) gin_bigm_ops,
 441     pgs2norm(col30) gin_bigm_ops, pgs2norm(col31) gin_bigm_ops)
 442     WITH (FASTUPDATE = off);
 443 \copy mc31_tbl from 'data/test_tbl_31.txt'
 444
 445 SELECT col1 FROM mc31_tbl
 446     WHERE pgs2norm(col1) like likequery(pgs2norm('＿Ｓ'))
 447     ORDER BY col1;
 448 EXPLAIN (costs off ) SELECT col1 FROM mc31_tbl
 449     WHERE pgs2norm(col1) like likequery(pgs2norm('＿Ｓ'))
 450     ORDER BY col1;
 451
 452 SELECT col15 FROM mc31_tbl
 453     WHERE pgs2norm(col15) LIKE likequery(pgs2norm('ﾃ゛ィ'))
 454     ORDER BY col15;
 455 EXPLAIN (costs off ) SELECT col15 FROM mc31_tbl
 456     WHERE pgs2norm(col15) LIKE likequery(pgs2norm('ﾃ゛ィ'))
 457     ORDER BY col15;
 458
 459 SELECT col31 FROM mc31_tbl
 460     WHERE pgs2norm(col31) LIKE likequery(pgs2norm('㊔'))
 461     ORDER BY col31;
 462 EXPLAIN (costs off) SELECT col31 FROM mc31_tbl
 463     WHERE pgs2norm(col31) LIKE likequery(pgs2norm('㊔'))
 464     ORDER BY col31;
 465
 466 -- Test the cases where various text search patterns are used.
 467 CREATE TABLE abc_tbl (col1 text, col2 text, col3 text);
 468 \copy abc_tbl from data/abc_data.tsv
 469 CREATE INDEX abc_idx ON abc_tbl USING gin
 470     (pgs2norm(col1) gin_bigm_ops, pgs2norm(col2) gin_bigm_ops, pgs2norm(col3) gin_bigm_ops)
 471     WITH (FASTUPDATE = off);
 472
 473 SELECT count(*) FROM abc_tbl WHERE
 474     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 475     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 476     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 477     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 478     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 479     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 480     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 481     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 482     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 483 SELECT count(*) FROM abc_tbl WHERE
 484     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 485     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) OR
 486     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 487     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 488     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) OR
 489     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 490     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 491     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) OR
 492     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 493 SELECT count(*) FROM abc_tbl WHERE
 494     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 495     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 496     NOT pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 497     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 498     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 499     NOT pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 500     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 501     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 502     NOT pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 503 SELECT count(*) FROM abc_tbl WHERE
 504     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) OR
 505     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 506     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 507     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) OR
 508     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 509     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 510     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) OR
 511     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 512     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 513 SELECT count(*) FROM abc_tbl WHERE
 514     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) OR
 515     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) OR
 516     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 517     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) OR
 518     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) OR
 519     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 520     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) OR
 521     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) OR
 522     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 523 SELECT count(*) FROM abc_tbl WHERE
 524     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) OR
 525     pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 526     NOT pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 527     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) OR
 528     pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 529     NOT pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 530     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) OR
 531     pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 532     NOT pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 533 SELECT count(*) FROM abc_tbl WHERE
 534     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 535     NOT pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 536     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 537     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 538     NOT pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 539     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 540     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 541     NOT pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 542     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 543 SELECT count(*) FROM abc_tbl WHERE
 544     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 545     NOT pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) OR
 546     pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 547     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 548     NOT pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) OR
 549     pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 550     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 551     NOT pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) OR
 552     pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 553 SELECT count(*) FROM abc_tbl WHERE
 554     ((pgs2norm(col1) LIKE likequery(pgs2norm('AAA')) AND
 555     NOT pgs2norm(col1) LIKE likequery(pgs2norm('BBB'))) AND
 556     NOT pgs2norm(col1) LIKE likequery(pgs2norm('CCC'))) OR
 557     ((pgs2norm(col2) LIKE likequery(pgs2norm('AAA')) AND
 558     NOT pgs2norm(col2) LIKE likequery(pgs2norm('BBB'))) AND
 559     NOT pgs2norm(col2) LIKE likequery(pgs2norm('CCC'))) OR
 560     ((pgs2norm(col3) LIKE likequery(pgs2norm('AAA')) AND
 561     NOT pgs2norm(col3) LIKE likequery(pgs2norm('BBB'))) AND
 562     NOT pgs2norm(col3) LIKE likequery(pgs2norm('CCC')));
 563
 564 -- Test whether pg_bigm and pgs2norm can handle all the UTF8 characters
 565 CREATE UNLOGGED TABLE utf8_tbl (code int, col1 text);
 566 INSERT INTO utf8_tbl VALUES (-1, NULL);
 567 INSERT INTO utf8_tbl VALUES (0, '');
 568 INSERT INTO utf8_tbl SELECT code, repeat(chr(code), 3) FROM generate_series(1, 55295) code;
 569 INSERT INTO utf8_tbl SELECT code, repeat(chr(code), 3) FROM generate_series(57344, 1114111) code;
 570 CREATE INDEX utf8_tbl_idx ON utf8_tbl USING gin (pgs2norm(col1) gin_bigm_ops)
 571     WITH (FASTUPDATE = off);
 572
 573 -- Test for multi-byte, single-byte, upper-case and lower-case alphabet characters
 574 SELECT * FROM utf8_tbl
 575     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('aa')) ORDER BY code;
 576 SELECT * FROM utf8_tbl
 577     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('BB')) ORDER BY code;
 578 SELECT * FROM utf8_tbl
 579     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｃｃ')) ORDER BY code;
 580 SELECT * FROM utf8_tbl
 581     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ＤＤ')) ORDER BY code;
 582 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 583     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('aa')) ORDER BY code;
 584 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 585     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('BB')) ORDER BY code;
 586 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 587     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｃｃ')) ORDER BY code;
 588 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 589     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ＤＤ')) ORDER BY code;
 590
 591 -- Test for multi-byte, single-byte numbers
 592 SELECT * FROM utf8_tbl
 593     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('11')) ORDER BY code;
 594 SELECT * FROM utf8_tbl
 595     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('２２')) ORDER BY code;
 596 SELECT * FROM utf8_tbl
 597     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('③③')) ORDER BY code;
 598 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 599     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('11')) ORDER BY code;
 600 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 601     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('２２')) ORDER BY code;
 602 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 603     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('③③')) ORDER BY code;
 604
 605 -- Test for Japanese katakana
 606 SELECT * FROM utf8_tbl
 607     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱｱ')) ORDER BY code;
 608 SELECT * FROM utf8_tbl
 609     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('イイ')) ORDER BY code;
 610 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 611     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('ｱｱ')) ORDER BY code;
 612 EXPLAIN (costs off) SELECT * FROM utf8_tbl
 613     WHERE pgs2norm(col1) LIKE likequery(pgs2norm('イイ')) ORDER BY code;
 614
 615 -- Text for NULL
 616 SELECT * FROM utf8_tbl
 617     WHERE pgs2norm(col1) LIKE likequery(pgs2norm(NULL)) ORDER BY code;
 618
 619 -- Drop utf8_tbl to reduce the impact on subsequent CHECKPOINT
 620 DROP TABLE utf8_tbl;