postgresqlサーバーのチューニングについて

質問

現在、Access2010＋Postgresqlにてシステムを構築しており
ADOにてデータベースからレコードの検索・追加・更新を行っております。

今回質問させて頂きましたのはpostgresqlのチューニング方法についてです。
現在、あるテーブルにWANからVPNを経由して接続した状態でSELECTを行うと、
結果が帰ってくるまでにおよそ6分程度かかるテーブルがあります。
（LAN内では4秒程度）
抽出するフィールドを*ではなく、1フィールドに限定しても約4分程度かかります。

そのテーブルには約30000件ほどレコードが格納されており
SELECTする際は、bool型のフィールドをWHERE条件に入れています。

例）SELECT * FROM tbl_test WHERE f条件 = TRUE;

しかし同じテーブルの別のvarchar型フィールドにLIKE演算子で検索を
かけると、すぐ（0.44秒）に結果が帰ってきます。

例）SELECT * FROM tbl_test WHERE 名称 LIKE '%テスト%';

尚、この場合の結果は20件ほどですので、結果が早いのは当然ですが
なぜbool型を条件にした場合に6分もかかってしまうのでしょうか。
システムの都合上、WANからVPNを経由してアクセスする事が必須ですので
LAN内の速度が速くてもWANからの速度が遅いのはNGなのです。

一応、Web上で紹介されている一般的なチューニング方法を参考にし
postgresql.confの設定値は下記の通りに行いました。

・shared_buffers → 1024MB
・max_connections → 100
・effective_cache_size → 512MB
・random_page_count → 2
・work_mem → 3MB

恐らく根本的にどこかの設定や設計がまずい為、このような結果に
なっているのであろうと思うのですが、それを特定するに至りません。
見直すべき点やアドバイスなどがあれば教えて下さい。

＜サーバースペック＞
OS：WindowsXP Pro SP3
メモリ：3GB
CPU：Pen4 2.8GHz
Postgresqlバージョン：8.3

回答No.1

superside0 · ベストアンサー

なんとなくですが、チューニングでどうにかなるレベルの話じゃなくても、設計の問題のように思えます。

SELECT * FROM tbl_test WHERE f条件 = TRUE;
のSELECT自体が遅いのではなく、その結果をパケットデータにして、
VPN経由でMS-ACCESSに転送する部分が重いのではないでしょうか？

つまり、SQLのリザルトデータが大量すぎるのが問題なので
そのリザルトを使ってさらにMS-ACCESS側でさらに絞り込んでいるのなら
そのロジックをACCESSからSQL側に移転させれば、一気に　解決できるように思われます。

たとえば、
更新時は、更新する対象のデータのみを取り出すSQLにする。
挿入前の衝突チェックなら、衝突チェックをACCESS側でなく衝突チェック用のSQLを投げる。
一覧表示なら、ページ分割にして　limit句で部分取り出しにする。
データ検索も、ACCESS側でなくSQLで実装する。
といった具合に。

補足

早々のご回答、誠に有り難うございます。

なるほど、チューニング云々の問題ではなさそうとの事ですね。
確かに「検索に時間がかかる」というよりかは、
データの受け取りに時間がかかっているような気はしていました
（LAN内では3秒程度なので）

回答者様のおっしゃる通りかと思います。

ただ、やはりAccess側に最高で30000件程度のデータを表示させる事は
避けられませんので、別の角度からデータの受け取りについて考えてみる必要がありそうですね。

有り難うございました。

回答No.2

superside0

どうしても、MS-ACCESS側に全データを取り込んでおく必要があるというのなら
（あまり綺麗な方法ではないですが）
毎回大量のリザルトがあるSQLを発行するのではなく、データをACCESS側にキャッシュしておいて、
更新・挿入・削除のあったデータのみをもってきてローカル側で整合させるって方法もありえますね。
これはこれで、アルゴリズムしっかり考えないと、同期とれなくなって
データ壊してしまう可能性もありますが。

それを考えると、MS-ACCESSのあるLAN側にもPostgreSQLを立てて、
PostgreSQL同士でレプリケーションを組んでしまったほうが、簡単かもしれません。

もし、PostgreSQL とMS-ACCESSとでレプリケーションが組めるような
パッケージなりライブラリがあるなら、そっちを使う方法もあるでしょうけど。

お礼

なるほど、ローカル側にキャッシュする方法もありですね。
もちろんご指摘の整合性を保つ問題はありますが・・

あれから色々とLAN内で試しましたが、どうやらLAN内では
そこそこの速度で結果が帰ってきましたので、やはりpostgresqlそのものの
問題ではなさそうな気がしてきました。

ご回答頂きました方法も合わせて、データ保持の方法について
もう一度考えてみたいと思います。

有り難うございました。