PostgreSQLチューニングテクニック集

  1. SELECT ... WHERE foo = 100 ORDER BY barをSELECT ... WHERE foo = 100 ORDER BY i, jに書き換える(2004/08/18掲載)

    SELECT * FROM t1 WHERE i = 100 ORDER BY j; 
    
    のようなパターンの検索では,iにインデックスを貼ることである程度の高 速化ができますが,その後でソートが発生し,速度低下の要因になります.
    SELECT * FROM t1 WHERE i = 100 ORDER BY j LIMIT 5; 
    
    のように,出力結果件数を制限しかつjにインデックスを貼ると,今度はjのイ ンデックスを使うようになります.

    しかし,よく考えてみると,iで検索し,jでソートするのはiとjのインデック スの両方を使えば一度にできるはずです.残念ながら,PostgreSQLはそこまで 賢くないので,そのようなプランは作ってくれません. こういう場合は,iとjの複合インデックスを作ると効果があります.すなわち,

    CREATE INDEX t1ijindex ON t1(i,j);
    
    のようにします.こうすると,以下のようなプランを作ってくれます.ソート が発生しないので,高速になります.
    test2=# EXPLAIN ANALYZE SELECT * FROM t1 WHERE i BETWEEN 1 AND 10000 ORDER BY i,j;
                                                            QUERY PLAN                                                        
    --------------------------------------------------------------------------------------------------------------------------
     Index Scan using t1ijindex on t1  (cost=0.00..2331.55 rows=10487 width=8) (actual time=0.021..22.519 rows=10486 loops=1)
       Index Cond: ((i >= 1) AND (i <= 10000))
     Total runtime: 28.760 ms
    (3 rows)
    

    参考: 本家ML

    Subject: Re: [PERFORM] Hardware upgrade for a high-traffic database 
    From: Tom Lane 
    To: "Jason Coene" 
    Cc: "'Merlin Moncure'" ,
            pgsql-performance@postgresql.org
    Date: Wed, 11 Aug 2004 19:20:04 -0400
    

  2. SELECT DISTINCTをSELECT ... GROUP BYに置き換える(7.4以降)(2004/07/03掲載)

    SELECT DISTINCTは,対象になる列にインデックスが貼ってない限りソートが 発生します.このとき使われるPostgreSQLのソートアルゴリズムは汎用的なも のであるせいか,あまり効率の良いものではなく,特に重複が多いときに商用 DBに比べると性能が悪いケースがあります.pgbenchで作ったデータを使って 例を示します.

    test=# EXPLAIN ANALYZE SELECT DISTINCT ON (bid) * FROM accounts;
                                                             QUERY PLAN                                                          
    -----------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=11668.82..12168.82 rows=1 width=4) (actual time=654.852..893.505 rows=1 loops=1)
       ->  Sort  (cost=11668.82..11918.82 rows=100000 width=4) (actual time=654.846..798.409 rows=100000 loops=1)
             Sort Key: bid
             ->  Seq Scan on accounts  (cost=0.00..2640.00 rows=100000 width=4) (actual time=0.087..363.120 rows=100000 loops=1)
     Total runtime: 1011.457 ms
    (5 rows)
    
    

    このようにソートが行われ,1秒ほど時間がかかっています.すべてのbidが重 複しており,つまり10万件の重複データがあるという極端な例です.こういう 場合,商用DBは重複を排除しながらソートを行うため,もっと効率がよいそう です.

    この問い合わせを高速化する方法としてまず思いつくのはインデックスの作成 です.bidにインデックスを作成すると確かに高速化します.

    test=# CREATE INDEX bidindex ON accounts(bid);
    CREATE INDEX
    test=# EXPLAIN ANALYZE SELECT DISTINCT ON (bid) * FROM accounts;
                                                                    QUERY PLAN                                                                
    ------------------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=0.00..3211.00 rows=1 width=100) (actual time=0.107..482.446 rows=1 loops=1)
       ->  Index Scan using bidindex on accounts  (cost=0.00..2961.00 rows=100000 width=100) (actual time=0.103..381.136 rows=100000 loops=1)
     Total runtime: 482.705 ms
    (3 rows)
    

    だいぶ速くなりました.しかし,インデックスを増やすということは,その分 更新処理の負荷が増えるということでもあります.インデックスを使わずに高 速化する手段はないのでしょうか?

    とりあえずsort_memを増やしてみましょう.

    test=# SET sort_mem = 10240;
    SET
    test=# EXPLAIN ANALYZE SELECT DISTINCT ON (bid) * FROM accounts;
                                                              QUERY PLAN                                                           
    -------------------------------------------------------------------------------------------------------------------------------
     Unique  (cost=16514.82..17014.82 rows=1 width=100) (actual time=678.469..945.930 rows=1 loops=1)
       ->  Sort  (cost=16514.82..16764.82 rows=100000 width=100) (actual time=678.462..855.123 rows=100000 loops=1)
             Sort Key: bid
             ->  Seq Scan on accounts  (cost=0.00..2640.00 rows=100000 width=100) (actual time=0.051..270.039 rows=100000 loops=1)
     Total runtime: 954.762 ms
    (5 rows)
    
    どうもさほど効果はなかったようです.

    そこで利用するのが HashAggregateです.PostgreSQL 7.4以降では HashAggregateを使ってGROUP BYなどを高速に実行できます.これを使って問 い合わせを書き換えてみましょう.

    test=# EXPLAIN ANALYZE SELECT bid FROM accounts GROUP BY bid;
                                                          QUERY PLAN                                                       
    -----------------------------------------------------------------------------------------------------------------------
     HashAggregate  (cost=2890.00..2890.00 rows=1 width=4) (actual time=374.987..374.988 rows=1 loops=1)
       ->  Seq Scan on accounts  (cost=0.00..2640.00 rows=100000 width=4) (actual time=0.050..222.040 rows=100000 loops=1)
     Total runtime: 375.512 ms
    (3 rows)
    

    驚いたことに,インデックスを用いたときよりも高速化されています.これは 重複の多いデータではBtreeインデックスはあまり効率よく働かないせいもあ るのでしょう.

    参考: 本家ML

    Subject: Re: [PERFORM] Major differences between oracle and postgres performance - what can I do ? 
    From: Tom Lane 
    To: Gary Cowell 
    Cc: pgsql-performance@postgresql.org
    Date: Fri, 18 Jun 2004 16:47:17 -0400