$ docker run -it --rm --link postgres96beta4:postgres postgres:9.6 psql -h postgres -U postgres Password for user postgres: psql (9.6beta4) Type "help" for help.
postgres=# select version(); version --------------------------------------------------------------------------------------------- PostgreSQL 9.6beta4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit (1 row)
$ psql -h localhost -p 15432 -U postgres Password for user postgres: psql (9.3.5, server 9.6beta4) WARNING: psql major version 9.3, server major version 9.6. Some psql features might not work. Type "help" for help.
$ docker exec -it postgres96beta4 bash root@d2e991bd61a7:/# cd /var/lib/postgresql/data/ root@d2e991bd61a7:/var/lib/postgresql/data# more postgresql.conf と、このようにpostgresql.confを眺めてみたり(lessもviもなかった)。
root@d2e991bd61a7:/var/lib/postgresql/data# more pg_hba.conf pg_hba.confを眺めてみたり。pg_hba.confから抜粋しますと
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all trust # IPv4 local connections: host all all 127.0.0.1/32 trust # IPv6 local connections: host all all ::1/128 trust
host all all 0.0.0.0/0 md5 ローカルからはtrustで他はパスワード認証ですかね。
RUN localedef -i ja_JP -c -f UTF-8 -A /usr/share/locale/locale.alias ja_JP.UTF-8 ENV LANG ja_JP.utf8 日本語化されたイメージ、mypostgres:9.6を作ります。
$ docker build -t mypostgres:9.6 ./ 確認
$ docker images REPOSITORY TAG IMAGE ID CREATED SIZE mypostgres 9.6 4b7ea9f892dd 24 seconds ago 269.2 MB データベースの初期化操作しているディレクトリに/docker-entrypoint-initdb.d対応用にinitdbというディレクトリを作成しました。postgres/9.6/docker-entrypoint.sh を読んでみたところ
for f in /docker-entrypoint-initdb.d/*; do case "$f" in *.sh) echo "$0: running $f"; . "$f" ;; *.sql) echo "$0: running $f"; "${psql[@]}" < "$f"; echo ;; *.sql.gz) echo "$0: running $f"; gunzip -c "$f" | "${psql[@]}"; echo ;; *) echo "$0: ignoring $f" ;; esac echo done で順次処理となっているのでファイル名順に実行されるようです。sqlをgzip圧縮したsql.gz拡張子のファイルにも対応しているようですね。
初期化時にdockerユーザーとdockerテーブルを作成するshを作成します。
initdb/01adduser.sh
#!/bin/bash set -e
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL CREATE USER docker; ALTER USER docker WITH PASSWORD 'dockerpassword'; CREATE DATABASE docker; GRANT ALL PRIVILEGES ON DATABASE docker TO docker; EOSQL 次にsqlファイル内で\cでデータベースとユーザーをdockerに切り替えてテーブルを作ってみます。shでdb接続からしたほうが簡単な気がしますが、実験です。
initdb/02createtb.sql
\c docker docker
CREATE TABLE auth_group ( id integer NOT NULL, name character varying(80) NOT NULL );
COPY auth_group (id, name) FROM stdin; 1 Gyoumu 2 Master 3 System \. 日本語化したmypostgres:9.6からMacのinitdbディレクトリを/docker-entrypoint-initdb.dにバインドしてコンテナを起動します。
SELECT haisou_daityou.syouhin_detail_id, syouhin_detail.code as syouhin_detail_code, haisou_daityou.haisou_bi, date_part('day'::text, haisou_daityou.haisou_bi)::int as day, sum(haisou_daityou.haisou_suu) AS haisou_sum
FROM haisou_daityou LEFT OUTER JOIN syouhin_detail ON haisou_daityou.syouhin_detail_id = syouhin_detail.id
WHERE "YearMonth"(haisou_daityou.haisou_bi) = "YearMonth"('2016-03-01'::date)
GROUP BY haisou_daityou.syouhin_detail_id, syouhin_detail.code, haisou_daityou.haisou_bi
ORDER BY syouhin_detail.code, haisou_daityou.haisou_bi
結果
GroupAggregate (cost=1141834.38..1173281.22 rows=1048228 width=18) (actual time=30736.728..35349.575 rows=9393 loops=1) -> Sort (cost=1141834.38..1144454.95 rows=1048228 width=18) (actual time=30735.447..34864.067 rows=1024085 loops=1) Sort Key: syouhin_detail.code, haisou_daityou.haisou_bi, haisou_daityou.syouhin_detail_id Sort Method: external merge Disk: 30048kB -> Hash Left Join (cost=650914.54..994020.09 rows=1048228 width=18) (actual time=1812.137..5063.999 rows=1024085 loops=1) Hash Cond: (haisou_daityou.syouhin_detail_id = syouhin_detail.id) -> Bitmap Heap Scan on haisou_daityou (cost=650901.04..979593.45 rows=1048228 width=12) (actual time=1811.261..4238.580 rows=1024085 loops=1) Recheck Cond: ((((date_part('year'::text, (haisou_bi)::timestamp without time zone))::integer * 100) + (date_part('month'::text, (haisou_bi)::timestamp without time zone))::integer) = 201603) Rows Removed by Index Recheck: 191609 -> Bitmap Index Scan on haisou_daityou_tokuisaki_yearmonth_updated (cost=0.00..650638.98 rows=1048228 width=0) (actual time=1808.060..1808.060 rows=1024085 loops=1) Index Cond: ((((date_part('year'::text, (haisou_bi)::timestamp without time zone))::integer * 100) + (date_part('month'::text, (haisou_bi)::timestamp without time zone))::integer) = 201603) -> Hash (cost=8.78..8.78 rows=378 width=10) (actual time=0.847..0.847 rows=379 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 17kB -> Seq Scan on syouhin_detail (cost=0.00..8.78 rows=378 width=10) (actual time=0.007..0.697 rows=379 loops=1) Total runtime: 35426.470 ms
うわぁ。おーそーいー。開発環境なので本番と比べると非常に非力ではありますが…
特に
「Hash LEFT JOIN (cost=650914.54..994020.09 ROWS=1048228 width=18) (actual TIME=1812.137..5063.999 ROWS=1024085 loops=1)」
SELECT syouhin_detail.code as syouhin_detail_code, sum_tmp.* FROM ( SELECT haisou_daityou.syouhin_detail_id, haisou_daityou.haisou_bi, date_part('day'::text, haisou_daityou.haisou_bi)::int as day, sum(haisou_daityou.haisou_suu) AS haisou_sum
FROM haisou_daityou
WHERE haisou_daityou.haisou_bi BETWEEN '2016-03-01'::date AND '2016-03-31'::date
GROUP BY haisou_daityou.syouhin_detail_id, haisou_daityou.haisou_bi ) as sum_tmp
LEFT OUTER JOIN syouhin_detail ON sum_tmp.syouhin_detail_id = syouhin_detail.id
次にpsql dbnameでログインして、GRANTを掛けます。 GRANT ALL ON pg_ts_cfg TO PUBLIC; GRANT ALL ON pg_ts_cfgmap TO PUBLIC; GRANT ALL ON pg_ts_dict TO PUBLIC; GRANT ALL ON pg_ts_parser TO PUBLIC;
とりあえずテスト。 CREATE TABLE test_tb(testts tsvector); CREATE INDEX test_tb_testts ON test_tb USING gist (testts);
何事もなく作成されたので、データを入れてみて、SELECT。 データ中に2と4の含まれるデータを検索します。 SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2&4'); -- and検索 SELECT * FROM test_tb WHERE testts @@ to_tsquery('default', '2|4'); -- or検索
EXPLAINすると、INDEXを使用しているようです。 Bitmap Heap Scan on test_tb (cost=4.10..108.72 rows=27 width=32) Filter: (testts @@ ”’2” & ”4”’::tsquery) -> Bitmap Index Scan on test_tb_index (cost=0.00..4.10 rows=27 width=0) Index Cond: (testts @@ ”’2” & ”4”’::tsquery)
これを応用して、対象となるhogeテーブルのcol列にtsvector型に対応させたインデックスを作成します。 一応カンマからスペースに変更して、格納するようにreplaceしています。 CREATE INDEX hoge_col ON hoge USING gist (to_tsvector('default', replace(col,',',' '))); SELECT * FROM hoge WHERE to_tsvector('default', replace(col,',',' ')) @@ to_tsquery('default', '2|4');
$ sudo -u postgres vim /Library/PostgreSQL/8.4/data/postgresql.conf
で、眺めると、全てのアドレスでlistenはしてるようです。
listen_addresses = '*' # what IP address(es) to listen on;
修正は必要ありませんでした。
次、pg_hba.confを同じくpostgresさんになりきってエディターで開きます。
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
# "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 # Win7 on Fusion3 connections host all all 192.168.249.0/24 trust
