Where communities thrive


  • Join over 1.5M+ people
  • Join over 100K+ communities
  • Free without limits
  • Create your own community
People
Activity
    anthonydafc
    @anthonydafc
    @kou ok works better with latest version of poostgres 11 (i was using 11.1)
    anthonydafc
    @anthonydafc
    @kou SELECT , pgroonga_score(tableoid, ctid) AS score
    FROM score_memos
    where content &~ '.
    groonga'
    if a use this it will give me 0 scorer on every line found
    Sutou Kouhei
    @kou
    anthonydafc
    @anthonydafc
    same thing with seqscan disabled
    Sutou Kouhei
    @kou
    Please show the result of EXPLAIN ANALYZE VERBOSE SELECT ...;.
    anthonydafc
    @anthonydafc
    Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)
    Sutou Kouhei
    @kou
    Please show whole result. Don't omit anything.
    anthonydafc
    @anthonydafc
    Sorry
    "Seq Scan on public.score_memos (cost=10000000000.00..10000000657.70 rows=1 width=44) (actual time=0.087..0.107 rows=3 loops=1)"
    " Output: id, content, pgroonga_score(tableoid, ctid)"
    " Filter: (score_memos.content &~ '.*groonga'::text)"
    " Rows Removed by Filter: 1"
    "Planning Time: 0.162 ms"
    "Execution Time: 0.152 ms"
    Sutou Kouhei
    @kou
    Please show the output of the following:
    \d score_memos
    anthonydafc
    @anthonydafc
    Sorry but how does that work ?
    Sutou Kouhei
    @kou
    It's a psql command.
    anthonydafc
    @anthonydafc

    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-#

    Sutou Kouhei
    @kou
    \di pgroonga_score_memos_content_index
    anthonydafc
    @anthonydafc
                               Liste des relations
    SchÚma | Nom | Type | PropriÚtaire | Table
    --------+------------------------------------+-------+--------------+-------------
    public | pgroonga_score_memos_content_index | index | postgres | score_memos
    (1 ligne)
    Sutou Kouhei
    @kou
    select * from pg_catalog.pg_indexes where indexname = 'pgroonga_score_memos_content_index';
    anthonydafc
    @anthonydafc
    "public" "score_memos" "pgroonga_score_memos_content_index" "CREATE INDEX pgroonga_score_memos_content_index ON public.score_memos USING pgroonga (content)"
    Sutou Kouhei
    @kou

    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

    anthonydafc
    @anthonydafc
    @kou Works great. Many thanks
    anthonydafc
    @anthonydafc

    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

    anthonydafc
    @anthonydafc
    the tableoid doesn't exist
    Sutou Kouhei
    @kou
    Could you open an issue from here https://github.com/pgroonga/pgroonga/issues and write full SQLs (CREATE TABLE, CREATE INDEX, INSERT, SELECT, ...) to reproduce the case?
    anthonydafc
    @anthonydafc
    ok, have you ever had issues concerning lock tables with groonga ???
    Sutou Kouhei
    @kou
    If your PostgreSQL is crashed, lock may be remained.
    anthonydafc
    @anthonydafc
    what do u mean ?
    Sutou Kouhei
    @kou
    Did you talk about http://groonga.org/docs/reference/commands/lock_clear.html related?
    If you didn't, you don't need to understand my answer.
    (I hope that you explain what you want to know with concrete examples. Concrete examples are easy to understand.)
    anthonydafc
    @anthonydafc
    Sorry, but i have had a few problems, with the database been locket, while executing tasks. the lof files that a pgrn table has been locked and only way to work around is to delete pgrn files and reindex...is there any easier way or an explanation to these locks ?
    Sutou Kouhei
    @kou
    Could you open an issue from here https://github.com/pgroonga/pgroonga/issues and attach your ${PGDATA}/pgroonga.log to the issue? pgroonga.log will include the details for this case.
    anthonydafc
    @anthonydafc
    Hello, have you a problem with the download section on https://pgroonga.github.io/, because the link announce "not found"
    i'm looking for the windows versions
    Kentaro Hayashi
    @kenhys
    @anthonydafc you can download here. https://github.com/pgroonga/pgroonga/releases/tag/2.2.0
    Sutou Kouhei
    @kou
    I've fixed these links.
    weldomha
    @weldomha
    hi all, pgroonga 11 debian
    there is no package following this :
    doesn't follow debian directory tree for postgres
    any idea how to install pgroonga 11 on debian ??
    weldomha
    @weldomha
    without compelling the source
    Sutou Kouhei
    @kou
    I've uploaded deb packages for PostgreSQL 11: https://pgroonga.github.io/install/debian.html
    Zhanzhao (Deo) Liang
    @DeoLeung
    Hi guys, is there any hints about using rk search on Chinese with pinyin?
    Sutou Kouhei
    @kou
    You can't use prefix RK search for pinyin. You can just use prefix search.
    Zhanzhao (Deo) Liang
    @DeoLeung
    Hi, I have an index question, I create a table with two column 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
    Zhanzhao (Deo) Liang
    @DeoLeung
    if I use ilike '%xx%', it uses the index, I tried other operators with no luck
    Zhanzhao (Deo) Liang
    @DeoLeung
    I cast the column to text works alter table datamarket.test_name alter column name type text; , so is it required that the column should be text type?
    Horimoto Yasuhiro
    @komainu8

    I think, If the type of column is varchar, we need to specify to pgroonga_varchar_full_text_search_ops_v2 operator class.

    For example, how about below example?

    CREATE INDEX ${INDEX_NAME} ON ${TABLE_NAME} USING pgroonga (${COLUMN_NAME} pgroonga_varchar_full_text_search_ops_v2);
    Zhanzhao (Deo) Liang
    @DeoLeung
    i c, let me have a try
    Zhanzhao (Deo) Liang
    @DeoLeung
    it works , thx @komainu8 , and I check the document the default for varchar & text are different :)