はじめに
＜ にゃーん 趣味でポスグレをやってる者だ
この記事はPostgreSQL Advent Calendar 2019 22日目の記事です。
昨日は @U_ikki さんのautovacuumチューニングの記事でした。
みんな、そういった役にたつ記事を書いている中、自分は今年もPostgreSQLを色々使って日々のQON（Quality of Noodle)を上げる話を書きます。こんな記事でごめんね。
俺々ラーメンデータベース
去年のAdvent Calendarでも書いた(麺とポスグレと私)、俺々ラーメンデータベースですが、あれからも色々改良を加えてみました。
- ラーメン以外(カレー、チャーハン、ぎょうざ、うどん、そば)のカテゴリの情報も収集
- 店舗のカテゴリ分類を追加
- 各レビューのテキストの収集
等々。
なんとかデータベースからの収集スクリプトやDDL等は、図々しくもGithub上に置いてあります。
https://github.com/nuko-yokohama/ramendb
テーブル構成
今はこんな感じのテーブル構成になっています。
各テーブルの件数はこんな感じ。そんなに巨大なデータベースじゃないです。
| テーブル名 | 内容 | 件数(概数) |
|---|---|---|
| shops | レビュー登録対象となる店舗の情報 所在地や登録可能なレビューのカテゴリなどの情報も持っている。 |
10万件 |
| reciews | レビューのメタ情報。カテゴリ、点数、登録したユーザIDなどの情報を持つ。 | 88万件 |
| users | レビューを登録するユーザ情報。ユーザ名などの情報を持つ。 | 20万件 |
| comments | レビューに対するコメント情報。レビューID、レビュー登録者のユーザID、コメントした人のユーザID | 140万件 |
| reciews_text | レビュー本文。 | 88万件 |
なお、全くDurabilityを考えていない＆データロードの高速化のために、総てのテーブルはUNLLOGED TABLEで作成しています(なので、この環境で自作EXTENSIONをデバッグしたりすると、バグ発生→クラッシュリカバリ→全データロスト！とか、よく起きますが気にしない）
もちろん、バックアップとかレプリケーションもなーんも考えてません。再ロードすればいいのだから。
インデックス
インデックスは必要に応じて**(あるいは気分で)**作成・削除します。
たいていはなーんも考えずに良く使う検索キーにbtreeインデックスを設定してますが、ただ、以下の列については、PostgreSQL固有のちょっと変わったインデックスを気分によって設定していたります。
- 店舗情報(shops)の「tags」にGINインデックスを設定しています。
- 店舗情報(shops)の「category」にGINインデックスを設定しています。
- 店舗情報(shops)やレビュー(reviews)の登録日付(reg_date)は、性質上correlation1が1に近い値になるので、BRIN(Block Range INdex)を使っています(btreeよりも容量が節約でき、かつ範囲検索時に効果が高いので)。
- レビューテキストに対して、pg_bigmで検索するときの全文インデックス(GINインデックス)を設定します。
EXTENSION
EXTENSIONに関してはLIKE検索の高速化のために、pg_bigmを入れたり入れなかったりします。最近は正規表現検索を使うことが多いですが・・・。
使用するPostgreSQLバージョン
気分によって、以下のバージョンを切り替えてます。
- PostgreSQL 13 devel
- PostgreSQL 13 with Incremental View Maingtenance patch
- PostgreSQL 12 with zheap patch
- PostgreSQL 12
全データをロードしても10分もかからないので、カジュアルにTRUNCATE→ロードしてます。
家系を求めて
家系といっても（かけい）じゃなくて（いえけい）です。神奈川県発祥の豚骨醤油系のラーメンのことを家系と称しています。2 もう今では全国にも広がってますよね。
さて、「家系」という名前のように、この系列の店舗の屋号末尾には「家」がつくことが多いのですが、新興の家系店だと「家」がつかないこともあるし、また逆に末尾に「家」がつくけど家系ではないお店もたまにあります。つまり、店舗名の情報だけで「家系店」か判断できないわけです。なんて面倒な。
幸い、収集元のサイトでは家系店を示すタグをチェックボックスで指定して登録できます。他にタグとして「自家製麺」「無化調」とか、ラーメン好きな人なら反応しそうな用語がありますね。
なので、その情報も店舗情報の一部として収集することにしました。
タグ情報のようなものは、今後、追加される可能性が高いので、個々の列として定義せずにTEXT型の配列として管理するようにします。
で、検索する場合には、例えば「家系」タグを含む店舗を検索したい、みたいな使い方をします。
また、他のタグと組み合わせた検索もできます。以下は「自家製麺」かつ「家系」の横浜市内の店舗を検索する例です。tagsというTEXT配列と、'{自家製麺,家系}'というTEXT配列を@<という演算子で評価しているのがポイントです。
ramendb=# SELECT name, tags
FROM shops
WHERE tags @> '{自家製麺,家系}'
AND area ~ '横浜市.*区'
;
name | tags
-------------------------------+-----------------------------------------------
餃子の翠葉 セントラルキッチン | {家系,自家製麺}
大黒家商店 | {家系,二郎系,自家製麺}
家系ラーメン王道 王道之印 | {家系,自家製麺,子供メニューあり,子供イスあり}
横浜家系ラーメン 町田商店 | {家系,自家製麺}
(4 rows)
単なる文字列として条件を与えた場合、「自家製麺,家系」という記述順序も含めて一致しないとヒットしませんが、上記のような例の場合、配列と配列の包含関係の比較（「自家製麺」「家系」の両方の要素を含む配列）になるため、こうした記述順序に依存しない検索が可能になります。
また、tagsにGINインデックスを設定してあると、上記のような検索時にもきちんとインデックスを使った検索をしてくれるようになります。便利ですね。
QUERY PLAN
----------------------------------------------------------------------------
Bitmap Heap Scan on shops (cost=29.50..1705.73 rows=28 width=62)
Recheck Cond: (tags @> '{自家製麺,家系}'::text[])
Filter: (area ~ '横浜市.*区'::text)
-> Bitmap Index Scan on shops_tags (cost=0.00..29.49 rows=732 width=0)
Index Cond: (tags @> '{自家製麺,家系}'::text[])
(5 rows)
日々家系を求めて
俺々ラーメンデータベースですが、数日に1回、自分の気が向いたときにクローラを動かして、店舗情報やレビュー情報を収集して、それをPostgreSQLにロードします。
現在は、Incremental Materialized Viewが使える開発中のバージョンをPostgreSQLを使っており、このIncremental Materialized View機能を使って、神奈川県内の家系の最新レビューを簡単に参照できるようにしています。
（通常のビューでも問題ない気はするけど、Incremental Materialized Viewのテストのために使っている）
ramendb=# \d+ kanagawa_house_reviews
Materialized view "public.kanagawa_house_reviews"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------+---------+-----------+----------+---------+----------+--------------+-------------
sid | integer | | | | plain | |
area | text | | | | extended | |
name | text | | | | extended | |
branch | text | | | | extended | |
rid | integer | | | | plain | |
menu | text | | | | extended | |
score | integer | | | | plain | |
reg_date | date | | | | plain | |
__ivm_count__ | bigint | | | | plain | |
View definition:
SELECT s.sid,
s.area,
s.name,
s.branch,
r.rid,
r.menu,
r.score,
r.reg_date
FROM reviews r
JOIN shops s ON r.sid = s.sid
WHERE s.pref = '神奈川県'::text AND s.tags @> '{家系}'::text[] AND s.status = 'open'::text AND r.category = 'ラーメン'::text;
Access method: heap
Incremental view maintenance: yes
このビューはマテリアライズド・ビューなんですが、ビュー元の更新差分を元に自動追随する機能を持っているので、一旦ビューを定義しておけば、さくっと検索できるようになっています。
自分の場合、クエリ実行から3日以内に登録された家系レビューの情報を見て、最新の家系事情(新店舗登録や新メニュー情報など)を知る助けにしています。
ramendb=# SELECT * FROM kanagawa_house_reviews
WHERE reg_date >= (now() - '5 days'::interval)
ORDER BY reg_date DESC
;
sid | area | name | branch | rid | menu | score | reg_date
--------+----------------+-------------------------+--------+---------+-----------------------------------+-------+------------
17702 | 大和市 | 壱六家 | 大和店 | 1285858 | ラーメン（中盛） | 70 | 2019-12-20
1614 | 横浜市港南区 | 環2家 | | 1285766 | ラーメン￥750 | 82 | 2019-12-20
116427 | 横浜市西区 | 壱六家 | 横浜店 | 1285962 | ラーメン 並 固め濃いめ | 90 | 2019-12-20
1614 | 横浜市港南区 | 環2家 | | 1285457 | ラーメン きくらげ ほうれん草 | 89 | 2019-12-18
72495 | 横浜市神奈川区 | 家系ラーメン とらきち家 | | 1285472 | 野菜らーめん、賄い飯 | 95 | 2019-12-18
72495 | 横浜市神奈川区 | 家系ラーメン とらきち家 | | 1285510 | 野菜ラーメン＋半賄い飯 | 100 | 2019-12-18
1852 | 横浜市神奈川区 | おーくら家 | 本店 | 1285507 | ラーメン | 70 | 2019-12-18
1055 | 横浜市港北区 | 極楽汁麺 らすた | | 1285419 | らすた麺 900円 | 81 | 2019-12-18
1611 | 横浜市西区 | 家系総本山 吉村家 | | 1285465 | 中盛チャーシュー麺970円 味玉50円 | 96 | 2019-12-18
2473 | 横浜市西区 | らーめんありがた家 | | 1285247 | ラーメン、ライス、味玉 | 79 | 2019-12-17
(10 rows)
Incremental Materialized Viewに関しては、今月開催の別のアドベントカレンダーの中で、自分が紹介記事を書いているので興味のある方は見ていただけると幸いです。
蕎麦屋さんのラーメン
実は、最近気になっているのが「蕎麦屋さんで提供しているラーメン」だったりします。今どきのラーメンにない素朴さや、蕎麦屋さん独自の解釈によるラーメンが面白いんですよね。
（当たり前ですが）ラーメンも提供している蕎麦屋さんというのはそんなに多くないので、きちんと探さないといけません。
かといって、「蕎麦屋さんのラーメン」の気分のときには、ラーメンもそばもチャーハンも餃子も提供している食堂のラーメンは除外したい（食堂のラーメンも嫌いじゃないけど）。
そういうときに使えるのがPostgreSQL 9.4から導入されたJSONB型です。
現在の店舗情報(shops)にはcategoryというJSONB型の列が追加されています。これも個々の列にしなかったのは、将来カテゴリの追加(丼ものやパスタなど)が有り得そうだからです。とりま、JSONB型の列にしておけばスキーマの変更なしに対応できるはず。
さて、JSONB型のcategoryには、こんな感じのJSONデータが格納されています。
ramendb=# SELECT name, category FROM shops ORDER BY sid DESC LIMIT 5;
name | category
--------------------------+-------------------------------------------------------------------------------------------------------------
ラーメン エボシ | {"sobadb": false, "udondb": false, "currydb": false, "ramendb": true, "chahandb": false, "gyouzadb": false}
ramenる | {"sobadb": false, "udondb": false, "currydb": false, "ramendb": true, "chahandb": false, "gyouzadb": false}
中華そば 四つ葉 | {"sobadb": false, "udondb": false, "currydb": false, "ramendb": true, "chahandb": false, "gyouzadb": false}
新亜飯店 | {"sobadb": false, "udondb": false, "currydb": false, "ramendb": true, "chahandb": true, "gyouzadb": false}
レインボーダイニングバー | {"sobadb": false, "udondb": false, "currydb": true, "ramendb": false, "chahandb": false, "gyouzadb": false}
(5 rows)
categoryに格納するJSONは、ramendb, currydb, chahandb, gyouzadb, udondb, sobadb のキーを持ち、そのキーに対応する値はboolean型(true or false)にしています。
で、categoryに対して、JSONB演算子@> JSONBデータを使うことで「蕎麦とラーメンは提供してるけど、炒飯と餃子は提供していない」店舗の検索ができるようになります。
以下は、横浜市中区で営業している、「ラーメンを提供する蕎麦屋さん」の検索例です。
ramendb=# SELECT area, name, branch
FROM shops
WHERE area ~ '横浜市中区'
AND status = 'open'
AND category @> '{"ramendb":true, "sobadb":true, "chahandb":false, "gyouzadb":false}'
LIMIT 5
;
area | name | branch
------------+---------------------+------------------
横浜市中区 | 名代 富士そば | 伊勢佐木モール店
横浜市中区 | そば処 富士美 |
横浜市中区 | 小島屋 |
横浜市中区 | レストラン ウェーブ |
横浜市中区 | ゆで太郎 | 長者町店
(5 rows)
EXPLAINをとってみると、こういう検索の場合にもGINインデックスが使われて検索の高速化がされています。このへんがJSON型よりJSONB型が優位なところですね。
ramendb=# EXPLAIN SELECT area, name, branch
FROM shops
WHERE area ~ '横浜市中区'
AND status = 'open'
AND category @> '{"ramendb":true, "sobadb":true, "chahandb":false, "gyouzadb":false}'
LIMIT 5
;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Limit (cost=544.80..909.26 rows=1 width=37)
-> Bitmap Heap Scan on shops (cost=544.80..909.26 rows=1 width=37)
Recheck Cond: (category @> '{"sobadb": true, "ramendb": true, "chahandb": false, "gyouzadb": false}'::jsonb)
Filter: ((area ~ '横浜市中区'::text) AND (status = 'open'::text))
-> Bitmap Index Scan on shops_category (cost=0.00..544.80 rows=107 width=0)
Index Cond: (category @> '{"sobadb": true, "ramendb": true, "chahandb": false, "gyouzadb": false}'::jsonb)
(6 rows)
ついでに集計
せっかくラーメンを提供する蕎麦屋さんの検索ができるようになったので、全国の店舗を調べて都道府県別にカウントし上位5都道府県を表示してみます。
ramendb=# SELECT pref, COUNT(*)
FROM shops
WHERE status = 'open'
AND category @> '{"ramendb":true, "sobadb":true, "chahandb":false, "gyouzadb":false}'
GROUP BY pref
ORDER BY COUNT(*) DESC
LIMIT 5;
pref | count
----------+-------
東京都 | 565
山形県 | 161
神奈川県 | 141
北海道 | 138
埼玉県 | 134
(5 rows)
東京、神奈川のように人口・店舗が多い都道府県が出るのは当然として、山形県がランクインしているというのが面白いです。
実は、山形県では蕎麦屋さんでラーメンを提供するのは、わりと当たり前のことらしいのです。
山形のラーメンを考える上で、最も特徴的な事としてあげられるのは、なんと言っても「そば屋でラーメン（中華そば）を出す」という事でしょう。
（ヤマガタ未来ラボ「日本一ラーメン大好き山形県。だけど山形ラーメンって？ 」より引用）
今回、catoegoryを収集して検索してみた結果も、それを示すものになっていて興味深いものがあります。
うどんは？
ごめんなさい。うどんにはそんなに興味はないんです。
玉葱を避ける
自分は結構、タンメンやサンマーメン3といった、野菜炒めを乗せたタイプのラーメンも好きなので、ちょくちょく食べに行くのですが、そこで悩ましいのが玉葱の存在です。
玉葱がどうにも苦手で(猫に玉葱を与えてはいけません)、できるなら玉葱が入っていないタンメンや生碼麺を食べたいところ。
週末にちょっと遠くまで遠征してタンメンや生碼麺を食べようとしたときに、玉葱が入っているのがどれだけ哀しいことか。
全レビューテキストを収集するようになったきっかけはそれです。
レビューを書く人にもよりますが、こういう野菜炒め系を乗せたラーメンの場合、どういった具材を使っているかを書く人がそれなりに多いです。
つまり、以下のようなクエリを書くことで、玉葱を具として入れていないサンマーメンを提供している店を探すことができるはずです。
- レビュー情報(reviews)のmenu名からサンマーメンを含むレビューID(rid)を取り出す。
- 1.でレビューが登録された店舗のID(sid)のリスト1を求める。
- レビューテキスト(review_text)のdata（レビュー本文ね）から、玉葱を含むレビューID(rid)を取り出す。
- 3.でレビューが登録された店舗のID(sid)のリスト2を求める。
- リスト1に合致する店舗かつ、リスト2に合致しない店舗を見つける。後は任意の条件を付与。
実際にはサンマーメンの表記揺れ、玉葱の表記揺れに対応するために、ちょいと面倒なクエリを書く必要があります。実際のクエリ例はこんな感じ。
（横浜市内にある玉葱を含まないサンマーメンを提供する店舗から評価(point)が高いもの上位10軒を検索)
ramendb=# WITH sanma_shop AS (
SELECT sid
FROM reviews
WHERE menu ~ '.*((サンマ(ー|～)*|さんま)(ー)*|生(馬|碼))(ラーメン|メン|めん|麺|面).*'
),
onion_sanma_shop AS (
SELECT sid
FROM reviews r JOIN reviews_text t ON (r.rid = t.rid)
WHERE data ~ '.*(玉|たま|タマ)(葱|ねぎ|ネギ).*'
)
SELECT sid, area, name FROM shops s
WHERE sid IN (SELECT sid FROM sanma_shop) AND sid NOT IN (SELECT sid FROM onion_sanma_shop)
AND pref = '神奈川県' AND area ~ '横浜市.*区'
AND status = 'open'
ORDER BY point DESC LIMIT 10;
sid | area | name
-------+------------------+-----------------------------
20257 | 横浜市南区 | 酔来軒
97124 | 横浜市中区 | 三幸苑
4420 | 横浜市西区 | タンメンワールド 横濱一品香
6652 | 横浜市神奈川区 | 南京亭
2747 | 横浜市戸塚区 | 麺工房ジロー
18575 | 横浜市中区 | 福満園
11587 | 横浜市保土ヶ谷区 | 横濱一品香
5402 | 横浜市緑区 | ギョーザ・ラーメン 華
31961 | 横浜市中区 | 三貴屋
51820 | 横浜市瀬谷区 | らーめん 花楽
(10 rows)
これでだいぶ玉葱避けができるようになりそうです。にゃーん。
真・町田は神奈川
重大な食い違いがある場合、まちがっているのはつねに現実のほうなのだ。(ダグラス・アダムス「宇宙の果てのレストラン」)
去年も書きましたが、町田市に属する店舗の県庁所在地が東京都として収集されてしまう課題は解決していません。収集スクリプトで内で変えたり、収集したファイルを直接変更してもいいんだけど、なんかそれも面倒なので、やっぱりビューで解決します。
ビュー元のテーブル（とスクレイピング元のサイトでは）町田市が東京都に属しています。これが現実？いやいや、現実というものはしょっちゅう間違えているものです。
なので、正しい姿をビューで提供する必要があります。
実は昨年度のビュー＋ユーザ定義関数方式では、真の「町田は神奈川」化ができませんでした。よろしくありません。今年はCASE WHEN関数を使って解決することにしました。4
CREATE VIEW shops_idea AS
SELECT shops.sid,
shops.status,
shops.name,
shops.branch,
CASE
WHEN shops.pref = '東京都' AND shops.area = '町田市' THEN '神奈川県'
ELSE shops.pref
END AS pref,
shops.area,
shops.insert_uid,
shops.update_uid,
shops.category,
shops.point,
shops.tags,
shops.reg_date
FROM shops;
こうして作成したビューshops_ideaに対してpref = '神奈川県'の条件を付与すると、きちんと町田市の店舗情報が検索されます。prefの表示も「神奈川県」になります。
元のテーブル(shops)に対してpref = '神奈川県' AND area = '町田市'という条件を付与すると1件もヒットしないという悲しい結果になりますが、
ramendb=# SELECT pref, area, name
FROM shops
WHERE pref = '神奈川県' AND area = '町田市'
LIMIT 3
;
pref | area | name
------+------+------
(0 rows)
作成したビュー(shops_idea)に対して同様の条件を付与すると、あるべき検索結果が出てきます。
ramendb=# SELECT pref, area, name
FROM shops_idea
WHERE pref = '神奈川県' AND area = '町田市'
LIMIT 3
;
pref | area | name
----------+--------+---------------------
神奈川県 | 町田市 | 雷文
神奈川県 | 町田市 | 横浜ラーメン 町田家
神奈川県 | 町田市 | 胡心房
(3 rows)
間違った現実(町田市が東京都に属している)が修正されれば、こういうビューを作らなくてもすむんですけどね。
今後の課題
自分のQONを上げるため、今後こういうこともやってみたいなと考えています。
- より柔軟なレビューテキストの高速検索(Groonga + pgroonga組み込みかなあ・・・)
- スパ銭近所のラーメン店のリストアップ(PostGISの利用)
- 類似画像検索によるラーメン店のリストアップ(このへんは勘所が良くわからない・・・)
- テキスト要約のライブラリを組み込んで、他人のレビューテキストを要約・表示する。
- 無駄に長いレビューを読みたくないので。
- 自分のレビューテキストとスコアを元に学習させておいて、他の人のレビューテキストからスコアを算出する
- 以前、Jubatusで試行したことがあったけど、それを本格的に(できればSQL関数でラップして)利用できるようにする。
- グラフデータベース(Neo4jとの連携
- ロジカルデコーディングを使った同期方式の改良(logideco2neo4jの作り直し含む
- neo4j_fdwをPostgreSQL最新版にアップデート
おわりに
PostgreSQLはエンタープライズ用途でも使える、優秀なDBMSですが、個人でちょこっと使う分にもいろいろ便利なDBMSでもあります。これからも仕事でもプライベートでもPostgreSQLをガンガン使っていきたいですねー。
明日は @kingtomo1122 さんの記事です。今年は何を書いてくれるかなー。
-
pg_statsシステムビューのcorrelationを参照。 ↩
-
家系ラーメンの詳細についてはWikipediaページ「家系ラーメン」を参照。 ↩
-
生碼麺の詳細については「かながわサンマーメンの会」のページを参照。 ↩
-
PostgreSQL 12から導入されたPluggable-Storage機構を使って、「東京都」「町田市」という文字列を含むタプルが存在した場合、Scan結果を「神奈川県」「町田市」に変換する、町田ストレージエンジンの開発も考えたこともありますが、労力の割にあまりにもアレなので開発は中断しました。 ↩