2014年1月16日木曜日

PostgreSQL 9.3 の JSON データのインデックス

こんにちは。渡辺です。

前回は PostgreSQL 9.2 で実装された JSON サポートの機能を振り返ってみましたが、今回は PostgreSQL 9.3 で強化された JSON サポートの機能が、どのように利用できるようになったか確認してみます。

PostgreSQL 9.3 から JSON データ型のサポートが強化され、JSON データ型のオペレータや関数が利用できる様になりました。

PostgreSQL 9.2 では JSON データにインデックスを利用するには PLV8 を利用する必要がありました。PostgreSQL 9.3 からはオペレータ等を利用して JSON データに高速にアクセスできるようになります。

■PostgreSQL 9.2 での JSON データのインデックス

PostgreSQL はインデックスに表現や関数をサポートしていますが、JSON 型はオペレータや関数を持たなかった為、インデックスを簡単に利用する事はできませんでした。JSON 型をテキスト型にする事は可能でしたが、テキスト型へ変換してもインデックスを効率よく利用する事はできません。

Javascript エンジンである V8 でストアドプロシージャを作成できる PLV8 を利用して関数を作成すると、効率よいインデックスを作る事ができました。

  • PLV8 を利用したインデックス作成用の JSON 関数例

    CREATE or REPLACE FUNCTION my_json_function (j json, key text )
     RETURNS text
     LANGUAGE plv8 
     IMMUTABLE
    AS $function$
      var ej = j;
      if (typeof ej != 'object')
            return NULL;
      return JSON.stringify(ej[key]);
    $function$;
    
  • インデックスの作成

    CREATE INDEX key_in_json ON mytable (my_json_function(json_data,'key'));
    

PLV8 とストアドプロシージャが必要であり、簡単に利用できるとは言えませんでした。


■PostgreSQL 9.3 での JSON データのインデックス

PostgreSQL 9.3 からは JSON 型のオペレータと関数を利用し、JSON 型のデータ内のフィールドに簡単にインデックスを利用できるようになります。

JSON 型は BTree のインデックスをサポートしていませんが、要素をテキストとして返してインデックスすると高速に検索できます。(注意:JSON 型をインデックスに利用するとエラーとなりインデックスを作成できません。)

  • JSON オペレータを利用した JSON 型のインデックス作成例

    CREATE INDEX json_index ON mytable (json_data->>'key');
    

    備考 : '->>' オペレータは JSON 型のデータの中から 'key' 要素をテキストとして取り出します。

  • JSON 関数を利用した JSON 型のインデックス作成例

    CREATE INDEX json_index ON mytable ((json_extract_path_text(json_data, 'key')));
    

    備考 : json_extract_path_text() は JSON 型のデータから 'key' をパスとするデータをテキストとして返します。

■ベンチマーク

[環境]

OS Linux (Fedora 19 を利用)
DB PostgreSQL 9.3 (git 版)
PHP PHP 5.5.2 (Fedora 19 の PHP) pgsql、mbstring モジュール必須

JSON 型のインデックスが高速に動作するか、簡単な PHP のベンチマークスクリプトを利用してベンチマークしてみます。利用するデータは郵便番号データを利用します。

郵便番号データ(全県一括、約 12 万件)
http://www.post.japanpost.jp/zipcode/dl/kogaki.html

ベンチマークスクリプトを実行するには、このサイトから ken_all.lzh をダウンロードしてください。ベンチマークに利用する PHP スクリプトは解凍済みの ken_all.csv を利用します。予め解凍してください。

import_zip_code.php スクリプトで郵便番号 CSV ファイルをデータベースに作成します。テーブル作成権限を持つアカウントを利用する必要があります。スクリプト最初の DBCONN 定数で接続 DB やユーザを調整してください。

PostgreSQL 9.3 のデフォルトの postgresql.conf を利用するとデータ挿入に時間がかかります。import_zip_code.php の実行前に、shared_buffer を 1GB 以上したり、fsync をオフにするなどの調整をする事をお勧めします。

import_zip_code.php

<?php
// 環境に合わせて調整してください。
// PostgreSQL は 9.3 以上でなければなりません。
const DBCONN = 'host=localhost port=5493';
const ZIPCSV = 'ken_all.csv';

$db = pg_connect(DBCONN);

// Create table and indexes
// Warning. This drops existing zip_code table in DB.
pg_query($db, "
DROP TABLE zip_code;
CREATE TABLE zip_code (
  id SERIAL NOT NULL,
  zip  TEXT NOT NULL,
  data JSON NOT NULL
);
CREATE INDEX zip_text ON zip_code (zip);
CREATE INDEX zip_json_op ON zip_code ((data->>'zip'));
CREATE INDEX zip_json_fn ON zip_code ((json_extract_path_text(data,'zip')));
");

// Get ZIP CSV and store data into PostgreSQL table
// Work around str_getcsv() limitation by processing line by line
// Simply ignore invalid data
$zips = mb_convert_encoding(file_get_contents(ZIPCSV), "UTF-8", "SJIS");
$zips = explode("\r\n", strtr($zips, ['"'=>'']));
$zip_objects = array_map(
       function($line) {
        $a = str_getcsv($line);
        // Use sevral fieilds
        $o = new StdClass;
        $o->zip_old = @$a[1];
        $o->zip = @$a[2];
        $o->pref_kana = @$a[3];
        $o->city_kana = @$a[4];
        $o->area_kana = @$a[5];
        $o->pref = @$a[6];
        $o->city = @$a[7];
        $o->area = @$a[8];
        return $o;
       },
       $zips);

pg_prepare($db, 'zip_insert', '
INSERT INTO zip_code (zip, data) VALUES ($1, $2);
');

$cnt = 0;
foreach($zip_objects as $o) {
 if (!$o->zip) {
  continue;
 }
 if (!(++$cnt%1000)) {
  echo $cnt.PHP_EOL;
 }
 pg_execute('zip_insert', [$o->zip, json_encode($o, JSON_UNESCAPED_UNICODE)]);
}

echo 'Done'.PHP_EOL;

スクリプトを実行すると zip_code テーブルとインデックスが作成されます。

インデックスは比較の為に、テキスト型の郵便番号インデックス、JSON 型オペレータを利用したインデックス、JSON 型関数を利用したインデックスの 3 つのインデックスを作成します。


[テーブル定義]

user@localhost ~=# \d zip_code;
                       テーブル "public.zip_code"
  列  |   型    |                        修飾語                         
------+---------+-------------------------------------------------------
 id   | integer | not null default nextval('zip_code_id_seq'::regclass)
 zip  | text    | not null
 data | json    | not null
インデックス:
    "zip_json_fn" btree (json_extract_path_text(data, VARIADIC ARRAY['zip'::text]))
    "zip_json_op" btree ((data ->> 'zip'::text))
    "zip_text" btree (zip)

[挿入されたデータの一部]

   id   |   zip   |                                                                                                                                              data                                                                                                                                              
--------+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
      1 | 0600000 | {"zip_old":"060  ","zip":"0600000","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"イカニケイサイガナイバアイ","pref":"北海道","city":"札幌市中央区","area":"以下に掲載がない場合"}
      2 | 0640941 | {"zip_old":"064  ","zip":"0640941","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"アサヒガオカ","pref":"北海道","city":"札幌市中央区","area":"旭ケ丘"}
      3 | 0600041 | {"zip_old":"060  ","zip":"0600041","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"オオドオリヒガシ","pref":"北海道","city":"札幌市中央区","area":"大通東"}
      4 | 0600042 | {"zip_old":"060  ","zip":"0600042","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"オオドオリニシ(1-19チョウメ)","pref":"北海道","city":"札幌市中央区","area":"大通西(1?19丁目)"}
      5 | 0640820 | {"zip_old":"064  ","zip":"0640820","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"オオドオリニシ(20-28チョウメ)","pref":"北海道","city":"札幌市中央区","area":"大通西(20?28丁目)"}
      6 | 0600031 | {"zip_old":"060  ","zip":"0600031","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"キタ1ジョウヒガシ","pref":"北海道","city":"札幌市中央区","area":"北一条東"}
      7 | 0600001 | {"zip_old":"060  ","zip":"0600001","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"キタ1ジョウニシ(1-19チョウメ)","pref":"北海道","city":"札幌市中央区","area":"北一条西(1?19丁目)"}
      8 | 0640821 | {"zip_old":"064  ","zip":"0640821","pref_kana":"ホッカイドウ","city_kana":"サッポロシチュウオウク","area_kana":"キタ1ジョウニシ(20-28チョウメ)","pref":"北海道","city":"札幌市中央区","area":"北一条西(20?28丁目)"}

benchmark_json_index.php は作成されたインデックスを利用して zip_code テーブルを検索します。最初に全ての郵便番号を取得し順番をランダムにシャッフルします。

その後、それぞれのインデックスに対してシャッフルした郵便番号を全て検索します。


benchmark_json_index.php

<?php
// 環境に合わせて調整してください。
// PostgreSQL は 9.3 以上でなければなりません。
const DBCONN = 'host=localhost port=5493';

$db = pg_connect(DBCONN);
// Get all zip
$res = pg_query($db, 'SELECT zip FROM zip_code');;
$zips = pg_fetch_all($res);
shuffle($zips);

pg_prepare($db, 'zip_select_text', "
SELECT zip FROM zip_code WHERE zip = $1;
");
pg_prepare($db, 'zip_select_json_op', "
SELECT zip FROM zip_code WHERE data->>'zip' = $1;
");
pg_prepare($db, 'zip_select_json_fn', "
SELECT zip FROM zip_code WHERE json_extract_path_text(data,'zip') = $1;
");


echo 'Start selecting all zip codes...'.PHP_EOL;

$start = microtime(true);
foreach($zips as $zip) {
 pg_execute($db, 'zip_select_text', [$zip['zip']]);
}
echo 'Text index: '.(microtime(true) - $start).PHP_EOL;

$start = microtime(true);
foreach($zips as $zip) {
 pg_execute($db, 'zip_select_json_op', [$zip['zip']]);
}
echo 'Json operator index: '.(microtime(true) - $start).PHP_EOL;

$start = microtime(true);
foreach($zips as $zip) {
 pg_execute($db, 'zip_select_json_fn', [$zip['zip']]);
}
echo 'Json function index: '.(microtime(true) - $start).PHP_EOL;

echo 'Done.'.PHP_EOL;

[ベンチマーク結果 (3 回実行)]

[user@dev SIOS]$ php benchmark_json_index.php 
Start selecting all zip codes...
Text index: 9.7189691066742
Json operator index: 9.8972401618958
Json function index: 9.8723850250244
Done.
[user@dev SIOS]$ php benchmark_json_index.php 
Start selecting all zip codes...
Text index: 9.7357420921326
Json operator index: 9.9487841129303
Json function index: 9.9152660369873
Done.
[user@dev SIOS]$ php benchmark_json_index.php 
Start selecting all zip codes...
Text index: 9.7414269447327
Json operator index: 9.9381420612335
Json function index: 9.9320080280304
Done.

■まとめ

PostgreSQL 9.2 では JSON 型に保存されたデータを簡単かつ高速に検索する方法は提供されていませんでした。このため GIN や GiST インデックスを利用しやすい hstore が柔軟なデータ保存先として利用されるケースも多かったと思います。

PostgreSQL 9.3 からは、PLV8 無しでも JSON 型データを簡単かつ高速に検索できるようになり、JSON 型の利用範囲が広がります。

JSON は事実上、テキスト型データ交換のデファクトスタンダードとなっています。特に Web アプリケーションの開発者にとって PostgreSQL 9.3 は強力なツールになると思います。

0 件のコメント:

コメントを投稿