&~
supports score.
Umm. It's too rough instruction. I couldn't reproduce your case with the instruction.
I try the following and it works well:
1.Install PostgreSQL 11.3 by installer: https://www.enterprisedb.com/thank-you-downloading-postgresql?anid=1256619
C:\Program Files\PostgreSQL\11
(I needed to enable administrator permission)psql
from startup menupostgres
database with postgres
userpgroonga_test
database by CREATE DATABASE pgroonga_test;
\c pgroonga_test
CREATE EXTENSION pgroonga;
(It doesn't report any error)CREATE TABLE memos (
id integer,
content text
);
CREATE INDEX pgroonga_content_index ON memos USING pgroonga (content);
INSERT INTO memos VALUES (1, 'PostgreSQL is a relational database management system.');
INSERT INTO memos VALUES (2, 'Groonga is a fast full text search engine that supports all languages.');
INSERT INTO memos VALUES (3, 'PGroonga is a PostgreSQL extension that uses Groonga as index.');
INSERT INTO memos VALUES (4, 'There is groonga command.');
SET enable_seqscan = off;
SELECT * FROM memos WHERE content &@ 'engine';
-- id | content
-- ----+------------------------------------------------------------------------
-- 2 | Groonga is a fast full text search engine that supports all languages.
-- (1 row)
So, it will be your environment specific issue.
SET enable_seqscan = off;
.https://www.postgresql.org/docs/current/app-psql.html
\d[S+] [ pattern ]
base_roonga_test-# \d score_memos
Table ½ public.score_memos ╗
Colonne | Type | Collationnement | NULL-able | Par dÚfaut
---------+---------+-----------------+-----------+------------
id | integer | | not null |
content | text | | |
Index :
"score_memos_pkey" PRIMARY KEY, btree (id)
"pgroonga_score_memos_content_index" pgroonga (content)
base_roonga_test-#
I got it.
You need to use pgroonga_text_regexp_ops_v2
operator class https://pgroonga.github.io/reference/#text-regexp-ops-v2 by searching with regular expression with index.
CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content pgroonga_text_regexp_ops_v2);
See also: https://pgroonga.github.io/reference/operators/regular-expression-v2.html#usage
I have a question concerning the score. If i use an inner join i get a sql message saying : ERROR: ERREUR: la colonne « tableoid » n'existe pas
LINE 1: select f.fid_recherchefichiers, pgroonga_score(tableoid, cti...
^
HINT: Il existe une colonne nommée « tableoid » pour la table « p » mais elle ne peut pas être référencée dans cette partie de la requête.
SQL state: 42703
Character: 48
CREATE TABLE
, CREATE INDEX
, INSERT
, SELECT
, ...) to reproduce the case?
${PGDATA}/pgroonga.log
to the issue? pgroonga.log
will include the details for this case.
id, name
and around 3m records, which having name a pgroonga index. but the query doesn't use the index, it just performs a seq scan, is there any technique to enforce it?-- Table Definition ----------------------------------------------
CREATE TABLE datamarket.test_name (
id integer PRIMARY KEY,
name character varying
);
-- Indices -------------------------------------------------------
CREATE UNIQUE INDEX test_name_pkey ON datamarket.test_name(id int4_ops);
CREATE INDEX test_name_name_pgroonga ON datamarket.test_name(name pgroonga_text_full_text_search_ops_v2);
explain SELECT _default_.name AS name
FROM datamarket.test_name AS _default_
WHERE _default_.name &@ '欧莱雅' limit 10;
Limit (cost=10000000000.00..10000005192.86 rows=10 width=38)
-> Seq Scan on test_name _default_ (cost=10000000000.00..10001984712.56 rows=3822 width=38)
Filter: (name &@ '欧莱雅'::character varying)
-- this seems not working neither
set enable_seqscan = off;
show enable_seqscan; -- it does show off