現版のPostgreSQL (9.6を含む)では、TOASTテーブルに従うクエリオプティマイザの弱さに苦しみます。 TOASTテーブルは、(長いテキスト、イメージ、多数の頂点を持つ複合ジオメトリといった)通常のデータページに適合しない、(データサイズという意味では)巨大な値を納めるための「拡張部屋」の一種です。詳細情報は the PostgreSQL Documentation for TOASTをご覧ください。
(高解像度で全てのヨーロッパの国の境界を含むテーブルのような)大きなジオメトリがあるうえ、行がそう多くないテーブルを持つようになると、この問題が出てきます。テーブル自体は小さいのですが、多くのTOASTスペースを使います。例として、テーブル自体は概ね80行で3データページしか使わなくてもTOASTテーブルで8225ページを使うとします。
ここで、ジオメトリ演算子の&&を使って、ほとんどマッチしないようなバウンダリボックスを検索するクエリを出してみます。クエリオプティマイザにはテーブルは3ページ80行しかないように見えます。オプティマイザは、小さなテーブルを順に走査する方がインデックスを使うよりも早いと見積もります。そして、GiSTインデックスは無視すると決めます。通常なら、この見積もりは正しいです。しかし、この場合は&&演算子が全てのジオメトリをディスクから呼び出してバウンディングボックスと比較しなければならなくなり、ゆえに、全てのTOASTページもまた呼び出す必要があります。
この問題に苦しむかどうかを見るには、PostgreSQLの"EXPLAIN ANALYZE"コマンドを使います。詳細情報と技術情報については、PostgreSQL性能メーリングリストのスレッドhttp://archives.postgresql.org/pgsql-performance/2005-02/msg00030.phpをご覧下さい。
また、PostGISの新しいスレッドhttps://lists.osgeo.org/pipermail/postgis-devel/2017-June/026209.htmlもご覧下さい。
PostgreSQLコミュニティでは、TOASTを意識したクエリ見積もりを作ることで、この問題を解決しようとしています。今のところは、二つの応急処置があります。
一つは、クエリプランナにインデックスの使用を強制することです。クエリを発行する前に"SET enable_seqscan TO off;"をサーバに送信します。これは基本的にクエリプランナに対して可能な限り順に走査することを避けるよう強制します。そのためGiSTインデックスを通常使うようになります。しかし、このフラグは接続するたびに設定しなければならず、他のケースにおいてはクエリプランナに誤った見積もりをさせることになるので、 "SET enable_seqscan TO on;"をクエリの後に送信すべきです。
もう一つは、順に走査することをクエリプランナが考える程度に早くすることです。これは、バウンダリボックスの「キャッシュ」を行う追加カラムを作成し、このカラムにマッチさせるようにすることで達成することができます。ここでの例では次のようになります。
SELECT AddGeometryColumn('myschema','mytable','bbox','4326','GEOMETRY','2');
UPDATE mytable SET bbox = ST_Envelope(ST_Force2D(geom));
そして、次のように、&&演算子をgeom_columnに対して行っていたものをbboxに変更します。
SELECT geom_column
FROM mytable
WHERE bbox && ST_SetSRID('BOX3D(0 0,1 1)'::box3d,4326);
もちろん、mytableの行を変更または追加したら、bboxを「同期」するようにしなければなりません。最もすっきりした方法はトリガです。もしくは、アプリケーションを変更してbboxカラムの現状を保持するか、テーブル更新後にいつもUPDATEクエリを実行するかでも対応できます。
読み込むことがほとんどで、かつほとんどのクエリでひとつのインデックスを使うようなテーブルのために、PostgreSQLはCLUSTERコマンドを提供しています。このコマンドは、全てのデータ行を、インデックス基準にあわせて物理的に再整理するので、二つの性能の利点を生みます。一つは、インデックスの範囲走査のために、データテーブルのシーク回数が劇的に減少することです。もう一つは、いくつかの小さなインデックス間隔に集中する場合には、データ行が分布するデータページがより少なくなることで、より効率的なキャッシュを持つことです (この点は、PostgreSQLマニュアルのCLUSTERコマンドのドキュメントを読むように仕向けられていると感じて下さい)。
しかし、GiSTインデックスは単純にNULL値を無視するため現在のところPostGISのGiSTインデックスのクラスタリングはできず、次のようなエラーメッセージを得ます。
lwgeom=# CLUSTER my_geom_index ON my_table; ERROR: cannot cluster when index access method does not handle null values HINT: You may be able to work around this by marking column "geom" NOT NULL.
ヒントメッセージにある通り、テーブルに"not null"制限を追加することで、この欠陥にとりあえず対応できます。例を示します。
lwgeom=# ALTER TABLE my_table ALTER COLUMN geom SET not null; ALTER TABLE
もちろん、ジオメトリカラムで実際にNULL値が必要な場合、この対応はできません。さらには、制限を追加するには上の方法を使わなければならず、"ALTER TABLE blubb ADD CHECK (geometry is not null);"のようなCHECK制限は使えません。
ときどき、テーブルで3次元、4次元のデータを持つのに、常にOpenGIS準拠のST_AsText()またはST_AsBinary()関数を使ってアクセスして 2次元ジオメトリを出力させるようなことが起きます。内部でST_Force_2d()関数を呼んでいるために発生しますが、これは、大きなジオメトリでは重大なオーバヘッドを誘引することになります。このオーバヘッドを回避するには、一度追加された次元を前もって落とし、かつこれを永続化するのが適当かも知れません。
UPDATE mytable SET geom = ST_Force2D(geom); VACUUM FULL ANALYZE mytable;
AddGeometryColumn()を使ってジオメトリカラムを追加した場合、ジオメトリの次元に関する制限があることに注意してください。この制限を迂回するには、制限の削除が必要になります。geometry_columnsテーブル内のエントリを更新して、その後で制限を再作成することを忘れないで下さい。
大きなテーブルの場合、WHERE節、およびプライマリキー若しくは他の適切な基準によってテーブルの一部へのUPDATEを制限させて、UPDATEの実行の間に単に"VACUUM;"と実行することで、UPDATEをより小さい塊に分割するのが賢いやり方かもしれません。これにより、テンポラリディスクスペースが劇的に減少します。さらに、次元混合のジオメトリを持つ場合、"WHERE dimension(the_geom)>2"によってUPDATEを制限することで、2次元で書かれているジオメトリの再書き込みをスキップさせることができます。