pg_bigmインストールメモ

事前に資材（pg_bigm-20130405.tar.gz）をあげておく
WINSCPで/usr/local/src/ディレクトリにpg_bigm-20130405.tar.gzをアップロード

■インストール
su
cd /usr/local/src/
tar zvxf pg_bigm-20130405.tar.gz
cd pg_bigm-20130405
make USE_PGXS=1 PG_CONFIG=/opt/powergres91/bin/pg_config
make USE_PGXS=1 PG_CONFIG=/opt/powergres91/bin/pg_config install


・postgres.confの最終行にでも下記２行を追加する
shared_preload_libraries = 'pg_bigm'
custom_variable_classes = 'pg_bigm'

・修正後にpostgresを再起動する
・postgresに接続

xxxxxx=# CREATE EXTENSION pg_bigm;
CREATE EXTENSION
xxxxxx=# \dx pg_bigm
                    List of installed extensions
  Name   | Version | Schema |              Description
---------+---------+--------+---------------------------------------
 pg_bigm | 1.0     | public | text index searching based on bigrams
(1 row)

(※\dxが使えない場合は。。
test=> select * from pg_extension;
 extname | extowner | extnamespace | extrelocatable | extversion | extconfig | e
xtcondition
---------+----------+--------------+----------------+------------+-----------+--
------------
 plpgsql |       10 |           11 | f              | 1.0        |           |
 pg_bigm |       10 |         2200 | t              | 1.0        |           |
(2 行)
）

CREATE EXTENSION pg_bigm;
DROP EXTENSION pg_bigm CASCADE;


CREATE INDEX idx_entry_body_gin_bigm ON entry  USING gin (body gin_bigm_ops);
DROP INDEX idx_entry_body_gin_bigm;
CREATE INDEX idx_entry_title_gin_bigm ON entry  USING gin (title gin_bigm_ops);
DROP INDEX idx_entry_title_gin_bigm;
CREATE INDEX idx_entry_extend_gin_bigm ON entry  USING gin (extend gin_bigm_ops);
DROP INDEX idx_entry_extend_gin_bigm;


CREATE INDEX idx_test_three ON entry  USING gin (body gin_bigm_ops,title gin_bigm_ops,extend gin_bigm_ops);
DROP INDEX idx_test_three;

■アンインストール
$ psql -d <データベース名>
=# DROP EXTENSION pg_bigm CASCADE;
=# \q

$ pg_ctl -D $PGDATA stop
$ su

# cd <pg_bigmのソースディレクトリ>
# make USE_PGXS=1 PG_CONFIG=/opt/pgsql-9.1.X/bin/pg_config uninstall
# exit



■pg-bigm
xxxxxx=# explain analyze select * from entry where body like'%ああ%';
                                                       QUERY PLAN

--------------------------------------------------------------------------------
-----------------------------------------
 Bitmap Heap Scan on entry  (cost=12.34..66.99 rows=44 width=173) (actual time=0
.036..0.056 rows=28 loops=1)
   Recheck Cond: (body ~~ '%ああ%'::text)
   ->  Bitmap Index Scan on idx_test_body  (cost=0.00..12.33 rows=44 width=0) (a
ctual time=0.026..0.026 rows=28 loops=1)
         Index Cond: (body ~~ '%ああ%'::text)
 Total runtime: 0.099 ms
(5 rows)

xxxxxx=# explain analyze select * from entry where body like'%あああ%';
                                                       QUERY PLAN

--------------------------------------------------------------------------------
-----------------------------------------
 Bitmap Heap Scan on entry  (cost=12.28..63.46 rows=36 width=173) (actual time=0
.035..0.070 rows=26 loops=1)
   Recheck Cond: (body ~~ '%あああ%'::text)
   ->  Bitmap Index Scan on idx_test_body  (cost=0.00..12.27 rows=36 width=0) (a
ctual time=0.023..0.023 rows=28 loops=1)
         Index Cond: (body ~~ '%あああ%'::text)
 Total runtime: 0.110 ms
(5 rows)


■indexなし
xxxxxx=# explain analyze select * from entry where body like'%あああ%';
                                             QUERY PLAN

--------------------------------------------------------------------------------
--------------------
 Seq Scan on entry  (cost=0.00..85.10 rows=36 width=173) (actual time=0.059..2.9
49 rows=26 loops=1)
   Filter: (body ~~ '%あああ%'::text)
 Total runtime: 3.016 ms
(3 rows)

xxxxxx=# explain analyze select * from entry where body like'%ああ%';                                               QUERY PLAN

--------------------------------------------------------------------------------
--------------------
 Seq Scan on entry  (cost=0.00..85.10 rows=44 width=173) (actual time=0.058..2.9
03 rows=28 loops=1)
   Filter: (body ~~ '%ああ%'::text)
 Total runtime: 2.961 ms
(3 rows)