PostgreSQLでIndex Only Scanにも関わらず、Heap Fetchesが発生して性能が劣化した

Index Only ScanはIndexにしかアクセスしないのでとても高速です。しかしIndex Only Scanにも関わらず、Heap Fetches(実テーブルへのアクセス)が発生するという状況に遭遇しました。

テーブル構造

この状況が発生したテーブルの定義を簡素化したものです。簡素化のために違和感があるかもしれませんが、ご容赦ください。

CREATE TABLE blogschema.items (
    id BIGSERIAL PRIMARY KEY,
    category_id INTEGER REFERENCES blogschema.categories (id) NOT NULL,
    item_hash BYTEA NOT NULL
);
CREATE INDEX ON blogschema.items (category_id, item_hash);

何をしたら発生したか

このテーブルに対して、数百万件のUPDATEおよびINSERTを繰り返していました。十中八九関係ないですが、SQLAlchemyを使っていました。

発生後の状況

UPDATEおよびINSERTをする部分にかかった時間を計測していたので気がつくことができました。数百万件を1000件ずつのチャンクに分けてINSERTしていました。その一度のINSERTにかかる時間が、早い数百万件のときは200ms程度なのですが、遅い数百万件のときには数秒かかっていました。しかも、この数秒は徐々に長くなっていたのです。INSERTだけでなくSELECTにも同様の傾向が見られたので、実際に実行されているSQL文を模倣し、 EXPLAIN ANALYZE を実行しました。

EXPLAIN ANALYZE SELECT
    category_id,
    item_hash
FROM blogschema.items
WHERE
    category_id IN (7) AND
    item_hash IN ('xadfc46d67b03d1d6f3fe87925baee87a'::BYTEA, ...)
;

その結果がこれです。

Index Only Scan using items_category_id_item_hash_idx on items  (cost=0.43..7.45 rows=1 width=190) (actual time=461.297..461.297 rows=0 loops=1)
   Index Cond: (category_id = 7)
   Filter: (item_hash = ANY ('{"\\xadfc46d67b03d1d6f3fe87925baee87a", ...
   Heap Fetches: 393963

このポストのタイトル通り、Index Only Scanになっているにも関わらず、Heap Fetchesがあります。また、複合indexがあるのも関わらず、 item_hash がFilterで処理されています。

さらに非常に面白いことに、 category_id を別の数字にすると、理想的なQuery Planが選択されていました。

EXPLAIN ANALYZE SELECT
    category_id,
    item_hash
FROM blogschema.items
WHERE
    category_id IN (2) AND
    item_hash IN ('xadfc46d67b03d1d6f3fe87925baee87a'::BYTEA, ...)
;
Index Only Scan using items_category_id_item_hash_idx on items  (cost=0.43..8.89 rows=1 width=21) (actual time=0.109..0.109 rows=0 loops=1)
  Index Cond: ((category_id = 7) AND (unit_hash = ANY ('{"\\xadfc46d67b03d1d6f3fe87925baee87a", ...
  Heap Fetches: 0

SQLの違いは category_id だけです。しかし、今度は複合indexがうまく使われており、Heap Fetchesも0になっています。結果を得るのにかかった時間は4232分の1まで高速になりました。

解決

大量のUPDATE、INSERTをする処理に入る前に VACUUM ANALYZE を実行してやることで、この症状が現れなくなりました。

VACUUM ANALYZE blogschema.items;

これで解決したということは、大量のUPDATE、INSERTを行うと統計情報が古くなってしまうことがあるようです。 ドキュメント には

更新頻度が多いテーブルでは、VACUUMを定期的に実行する必要があります。

とありますが、 VACUUM ANALYZE による統計情報の更新というよりは、 VACUUM による不要領域回収の意味が強いように読み取れます。私は VACUUM ANALYZE を使用しましたが、 VACUUM が必要ないのならば ANALYZE だけでも効果があるかもしれません。また、私の例では検索に category_iditem_hash しか使っていないので、

VACUUM ANALYZE blogschema.items (category_id, item_hash);

このようにカラム絞ることでより効率化できるかもしれません。未検証です。

最終的な処理

PyramidとSQLAlchemyを使っているので Item モデルにクラスメソッド作成しました。

@classmethod
def vacuum_analyze(cls) -> None:
    ISOLATION_LEVEL_AUTOCOMMIT = 0
    db_session = sqlalchemy.orm.sessionmaker(bind=engine)()
    db_session.connection().connection.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT)
    db_session.execute('VACUUM ANALYZE blogschema.items;')

VACUUM ANALYZE はトランザクション内で実行できないので、通常使うのとは別にSessionを作成しています。

コメント

2015 - 2017 (c) 成瀬基樹