[pgsql-jp: 41403] Re: 複数のテーブルを参照する関数について

[Tomoaki Sato]

佐藤です。

> こんにちは、高田と申します。
> 関数とそのインデックスについて質問させてください。
> 
> 以下の様な「商品マスタ」と「ページマスタ」を用意して、各ページで表示
> 可能な商品を抽出することを想定しています。
> 
> 【m_product】
> id|name|user|copy_of
> 1001|商品A|100|null
> 1002|商品B|101|1001
> 1003|商品C|100|null
> 
> 【m_page】
> pageid|user
> 10|100
> 11|101
> 12|102
> 
> AからCの商品うち、AとBは同一の商品で登録したユーザが違っており、BはA
> の「コピー」という位置づけです。
> 基本的にはコピー以外の全ての商品を抽出しますが、もし自分が登録したコ
> ピーが存在する場合はそちらを優先表示する、という意図です。
> 例）
> ユーザA（ID:100）が管理するページ（ID:10）には、商品Aと商品C
> ユーザB（ID:101）が管理するページ（ID:11）には、商品Bと商品C
> ユーザC（ID:102）が管理するページ（ID:12）には、商品Aと商品C
> 
> この内容で商品を抽出する目的で下記のような関数を用意しました。
> 商品IDとページIDを引数とし、その商品を表示可であれば真、表示不可であ
> れば偽を返します。
> 
> select
> case
> when m_product.user = m_page.user then true
> when m_product.user <> m_page.user and m_product.id not in
> ( select copy_of from m_product s1 where s1.user = m_page.user and
> s1.copy_of is not null ) then true
> else false
> end
> from
> m_product
> left join m_page on m_page.pageid = $2
> where m_product.id = $1
> 
> より高速に実現できればいいのですが、仮にこの関数を使用するとして、イ
> ンデクスなどを設定して処理を高速化することは可能でしょうか。
> また、可能であればどのように設定すると高速に処理することができるもの
> でしょうか。

この関数を使うとなると、インデックスを張っても速くするのは難しいです。

この関数を使ってあるページに表示できる商品の一覧を取り出す SQL 文は以下
のようになると思います。

  select id from m_product where 関数(id, ページID);

これだと、商品 1 件ごとに関数が呼び出され、かつ、関数内で相関副問い合わ
せを使っているため、商品 1 件ごとに副問い合わせが呼び出され、件数が増え
るとすごく時間がかかるようになります。

例えば、以下のように書き換えるといいでしょう。

  select id
  from m_product
  where id not in (
      select copy_of
      from m_product
      where "user" = (select "user" from m_page where pageid = ページID)
          and copy_of is not null
  );

試しに m_page テーブルに 1,000 件、m_product テーブルに 200,000 件のデー
タ (copy_of 列が NULL のデータが半分) を入れた状態で比べてみると、

                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on m_product pr3  (cost=0.00..390643321.00 rows=100000 width=4) (actual time=0.065..2558742.985 rows=199900 loops=1)
   Filter: (SubPlan 2)
   Rows Removed by Filter: 100
   SubPlan 2
     ->  Nested Loop Left Join  (cost=0.00..1953.20 rows=1 width=12) (actual time=12.780..12.787 rows=1 loops=200000)
           ->  Index Scan using m_product_pkey on m_product pr  (cost=0.00..8.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=200000)
                 Index Cond: (id = pr3.id)
           ->  Index Scan using m_page_pkey on m_page pa  (cost=0.00..8.27 rows=1 width=4) (actual time=0.004..0.007 rows=1 loops=200000)
                 Index Cond: (pageid = 1)
           SubPlan 1
             ->  Seq Scan on m_product pr2  (cost=0.00..3873.00 rows=100 width=4) (actual time=3.875..12.698 rows=100 loops=199800)
                   Filter: ((copy_of IS NOT NULL) AND ("user" = pa."user"))
                   Rows Removed by Filter: 199875
 Total runtime: 2558953.464 ms
(14 行)

                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on m_product  (cost=3881.52..7754.52 rows=100000 width=4) (actual time=57.826..223.150 rows=199900 loops=1)
   Filter: (NOT (hashed SubPlan 2))
   Rows Removed by Filter: 100
   SubPlan 2
     ->  Seq Scan on m_product  (cost=8.27..3881.27 rows=100 width=4) (actual time=20.376..57.089 rows=100 loops=1)
           Filter: ((copy_of IS NOT NULL) AND ("user" = $0))
           Rows Removed by Filter: 199900
           InitPlan 1 (returns $0)
             ->  Index Scan using m_page_pkey on m_page  (cost=0.00..8.27 rows=1 width=4) (actual time=0.023..0.026 rows=1 loops=1)
                   Index Cond: (pageid = 1)
 Total runtime: 350.685 ms
(11 行)

2558953 ミリ秒 = 42 分を 350 ミリ秒まで速くできました。ちなみに関数は中
の実行計画を見たかったので、以下のように展開して呼び出しています。

explain analyze
select id
from m_product as pr3
where (
    select case when pr.user = pa.user then true
        when pr.user <> pa.user and pr.id not in (
                select copy_of
                from m_product as pr2
                where pr2.user = pa.user
                    and pr2.copy_of is not null
            ) then true
            else false
        end
    from m_product as pr
        left join m_page as pa on pa.pageid = 1
    where pr.id = pr3.id
);

あと、列名に user は使わないほうがいいです。予約語だからということもあ
りますが、" で括らないと現在のユーザ名 (= current_user) が返ってきます。

  test=# select user from m_page limit 1;
   current_user
  --------------
   postgres
  (1 行)
  
  test=# select "user" from m_page limit 1;
   user
  ------
      1
  (1 行)


----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan