postgresqlインデックスのhashの使用詳細
11650 ワード
os:uuntu 16.04
postgresql:9.6.8
ip企画
192.168.56.102 node 2 postgresql
help create index
method
使用する索引方法の名前。btree、hash、gist、spgist、gin、binが選択できます。デフォルトの方法はbtreeです。
sh
hashは簡単な等値比較しかできません。
Hashインデックス操作はWALに記録されていませんので、未記入の変更があります。データベース崩壊後、REINDEXコマンドでHashインデックスを再構築する必要があります。
同様に、初期の基礎バックアップが完了した後も、Hashインデックスの変更は、ストリーミングやファイルベースのコピーによってコピーされませんので、それらを使ったクエリーに対してエラーの答えを与えます。
これらの理由から、Hashインデックスはもう推奨されていません。
追加:Postgresql hashインデックスの紹介
hashインデックスの構造
データがインデックスを挿入すると、このインデックスキーのためにハッシュ関数を通して値を計算します。PostgreSQLのハッシュ関数は常に「整数」タイプを返します。範囲は2^32≒40億円です。ブロックバケツの数は最初は2つで、その後はデータサイズに応じて動的に増加します。ビットアルゴリズムを使用して、ハッシュコードからバケット番号を計算することができる。このブックレットはTIDを預けます。
異なるインデックスキーと一致するTIDを同じbucketバケツに入れることができるからです。また、TID以外にも、キーのソース値をbucketバケツに格納することができますが、インデックスサイズを増加させます。スペースを節約するために、bucketバケツはインデックスキーのハッシュコードのみを格納し、インデックスキーを格納しない。
インデックス・クエリーを通過すると、インデックス・キーのハッシュ関数を計算し、ブロック・バケツの番号を取得する。今でも、格納されたバケットのコンテンツを巡回し、必要なハッシュコードマッチングのTIDのみを返す必要がある。格納された「hash code-TID」ペアは規則的であるので、効率的にこの動作を行うことができる。
しかし、2つの異なるインデックスキーは、次のような場合があります。2つのインデックスキーは、同じ4バイトのハッシュコードを持つ1つのbucketバケットに入ります。したがって、インデックスアクセス方法は、インデックスエンジンが各TIDを検証するために、表の行の状況を再チェックする必要があります。
マップデータ構造をpageに
Meta page-0番pageには、インデックス内部の関連情報が含まれています。
Bucket pages-インデックスの主なpageは、「hash code-TID」ペアを格納します。
Overflow pages-bucket pageと同じ構造で、一つのpage未満の場合は、bucketバレルとして使用します。
Bitmap pages-現在の綺麗なoverflow pageを追跡し、他のbucketバケツに再利用することができます。
ハッシュインデックスは小さいサイズを減らすことができません。インデックス行を削除しましたが、割り当てられたページはオペレーティングシステムに戻りません。VACUUMINGの後に新しいデータを再利用します。インデックスサイズを減らす唯一のオプションは、REINDEXまたはVALUUM FLLコマンドを使用してインデックスの再構築を開始することです。
次に、hashインデックスはどうやって作成されますか?
hashアクセス方法に関する操作関数を表示します。
プラグインをインストール
つまり、インデックスを維持するために膨張度です。
postgresql:9.6.8
ip企画
192.168.56.102 node 2 postgresql
help create index
postgres=# \h create index
Command: CREATE INDEX
Description: define a new index
Syntax:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ WITH ( storage_parameter = value [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]
[USING method]method
使用する索引方法の名前。btree、hash、gist、spgist、gin、binが選択できます。デフォルトの方法はbtreeです。
sh
hashは簡単な等値比較しかできません。
postgres=# drop table tmp_t0;
DROP TABLE
postgres=# create table tmp_t0(c0 varchar(100),c1 varchar(100));
CREATE TABLE
postgres=# insert into tmp_t0(c0,c1) select md5(id::varchar),md5((id+id)::varchar) from generate_series(1,100000) as id;
INSERT 0 100000
postgres=# create index idx_tmp_t0_1 on tmp_t0 using hash(c0);
CREATE INDEX
postgres=# \d+ tmp_t0
Table "public.tmp_t0"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+------------------------+-----------+----------+---------+----------+--------------+-------------
c0 | character varying(100) | | | | extended | |
c1 | character varying(100) | | | | extended | |
Indexes:
"idx_tmp_t0_1" hash (c0)
postgres=# explain select * from tmp_t0 where c0 = 'd3d9446802a44259755d38e6d163e820';
QUERY PLAN
----------------------------------------------------------------------------
Index Scan using idx_tmp_t0_1 on tmp_t0 (cost=0.00..8.02 rows=1 width=66)
Index Cond: ((c0)::text = 'd3d9446802a44259755d38e6d163e820'::text)
(2 rows)
注意事項は、公式サイトで特に強調されています。Hashインデックス操作はWALに記録されていませんので、未記入の変更があります。データベース崩壊後、REINDEXコマンドでHashインデックスを再構築する必要があります。
同様に、初期の基礎バックアップが完了した後も、Hashインデックスの変更は、ストリーミングやファイルベースのコピーによってコピーされませんので、それらを使ったクエリーに対してエラーの答えを与えます。
これらの理由から、Hashインデックスはもう推奨されていません。
追加:Postgresql hashインデックスの紹介
hashインデックスの構造
データがインデックスを挿入すると、このインデックスキーのためにハッシュ関数を通して値を計算します。PostgreSQLのハッシュ関数は常に「整数」タイプを返します。範囲は2^32≒40億円です。ブロックバケツの数は最初は2つで、その後はデータサイズに応じて動的に増加します。ビットアルゴリズムを使用して、ハッシュコードからバケット番号を計算することができる。このブックレットはTIDを預けます。
異なるインデックスキーと一致するTIDを同じbucketバケツに入れることができるからです。また、TID以外にも、キーのソース値をbucketバケツに格納することができますが、インデックスサイズを増加させます。スペースを節約するために、bucketバケツはインデックスキーのハッシュコードのみを格納し、インデックスキーを格納しない。
インデックス・クエリーを通過すると、インデックス・キーのハッシュ関数を計算し、ブロック・バケツの番号を取得する。今でも、格納されたバケットのコンテンツを巡回し、必要なハッシュコードマッチングのTIDのみを返す必要がある。格納された「hash code-TID」ペアは規則的であるので、効率的にこの動作を行うことができる。
しかし、2つの異なるインデックスキーは、次のような場合があります。2つのインデックスキーは、同じ4バイトのハッシュコードを持つ1つのbucketバケットに入ります。したがって、インデックスアクセス方法は、インデックスエンジンが各TIDを検証するために、表の行の状況を再チェックする必要があります。
マップデータ構造をpageに
Meta page-0番pageには、インデックス内部の関連情報が含まれています。
Bucket pages-インデックスの主なpageは、「hash code-TID」ペアを格納します。
Overflow pages-bucket pageと同じ構造で、一つのpage未満の場合は、bucketバレルとして使用します。
Bitmap pages-現在の綺麗なoverflow pageを追跡し、他のbucketバケツに再利用することができます。
ハッシュインデックスは小さいサイズを減らすことができません。インデックス行を削除しましたが、割り当てられたページはオペレーティングシステムに戻りません。VACUUMINGの後に新しいデータを再利用します。インデックスサイズを減らす唯一のオプションは、REINDEXまたはVALUUM FLLコマンドを使用してインデックスの再構築を開始することです。
次に、hashインデックスはどうやって作成されますか?
demo=# create index on flights using hash(flight_no);
demo=# explain (costs off) select * from flights where flight_no = 'PG0001';
QUERY PLAN
----------------------------------------------------
Bitmap Heap Scan on flights
Recheck Cond: (flight_no = 'PG0001'::bpchar)
-> Bitmap Index Scan on flights_flight_no_idx
Index Cond: (flight_no = 'PG0001'::bpchar)
(4 rows)
注意:10バージョン前のhashインデックスはwalに記録されていませんので、hashインデックスはrecoveryを作ることができません。もちろんコピーはできませんが、10バージョン以降はhashが使用されるようになりました。walに記録できます。作成時も警告はありません。hashアクセス方法に関する操作関数を表示します。
demo=# select opf.opfname as opfamily_name,
amproc.amproc::regproc AS opfamily_procedure
from pg_am am,
pg_opfamily opf,
pg_amproc amproc
where opf.opfmethod = am.oid
and amproc.amprocfamily = opf.oid
and am.amname = 'hash'
order by opfamily_name,
opfamily_procedure;
opfamily_name | opfamily_procedure
--------------------+-------------------------
abstime_ops | hashint4extended
abstime_ops | hashint4
aclitem_ops | hash_aclitem
aclitem_ops | hash_aclitem_extended
array_ops | hash_array
array_ops | hash_array_extended
bool_ops | hashcharextended
bool_ops | hashchar
bpchar_ops | hashbpcharextended
bpchar_ops | hashbpchar
bpchar_pattern_ops | hashbpcharextended
bpchar_pattern_ops | hashbpchar
bytea_ops | hashvarlena
bytea_ops | hashvarlenaextended
char_ops | hashcharextended
char_ops | hashchar
cid_ops | hashint4extended
cid_ops | hashint4
date_ops | hashint4extended
date_ops | hashint4
enum_ops | hashenumextended
enum_ops | hashenum
float_ops | hashfloat4extended
float_ops | hashfloat8extended
float_ops | hashfloat4
float_ops | hashfloat8
...
これらの関数を用いて、関連するタイプのハッシュコードを計算することができる。
hank=# select hashtext('zhang');
hashtext
-------------
-1172392837
(1 row)
hank=# select hashint4(10);
hashint4
-------------
-1547814713
(1 row)
hashインデックスに関する属性
hank=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
hank-# from pg_am a,
hank-# unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
hank-# where a.amname = 'hash'
hank-# order by a.amname;
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
hash | can_order | f
hash | can_unique | f
hash | can_multi_col | f
hash | can_exclude | t
(4 rows)
hank=# select p.name, pg_index_has_property('hank.idx_test_name'::regclass,p.name)
hank-# from unnest(array[
hank(# 'clusterable','index_scan','bitmap_scan','backward_scan'
hank(# ]) p(name);
name | pg_index_has_property
---------------+-----------------------
clusterable | f
index_scan | t
bitmap_scan | t
backward_scan | t
(4 rows)
hank=# select p.name,
hank-# pg_index_column_has_property('hank.idx_test_name'::regclass,1,p.name)
hank-# from unnest(array[
hank(# 'asc','desc','nulls_first','nulls_last','orderable','distance_orderable',
hank(# 'returnable','search_array','search_nulls'
hank(# ]) p(name);
name | pg_index_column_has_property
--------------------+------------------------------
asc | f
desc | f
nulls_first | f
nulls_last | f
orderable | f
distance_orderable | f
returnable | f
search_array | f
search_nulls | f
(9 rows)
hash関数には特定の並べ替え規則がないので、一般的なhashインデックスは等値検索だけをサポートしています。下のデータ辞書で見ると、すべての操作は「=」であり、hashインデックスもnull値を処理しないので、null値はマークされません。複数の列でhashインデックスを作成することもサポートされていません。
hank=# select opf.opfname AS opfamily_name,
hank-# amop.amopopr::regoperator AS opfamily_operator
hank-# from pg_am am,
hank-# pg_opfamily opf,
hank-# pg_amop amop
hank-# where opf.opfmethod = am.oid
hank-# and amop.amopfamily = opf.oid
hank-# and am.amname = 'hash'
hank-# order by opfamily_name,
hank-# opfamily_operator;
opfamily_name | opfamily_operator
--------------------+------------------------------------------------------------
abstime_ops | =(abstime,abstime)
aclitem_ops | =(aclitem,aclitem)
array_ops | =(anyarray,anyarray)
bool_ops | =(boolean,boolean)
bpchar_ops | =(character,character)
bpchar_pattern_ops | =(character,character)
bytea_ops | =(bytea,bytea)
char_ops | =("char","char")
cid_ops | =(cid,cid)
date_ops | =(date,date)
enum_ops | =(anyenum,anyenum)
float_ops | =(real,real)
float_ops | =(double precision,double precision)
float_ops | =(real,double precision)
float_ops | =(double precision,real)
hash_hstore_ops | =(hstore,hstore)
integer_ops | =(integer,bigint)
integer_ops | =(smallint,smallint)
integer_ops | =(integer,integer)
integer_ops | =(bigint,bigint)
integer_ops | =(bigint,integer)
integer_ops | =(smallint,integer)
integer_ops | =(integer,smallint)
integer_ops | =(smallint,bigint)
integer_ops | =(bigint,smallint)
interval_ops | =(interval,interval)
jsonb_ops | =(jsonb,jsonb)
macaddr8_ops | =(macaddr8,macaddr8)
macaddr_ops | =(macaddr,macaddr)
name_ops | =(name,name)
network_ops | =(inet,inet)
numeric_ops | =(numeric,numeric)
oid_ops | =(oid,oid)
oidvector_ops | =(oidvector,oidvector)
pg_lsn_ops | =(pg_lsn,pg_lsn)
range_ops | =(anyrange,anyrange)
reltime_ops | =(reltime,reltime)
text_ops | =(text,text)
text_pattern_ops | =(text,text)
time_ops | =(time without time zone,time without time zone)
timestamp_ops | =(timestamp without time zone,timestamp without time zone)
timestamptz_ops | =(timestamp with time zone,timestamp with time zone)
timetz_ops | =(time with time zone,time with time zone)
uuid_ops | =(uuid,uuid)
xid_ops | =(xid,xid)
10バージョンから、hashインデックスの内部状況をpageinspectプラグインで確認できます。プラグインをインストール
create extension pageinspect;
0番のページを表示します
hank=# select hash_page_type(get_raw_page('hank.idx_test_name',0));
hash_page_type
----------------
metapage
(1 row)
インデックスの行数と使用済みの最大記憶桶数を表示します。
hank=# select ntuples, maxbucket
hank-# from hash_metapage_info(get_raw_page('hank.idx_test_name',0));
ntuples | maxbucket
---------+-----------
1000 | 3
(1 row)
1番のpageはbucketと見られます。このbucket pageの活動元組と死元組の数を調べてください。つまり、インデックスを維持するために膨張度です。
hank=# select hash_page_type(get_raw_page('hank.idx_test_name',1));
hash_page_type
----------------
bucket
(1 row)
hank=# select live_items, dead_items
hank-# from hash_page_stats(get_raw_page('hank.idx_test_name',1));
live_items | dead_items
------------+------------
407 | 0
(1 row)
以上は個人の経験ですので、参考にしていただければと思います。間違いがあったり、完全に考えていないところがあれば、教えてください。