CREATE FUNCTIONのROWS指定

CREATE FUNCTIONオプションとしてROWSというのが設定できる。
これは(たぶん)複数行を返却する関数において、妥当な実行計画を作成させる手助けとして設定するんだろうけど、一応自分でも確認してみた。
環境は、PostgreSQL 9.3.4。

簡単にSQL関数内でgenerate_series()を使って複数行を返す関数を2つ作ってみる。

[nuko]$ cat func.sql 
CREATE TYPE foo AS (id int, data text);
CREATE OR REPLACE FUNCTION get_row_10() RETURNS SETOF foo AS $$
  SELECT * FROM (SELECT generate_series(1, 10) as id, 'xxxx'::text as data ) AS foo;
$$ LANGUAGE sql;
CREATE OR REPLACE FUNCTION get_row_20() RETURNS SETOF foo AS $$
  SELECT * FROM (SELECT generate_series(1, 20) as id, 'xxxx'::text as data ) AS foo;
$$ LANGUAGE sql;

で、その2つの関数の結果をJOINさせてみる。

func=# EXPLAIN ANALYZE SELECT * FROM  get_row_10() as gr1, get_row_20() as gr2 WHERE gr1.id = gr2.id;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=120.16..200.16 rows=5000 width=72) (actual time=0.327..0.333 rows=10 loops=1)
   Merge Cond: (gr1.id = gr2.id)
   ->  Sort  (cost=60.08..62.58 rows=1000 width=36) (actual time=0.238..0.238 rows=10 loops=1)
         Sort Key: gr1.id
         Sort Method: quicksort  Memory: 25kB
         ->  Function Scan on get_row_10 gr1  (cost=0.25..10.25 rows=1000 width=36) (actual time=0.210..0.211 rows=10 loops=1)
   ->  Sort  (cost=60.08..62.58 rows=1000 width=36) (actual time=0.086..0.086 rows=11 loops=1)
         Sort Key: gr2.id
         Sort Method: quicksort  Memory: 25kB
         ->  Function Scan on get_row_20 gr2  (cost=0.25..10.25 rows=1000 width=36) (actual time=0.077..0.079 rows=20 loops=1)
 Total runtime: 0.402 ms
(11 rows)

gr1, gr2ともにデフォルトのままだと推定行数は1000になる。今の環境ではこのくらいの数だとMergeJoinが選択されるのな。

で、次にCREATE FUNCTIONのオプションとしてROWSを設定してみる。gr1には10、gr2には20と正確な値をとりあえず設定しておく。

$ cat func2.sql 
CREATE TYPE foo AS (id int, data text);
CREATE OR REPLACE FUNCTION get_row_10() RETURNS SETOF foo AS $$
  SELECT * FROM (SELECT generate_series(1, 10) as id, 'xxxx'::text as data ) AS foo;
$$ LANGUAGE sql ROWS 10;
CREATE OR REPLACE FUNCTION get_row_20() RETURNS SETOF foo AS $$
  SELECT * FROM (SELECT generate_series(1, 20) as id, 'xxxx'::text as data ) AS foo;
$$ LANGUAGE sql ROWS 20;

で、さっきと同じクエリを実行してみる。

func2=# EXPLAIN ANALYZE SELECT * FROM  get_row_10() as gr1, get_row_20() as gr2 WHERE gr1.id = gr2.id;
                                                         QUERY PLAN                                                         
----------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=0.72..1.10 rows=10 width=72) (actual time=0.322..0.329 rows=10 loops=1)
   Hash Cond: (gr2.id = gr1.id)
   ->  Function Scan on get_row_20 gr2  (cost=0.25..0.45 rows=20 width=36) (actual time=0.207..0.208 rows=20 loops=1)
   ->  Hash  (cost=0.35..0.35 rows=10 width=36) (actual time=0.077..0.077 rows=10 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 1kB
         ->  Function Scan on get_row_10 gr1  (cost=0.25..0.35 rows=10 width=36) (actual time=0.072..0.072 rows=10 loops=1)
 Total runtime: 0.375 ms
(7 rows)

プランが変わってHashJoinになった。gr1,gr2の推定行数も設定した値どおりになる。このくらいの件数だとHashJoinになるのな。
コストもactual timeも一応は削減されている。