askdkc

      - name: Setup PostgreSQL
        run: |
          sudo apt install -y software-properties-common
          sudo add-apt-repository -y universe
          sudo add-apt-repository -y ppa:groonga/ppa
          sudo apt update
          sudo apt install postgresql postgresql-14-pgroonga
          sudo systemctl restart postgresql
          sudo -u postgres -H psql -c "ALTER ROLE postgres WITH PASSWORD 'postgres';"
          sudo -u postgres -H createdb TEST_DB_NAME

askdkc

# VER 15のPostgreSQLインストール
sudo su -

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
apt-get update
apt-get install -y --force-yes postgresql-15 postgresql-contrib-15


# アクセス設定：OSのユーザを使う YOUR_MAIN_USERNAMEをubuntuのユーザ名に、YOUR_DB_PASSWORDをお好きなパスワードに書き換えるべし

askdkc

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.5 (Homebrew)
-- Dumped by pg_dump version 14.5 (Homebrew)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;

askdkc

// 同義語モデル「Synonym」がある前提

Synonym::all('terms');
/* 出力
Illuminate\Database\Eloquent\Collection {#1487
  all: [
    App\Models\Synonym {#2094
      terms: "{さいとう,サイトウ,斉藤,斎藤,齋藤,齊藤}",
    },
    App\Models\Synonym {#2095

askdkc

# explain analyze verbose select pgroonga_highlight_html(title, pgroonga_query_extract_keywords('ジョバンニ OR 牛 OR 斉藤'))AS highlighted_title, title, pgroonga_highlight_html(body, pgroonga_query_extract_keywords('ジョバンニ OR 牛 OR 斉藤'))AS highlighted_body, body from "posts" where title &@~ pgroonga_query_expand('synonyms', 'terms', 'terms', 'ジョバンニ OR 牛 OR 斉藤')::varchar or body &@~ pgroonga_query_expand('synonyms', 'terms', 'terms', 'ジョバンニ OR 牛 OR 斉藤')::varchar limit 20 offset 90420;
                                                                                                                                                   QUERY PLAN                                                                                                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------