インターフェース

最初の部分では、アクセス方法が自分に関する情報を提供する必要があるという事実について話しました。このインターフェースの仕組みを見てみましょう。

プロパティ

アクセス方法のすべてのプロパティは、pg_amテーブルに表示されます（am-アクセス方法）。この表から、使用可能なメソッドのリストを取得できます。

postgres=# select amname from pg_am; 
      

        
        
        
      

     amname 
      

        
        
        
      

     -------- 
      

        
        
        
      

     btree 
      

        
        
        
      

     hash 
      

        
        
        
      

     gist 
      

        
        
        
      

     gin 
      

        
        
        
      

     spgist 
      

        
        
        
      

     brin 
      

        
        
        
      

     (6 rows)

シーケンシャルスキャンはアクセス方法に当然起因すると考えられますが、歴史的にはこのリストにはないことが判明しています。

PostgreSQL 9.5以降では、各プロパティはpg_amテーブルの個別のフィールドで表されていました。バージョン9.6以降、プロパティは特別な機能によってポーリングされ、いくつかのレベルに分割されます。

アクセスメソッドのプロパティ-pg_indexam_has_property、
特定のインデックスのプロパティ-pg_index_has_property、
個々のインデックス列のプロパティはpg_index_column_has_propertyです。

アクセス方法とインデックスレベルの分離は、将来を見据えて行われます。現在、同じアクセス方法に基づいて作成されたすべてのインデックスは、常に同じプロパティを持ちます。

アクセス方法のプロパティには、次の4つが含まれます（例としてbtreeを使用）。

postgres=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name) 
      

        
        
        
      

     from pg_am a, 
      

        
        
        
      

     unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name) 
      

        
        
        
      

     where a.amname = 'btree' order by a.amname; 
      

        
        
        
      

     amname |     name      | pg_indexam_has_property 
      

        
        
        
      

     --------+---------------+------------------------- 
      

        
        
        
      

     btree  | can_order     | t 
      

        
        
        
      

     btree  | can_unique    | t 
      

        
        
        
      

     btree  | can_multi_col | t 
      

        
        
        
      

     btree  | can_exclude   | t 
      

        
        
        
      

     (4 rows)

can_order

アクセス方法を使用すると、インデックスの作成時に値のソート順を指定できます（現在、btreeにのみ適用可能）。
can_unique

一意性制限と主キーのサポート（btreeにのみ適用可能）;
can_multi_col

インデックスは複数の列で作成できます。
can_exclude

EXCLUDE例外制限のサポート。

インデックスに関連するプロパティ（たとえば、いくつかの既存のものを取得します）：

postgres=# select p.name, pg_index_has_property('t_a_idx'::regclass,p.name) 
      

        
        
        
      

     from unnest(array['clusterable','index_scan','bitmap_scan','backward_scan']) p(name); 
      

        
        
        
      

     name      | pg_index_has_property 
      

        
        
        
      

     ---------------+----------------------- 
      

        
        
        
      

     clusterable   | t 
      

        
        
        
      

     index_scan    | t 
      

        
        
        
      

     bitmap_scan   | t 
      

        
        
        
      

     backward_scan | t 
      

        
        
        
      

     (4 rows)

クラスター化可能

特定のインデックスに従ってテーブル行を並べ替える機能（同じ名前のCLUSTERコマンドによるクラスタリング）。
index_scan

インデックススキャンのサポート。このプロパティは奇妙に見えるかもしれませんが、すべてのインデックスが一度に1つずつTIDを生成できるわけではありません。一部のインデックスは一度にすべての結果を提供し、ビットマップスキャンのみをサポートします。
bitmap_scan

ビットマップスキャンをサポート。
後方スキャン

インデックス作成時に指定された逆順で結果を出力します。

最後に、列のプロパティ：

postgres=# select p.name, pg_index_column_has_property('t_a_idx'::regclass,1,p.name) 
      

        
        
        
      

     from unnest(array['asc','desc','nulls_first','nulls_last','orderable','distance_orderable','returnable','search_array','search_nulls']) p(name); 
      

        
        
        
      

     name        | pg_index_column_has_property 
      

        
        
        
      

     --------------------+------------------------------ 
      

        
        
        
      

     asc                | t 
      

        
        
        
      

     desc               | f 
      

        
        
        
      

     nulls_first        | f 
      

        
        
        
      

     nulls_last         | t 
      

        
        
        
      

     orderable          | t 
      

        
        
        
      

     distance_orderable | f 
      

        
        
        
      

     returnable         | t 
      

        
        
        
      

     search_array       | t 
      

        
        
        
      

     search_nulls       | t 
      

        
        
        
      

     (9 rows)

asc、desc、nulls_first、nulls_last、オーダー可能

これらのプロパティは、値の順序に関連しています（btreeインデックスに到達したら、それらについて説明します）。
distance_orderable

操作によるソート順で結果を出力します（現在、gistおよびrumインデックスにのみ適用可能）。
返却可能

テーブルにアクセスせずにインデックスを使用する機能。つまり、インデックスアクセスのみをサポートします。
search_array

構成「 index-field IN（ constant_list ）」または同じもの「 index-field = ANY（ constant_array ）」の複数の値の検索のサポート。
search_nulls

条件による検索機能はヌルであり、ヌルではありません。

すでに詳細に説明したプロパティの一部。現在、一部のプロパティは1つのメソッドによってのみ実装されています。この特定の方法について話すとき、そのような可能性を考慮します。

演算子のクラスとファミリー

「スキル」のセットに加えて、アクセス方法が機能するデータの種類とオペレーターを知る必要があります。このため、PostgreSQLには演算子クラスと演算子族の概念があります。

演算子クラスには、インデックスが特定のデータ型で動作するための最小限の演算子（および場合によっては補助関数）のセットが含まれます。

クラスは常に演算子のファミリーの一部です。同時に、複数のクラスが同じセマンティクスを持っている場合、1つの共通のファミリに含まれることがあります。たとえば、integer_opsファミリには、int8_ops、int4_ops、およびint2_opsのクラスが含まれます。これらは、意味が同じである異なるタイプのbigint、integer、およびsmallintに対応しています。

postgres=# select opfname, opcname, opcintype::regtype 
      

        
        
        
      

     from pg_opclass opc, pg_opfamily opf 
      

        
        
        
      

     where opf.opfname = 'integer_ops' 
      

        
        
        
      

     and opc.opcfamily = opf.oid 
      

        
        
        
      

     and opf.opfmethod = (select oid from pg_am where amname = 'btree'); 
      

        
        
        
      

     opfname   | opcname  | opcintype 
      

        
        
        
      

     -------------+----------+----------- 
      

        
        
        
      

     integer_ops | int2_ops | smallint 
      

        
        
        
      

     integer_ops | int4_ops | integer 
      

        
        
        
      

     integer_ops | int8_ops | bigint 
      

        
        
        
      

     (3 rows)

別の例：datetime_opsファミリには、日付を処理するための演算子クラスが含まれています（時間なしと時間付きの両方）：

postgres=# select opfname, opcname, opcintype::regtype 
      

        
        
        
      

     from pg_opclass opc, pg_opfamily opf 
      

        
        
        
      

     where opf.opfname = 'datetime_ops' 
      

        
        
        
      

     and opc.opcfamily = opf.oid 
      

        
        
        
      

     and opf.opfmethod = (select oid from pg_am where amname = 'btree'); 
      

        
        
        
      

     opfname    |     opcname     |          opcintype 
      

        
        
        
      

     --------------+-----------------+----------------------------- 
      

        
        
        
      

     datetime_ops | date_ops        | date 
      

        
        
        
      

     datetime_ops | timestamptz_ops | timestamp with time zone 
      

        
        
        
      

     datetime_ops | timestamp_ops   | timestamp without time zone 
      

        
        
        
      

     (3 rows)

ファミリには、異なるタイプの値を比較するための追加の演算子も含まれる場合があります。ファミリーでグループ化することにより、スケジューラーは、異なるタイプの値を持つ述部のインデックスを使用できます。ファミリには、他の補助機能も含まれる場合があります。

ほとんどの場合、演算子族とクラスは何も知る必要はありません。通常、インデックスを作成するだけで、デフォルトでいくつかのクラスの演算子を使用します。

ただし、演算子クラスを明示的に指定できます。これが必要な場合の簡単な例：C以外の並べ替え規則を持つデータベースでは、通常のテキストフィールドインデックスはLIKE操作をサポートしません。

postgres=# show lc_collate; 
      

        
        
        
      

     lc_collate 
      

        
        
        
      

     ------------- 
      

        
        
        
      

     en_US.UTF-8 
      

        
        
        
      

     (1 row) 
      

        
        
        
      

     postgres=# explain (costs off) select * from t where b like 'A%'; 
      

        
        
        
      

     QUERY PLAN 
      

        
        
        
      

     ----------------------------- 
      

        
        
        
      

     Seq Scan on t 
      

        
        
        
      

     Filter: (b ~~ 'A%'::text) 
      

        
        
        
      

     (2 rows)

この制限は、演算子クラスtext_pattern_opsを使用してインデックスを作成することで克服できます（プランの条件がどのように変更されたかに注意してください）。

postgres=# create index on t(b text_pattern_ops); 
      

        
        
        
      

     CREATE INDEX 
      

        
        
        
      

     postgres=# explain (costs off) select * from t where b like 'A%'; 
      

        
        
        
      

     QUERY PLAN 
      

        
        
        
      

     ---------------------------------------------------------------- 
      

        
        
        
      

     Bitmap Heap Scan on t 
      

        
        
        
      

     Filter: (b ~~ 'A%'::text) 
      

        
        
        
      

     ->  Bitmap Index Scan on t_b_idx1 
      

        
        
        
      

     Index Cond: ((b ~>=~ 'A'::text) AND (b ~<~ 'B'::text)) 
      

        
        
        
      

     (4 rows)

システムカタログ

このパートを締めくくるために、演算子のクラスとファミリーに直接関連するシステムカタログ表の小さな図を示します。

もちろん、これらの表はすべて詳細に説明されています。

システムカタログを使用すると、ドキュメントを見なくても多くの質問に対する答えを見つけることができます。たとえば、これまたはそのアクセス方法で使用できるデータの種類は何ですか？

postgres=# select opcname, opcintype::regtype 
      

        
        
        
      

     from pg_opclass 
      

        
        
        
      

     where opcmethod = (select oid from pg_am where amname = 'btree') 
      

        
        
        
      

     order by opcintype::regtype::text; 
      

        
        
        
      

     opcname       |          opcintype 
      

        
        
        
      

     ---------------------+----------------------------- 
      

        
        
        
      

     abstime_ops         | abstime 
      

        
        
        
      

     array_ops           | anyarray 
      

        
        
        
      

     enum_ops            | anyenum 
      

        
        
        
      

     ...

クラスにはどの演算子が含まれていますか（したがって、そのような演算子を含む条件によるアクセスにインデックスを使用できますか）？

postgres=# select amop.amopopr::regoperator 
      

        
        
        
      

     from pg_opclass opc, pg_opfamily opf, pg_am am, pg_amop amop 
      

        
        
        
      

     where opc.opcname = 'array_ops' 
      

        
        
        
      

     and opf.oid = opc.opcfamily 
      

        
        
        
      

     and am.oid = opf.opfmethod 
      

        
        
        
      

     and amop.amopfamily = opc.opcfamily 
      

        
        
        
      

     and am.amname = 'btree' 
      

        
        
        
      

     and amop.amoplefttype = opc.opcintype; 
      

        
        
        
      

     amopopr 
      

        
        
        
      

     ----------------------- 
      

        
        
        
      

     <(anyarray,anyarray) 
      

        
        
        
      

     <=(anyarray,anyarray) 
      

        
        
        
      

     =(anyarray,anyarray) 
      

        
        
        
      

     >=(anyarray,anyarray) 
      

        
        
        
      

     >(anyarray,anyarray) 
      

        
        
        
      

     (5 rows)

継続する。

PostgreSQLインデックス-2

インターフェース

プロパティ

演算子のクラスとファミリー

システムカタログ

More articles: