-- without indexes SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) create index wowidx on test_tsvector2 using gist (a); SET enable_seqscan=OFF; SET enable_indexscan=ON; SET enable_bitmapscan=OFF; explain (costs off) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------- Aggregate -> Index Scan using wowidx on test_tsvector2 Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (3 rows) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SET enable_indexscan=OFF; SET enable_bitmapscan=ON; explain (costs off) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector2 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) RESET enable_seqscan; RESET enable_indexscan; RESET enable_bitmapscan; DROP INDEX wowidx; SET enable_seqscan=OFF; CREATE INDEX wowidx ON test_tsvector2 USING btree(a); SELECT count(*) FROM test_tsvector2 WHERE a > 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 499 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a < 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 8 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a >= 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 500 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a <= 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 9 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a = 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 1 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a <> 'lq d7 i4 7w y0 qt gw ch o6 eo'; count ------- 507 (1 row) EXPLAIN (COSTS OFF) SELECT count(*) FROM test_tsvector2 WHERE a <> 'lq d7 i4 7w y0 qt gw ch o6 eo'; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector2 Filter: (a <> '''7w'' ''ch'' ''d7'' ''eo'' ''gw'' ''i4'' ''lq'' ''o6'' ''qt'' ''y0'''::tsvector2) -> Bitmap Index Scan on wowidx (4 rows) DROP INDEX wowidx; CREATE INDEX wowidx ON test_tsvector2 USING gin (a); -- GIN only supports bitmapscan, so no need to test plain indexscan EXPLAIN (COSTS OFF) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; QUERY PLAN ------------------------------------------------------------- Aggregate -> Bitmap Heap Scan on test_tsvector2 Recheck Cond: (a @@ '''wr'' | ''qh'''::tsquery) -> Bitmap Index Scan on wowidx Index Cond: (a @@ '''wr'' | ''qh'''::tsquery) (5 rows) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr|qh'; count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'wr&qh'; count ------- 17 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq&yt'; count ------- 6 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'eq|yt'; count ------- 98 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq&yt)|(wr&qh)'; count ------- 23 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '(eq|yt)&(wr|qh)'; count ------- 39 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'w:*|q:*'; count ------- 494 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ any ('{wr,qh}'); count ------- 158 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ 'no_such_lexeme'; count ------- 0 (1 row) SELECT count(*) FROM test_tsvector2 WHERE a @@ '!no_such_lexeme'; count ------- 508 (1 row) SELECT * FROM tsvector2_stat('SELECT a FROM test_tsvector2') ORDER BY ndoc DESC, nentry DESC, word LIMIT 10; word | ndoc | nentry ------+------+-------- qq | 108 | 108 qt | 102 | 102 qe | 100 | 100 qh | 98 | 98 qw | 98 | 98 qa | 97 | 97 ql | 94 | 94 qs | 94 | 94 qi | 92 | 92 qr | 92 | 92 (10 rows) SELECT * FROM tsvector2_stat('SELECT a FROM test_tsvector2', 'AB') ORDER BY ndoc DESC, nentry DESC, word; word | ndoc | nentry ------+------+-------- (0 rows) -- test finding items in GIN's pending list create temp table pendtest (ts tsvector2); create index pendtest_idx on pendtest using gin(ts); insert into pendtest values (to_tsvector2('Lore ipsam')); insert into pendtest values (to_tsvector2('Lore ipsum')); select * from pendtest where 'ipsu:*'::tsquery @@ ts; ts -------------------- 'ipsum':2 'lore':1 (1 row) select * from pendtest where 'ipsa:*'::tsquery @@ ts; ts -------------------- 'ipsam':2 'lore':1 (1 row) select * from pendtest where 'ips:*'::tsquery @@ ts; ts -------------------- 'ipsam':2 'lore':1 'ipsum':2 'lore':1 (2 rows) select * from pendtest where 'ipt:*'::tsquery @@ ts; ts ---- (0 rows) select * from pendtest where 'ipi:*'::tsquery @@ ts; ts ---- (0 rows) --check OP_PHRASE on index create temp table phrase_index_test(fts tsvector2); insert into phrase_index_test values ('A fat cat has just eaten a rat.'); insert into phrase_index_test values (to_tsvector2('english', 'A fat cat has just eaten a rat.')); create index phrase_index_test_idx on phrase_index_test using gin(fts); set enable_seqscan = off; select * from phrase_index_test where fts @@ phraseto_tsquery('english', 'fat cat'); fts ----------------------------------- 'cat':3 'eaten':6 'fat':2 'rat':8 (1 row) RESET enable_seqscan; DROP TABLE test_tsvector2 CASCADE; DROP TABLE pendtest CASCADE; DROP TABLE phrase_index_test CASCADE; DROP EXTENSION tsvector2 CASCADE;