Postgres の世界では、データベース ストレージ (「ヒープ」と呼ばれます) を効率的に移動するためにインデックスが不可欠です。 Postgres はクラスタリングをサポートしておらず、MVCC アーキテクチャにより、同じタプルの多くのバージョンが存在することになります。 したがって、アプリケーションをサポートするために効率的なインデックスを作成および維持できることが非常に重要です。
ここでは、インデックスの使用を最適化し改善するためのヒントをいくつか紹介します。
注: 以下に示すクエリは、未変更のクエリで動作します。 .
カバーインデックスの使用
非アクティブなユーザーの電子メール アドレスを抽出するリクエストを見てみましょう。 テーブル customer コラムがあります active、クエリは単純です。
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on customer (cost=0.00..16.49 rows=15 width=32)
Filter: (active = 0)
(2 rows)
クエリはテーブル全体のスキャン シーケンスを呼び出します。 customer。 列にインデックスを作成しましょう active:
pagila=# CREATE INDEX idx_cust1 ON customer(active);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using idx_cust1 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
それは役に立ちました、その後のスキャンは「index scan」。 これは、Postgres がインデックスをスキャンすることを意味します。idx_cust1" そして、テーブル ヒープの検索を続けて、他の列 (この場合は列) の値を読み取ります。 email) クエリが必要とするもの。
カバーインデックスは PostgreSQL 11 で導入されました。 これらを使用すると、インデックス自体に XNUMX つ以上の追加の列を含めることができます。その値はインデックス データ ストアに保存されます。
この機能を利用してインデックス内に電子メールの値を追加した場合、Postgres はテーブル ヒープで値を検索する必要がなくなります。 email。 これが機能するかどうか見てみましょう:
pagila=# CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);
CREATE INDEX
pagila=# EXPLAIN SELECT email FROM customer WHERE active=0;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using idx_cust2 on customer (cost=0.28..12.29 rows=15 width=32)
Index Cond: (active = 0)
(2 rows)
«Index Only Scan' は、クエリに必要なのはインデックスのみであることを示しています。これにより、テーブル ヒープを読み取るためのすべてのディスク I/O を回避できます。
現在、カバーインデックスは B ツリーでのみ使用できます。 ただし、この場合、メンテナンスの手間はより多くなります。
部分インデックスの使用
部分インデックスは、テーブル内の行のサブセットのみにインデックスを付けます。 これにより、インデックスのサイズが節約され、スキャンが高速化されます。
カリフォルニアの顧客の電子メール アドレスのリストを取得したいとします。 リクエストは次のようになります。
SELECT c.email FROM customer c
JOIN address a ON c.address_id = a.address_id
WHERE a.district = 'California';
which has a query plan that involves scanning both the tables that are joined:
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
----------------------------------------------------------------------
Hash Join (cost=15.65..32.22 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=15.54..15.54 rows=9 width=4)
-> Seq Scan on address a (cost=0.00..15.54 rows=9 width=4)
Filter: (district = 'California'::text)
(6 rows)通常のインデックスから得られるもの:
pagila=# CREATE INDEX idx_address1 ON address(district);
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
---------------------------------------------------------------------------------------
Hash Join (cost=12.98..29.55 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.87..12.87 rows=9 width=4)
-> Bitmap Heap Scan on address a (cost=4.34..12.87 rows=9 width=4)
Recheck Cond: (district = 'California'::text)
-> Bitmap Index Scan on idx_address1 (cost=0.00..4.34 rows=9 width=0)
Index Cond: (district = 'California'::text)
(8 rows)
スキャン address インデックススキャンに置き換えられました idx_address1そしてヒープをスキャンしました address.
これは頻繁に使用されるクエリであり、最適化する必要があるため、部分インデックスを使用できます。これは、地区が含まれる住所を持つ行のみにインデックスを作成します。 ‘California’:
pagila=# CREATE INDEX idx_address2 ON address(address_id) WHERE district='California';
CREATE INDEX
pagila=# EXPLAIN SELECT c.email FROM customer c
pagila-# JOIN address a ON c.address_id = a.address_id
pagila-# WHERE a.district = 'California';
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=12.38..28.96 rows=9 width=32)
Hash Cond: (c.address_id = a.address_id)
-> Seq Scan on customer c (cost=0.00..14.99 rows=599 width=34)
-> Hash (cost=12.27..12.27 rows=9 width=4)
-> Index Only Scan using idx_address2 on address a (cost=0.14..12.27 rows=9 width=4)
(5 rows)
これで、クエリは読み取りのみになりました idx_address2 そしてテーブルには触れません address.
複数値インデックスの使用
インデックス付けされる一部の列には、スカラー データ型が含まれない場合があります。 次のような列タイプ jsonb, arrays и tsvector 複合値または複数の値が含まれています。 このような列にインデックスを付ける必要がある場合は、通常、それらの列内のすべての個々の値を検索する必要があります。
失敗したテイクのカットを含むすべての映画のタイトルを見つけてみましょう。 テーブル film というテキスト列があります special_features。 ムービーにこの「特別なプロパティ」がある場合、列には要素がテキスト配列として含まれます。 Behind The Scenes。 このような映画をすべて検索するには、「Behind The Scenes」を含むすべての行を選択する必要があります。 任意の 配列値 special_features:
SELECT title FROM film WHERE special_features @> '{"Behind The Scenes"}';
ネスト演算子 @> 右側が左側のサブセットであるかどうかを確認します。
リクエストプラン:
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)これは、コスト 67 のフル ヒープ スキャンを要求します。
通常の B ツリー インデックスが役立つかどうかを見てみましょう。
pagila=# CREATE INDEX idx_film1 ON film(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on film (cost=0.00..67.50 rows=5 width=15)
Filter: (special_features @> '{"Behind The Scenes"}'::text[])
(2 rows)インデックスも考慮されていませんでした。 B ツリー インデックスは、インデックス付きの値内の個々の要素の存在を認識しません。
GIN インデックスが必要です。
pagila=# CREATE INDEX idx_film2 ON film USING GIN(special_features);
CREATE INDEX
pagila=# EXPLAIN SELECT title FROM film
pagila-# WHERE special_features @> '{"Behind The Scenes"}';
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on film (cost=8.04..23.58 rows=5 width=15)
Recheck Cond: (special_features @> '{"Behind The Scenes"}'::text[])
-> Bitmap Index Scan on idx_film2 (cost=0.00..8.04 rows=5 width=0)
Index Cond: (special_features @> '{"Behind The Scenes"}'::text[])
(4 rows)GIN インデックスは、インデックス付き複合値に対する単一値のマッピングをサポートしているため、クエリ プランのコストが半分以上になります。
重複したインデックスを削除する
インデックスは時間の経過とともに蓄積されるため、新しいインデックスに以前のインデックスの XNUMX つと同じ定義が含まれる場合があります。 カタログ ビューを使用すると、人間が判読できるインデックスの SQL 定義を取得できます。 pg_indexes。 同一の定義を簡単に見つけることもできます。
SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
FROM pg_indexes
GROUP BY defn
HAVING count(*) > 1;
And here’s the result when run on the stock pagila database:
pagila=# SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
pagila-# FROM pg_indexes
pagila-# GROUP BY defn
pagila-# HAVING count(*) > 1;
indexes | defn
------------------------------------------------------------------------+------------------------------------------------------------------
{payment_p2017_01_customer_id_idx,idx_fk_payment_p2017_01_customer_id} | CREATE INDEX ON public.payment_p2017_01 USING btree (customer_id
{payment_p2017_02_customer_id_idx,idx_fk_payment_p2017_02_customer_id} | CREATE INDEX ON public.payment_p2017_02 USING btree (customer_id
{payment_p2017_03_customer_id_idx,idx_fk_payment_p2017_03_customer_id} | CREATE INDEX ON public.payment_p2017_03 USING btree (customer_id
{idx_fk_payment_p2017_04_customer_id,payment_p2017_04_customer_id_idx} | CREATE INDEX ON public.payment_p2017_04 USING btree (customer_id
{payment_p2017_05_customer_id_idx,idx_fk_payment_p2017_05_customer_id} | CREATE INDEX ON public.payment_p2017_05 USING btree (customer_id
{idx_fk_payment_p2017_06_customer_id,payment_p2017_06_customer_id_idx} | CREATE INDEX ON public.payment_p2017_06 USING btree (customer_id
(6 rows)
スーパーセットインデックス
最終的に多数のインデックスが作成され、そのうちの XNUMX つが他のインデックスにインデックスを付ける列のスーパーセットにインデックスを付けることになる場合があります。 これは望ましい場合と望ましくない場合があります。スーパーセットによりインデックスのみのスキャンが行われる可能性があり、これは良いことですが、スペースを消費しすぎたり、スーパーセットが最適化する予定だったクエリが使用されなくなったりする可能性があります。
このようなインデックスの定義を自動化する必要がある場合は、次のことから始めることができます。 テーブルから pg_catalog.
未使用のインデックス
データベースを使用するアプリケーションが進化するにつれて、使用するクエリも進化します。 以前に追加されたインデックスは、どのクエリでも使用できなくなります。 インデックスがスキャンされるたびに、統計マネージャーとシステム カタログ ビューでインデックスがマークされます。 pg_stat_user_indexes 価値がわかります idx_scan、これは累積カウンターです。 この値を一定期間 (たとえば XNUMX か月) にわたって追跡すると、どのインデックスが使用されておらず、削除される可能性があるのかがわかります。
以下は、スキーマ内のすべてのインデックスの現在のスキャン数を取得するクエリです。 ‘public’:
SELECT relname, indexrelname, idx_scan
FROM pg_catalog.pg_stat_user_indexes
WHERE schemaname = 'public';
with output like this:
pagila=# SELECT relname, indexrelname, idx_scan
pagila-# FROM pg_catalog.pg_stat_user_indexes
pagila-# WHERE schemaname = 'public'
pagila-# LIMIT 10;
relname | indexrelname | idx_scan
---------------+--------------------+----------
customer | customer_pkey | 32093
actor | actor_pkey | 5462
address | address_pkey | 660
category | category_pkey | 1000
city | city_pkey | 609
country | country_pkey | 604
film_actor | film_actor_pkey | 0
film_category | film_category_pkey | 0
film | film_pkey | 11043
inventory | inventory_pkey | 16048
(10 rows)少ないロックでインデックスを再構築する
インデックスが肥大化した場合など、インデックスの再構築が必要になることが多く、再構築によりスキャンが高速化されます。 また、インデックスが破損する可能性もあります。 インデックスパラメータを変更すると、インデックスの再構築が必要になる場合もあります。
並列インデックス作成を有効にする
PostgreSQL 11 では、B ツリー インデックスの作成が同時に行われます。 作成プロセスを高速化するために、複数の並列ワーカーを使用できます。 ただし、次の構成オプションが正しく設定されていることを確認してください。
SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;デフォルト値は小さすぎます。 理想的には、これらの数はプロセッサ コアの数とともに増加するはずです。 続きを読む .
バックグラウンドでのインデックス作成
オプションを使用してバックグラウンドでインデックスを作成できます。 CONCURRENTLY チーム CREATE INDEX:
pagila=# CREATE INDEX CONCURRENTLY idx_address1 ON address(district);
CREATE INDEXこのインデックス作成手順は、テーブルのロックを必要としないため、書き込み操作をブロックしないという点で通常の手順とは異なります。 その一方で、より多くの時間がかかり、より多くのリソースが消費されます。
Postgres は、インデックスの作成、特殊なケースを解決する方法、およびアプリケーションが爆発的に増大した場合にデータベースを管理する方法を非常に柔軟に提供します。 これらのヒントが、クエリを迅速に実行し、データベースをスケールできるようにするのに役立つことを願っています。
出所： habr.com