スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

PL/pgSQLで動的SQLを書く必要がなくなった

ちょっと古いですけどPostgreSQL9.2のリリースノートに

「プリペアド文が使われる場合であっても、特定のパラメータ値のための特別な計画をプランナが生成できるようにしました。」

とあります。2番目にあるくらいなので重要だと思うのですが、当時見た私は何のことだかわからずスルーしていたようです。で詳細を見ると、

「以前は、プリペアド文は必ず1つだけの"一般的な"計画があり、それがすべてのパラメータ値に対して使われていました。 これは、明示的な定数値を持つプリペアドでない文で使われる計画に比べ、大幅に劣ることが時々ありました。 これからは、プランナは、特定のパラメータ値に対して特別な計画を生成しようとします。 特別な計画による利点がないことが繰り返し証明された後になって初めて、一般的な計画が使われます。 この変更により、以前からあった、(PL/pgSQLの動的でない文を含め)プリペアド文を使うことによるパフォーマンスの不利を排除できます。」

これ重要なのは最後の文章で、以前はPL/pgSQLを使うと動的でない素のSQLを書くとインデックスが効かずにまるでパフォーマンスがでませんでした。で、ほとんどすべて動的SQLで書くことになっていました。
ところが、このリリースノートによればそれが解消されているとなります。そうなると直感的に動的SQLを使っているほうが、都度SQLがパースされるため重くなるんじゃないかと思いました。

ということで、検証してみました。以下、使用したSQLです。
DROP TABLE IF EXISTS t_user CASCADE;
CREATE TABLE t_user (
id BIGINT
,nm TEXT
,PRIMARY KEY(id)
);

CREATE OR REPLACE FUNCTION insert_test(
p_count BIGINT DEFAULT NULL
) RETURNS VOID AS $FUNCTION$
DECLARE
BEGIN
FOR i IN 1..p_count LOOP
INSERT INTO t_user (
id
,nm
) VALUES (
i
,i::TEXT
);
END LOOP;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test1(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM t_user WHERE nm = p_id::TEXT;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test2(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM t_user WHERE id = p_id;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test3(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY EXECUTE $$
SELECT * FROM t_user WHERE id = $1
$$ USING
p_id
;
END;
$FUNCTION$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION sel_test4(
p_id BIGINT DEFAULT NULL
) RETURNS SETOF t_user AS $FUNCTION$
DECLARE
BEGIN
RETURN QUERY SELECT * FROM sel_test2(p_id);
END;
$FUNCTION$ LANGUAGE plpgsql;

これをrubyから呼んでみます。
require 'sequel'

DB = Sequel.connect('postgres://localhost/test')

def select(n)
ds = DB["SELECT * FROM sel_test4(?)", :$id]
n.times do |i|
ds.call(:select, id: i)
end
end

s = Time.now
p s

select(100000)

e = Time.now
p e
puts "#{e - s} sec"

データは10,000,000件作成。(77.97秒)
select insert_test(10000000)

sel_test2,3,4を100,000回呼び出してみました。
sel_test2 45.524594 sec
sel_test3 53.544521 sec
sel_test4 45.996167 sec

結論としては、PL/pgSQLでは素のSQLが速度が速いです。PL/pgSQLを多段に呼ぶのはそんなに重くないようです。動的SQLは確かに遅いですが、そこまで速度にシビアでなければ便利に利用すれば良いと思います。
スポンサーサイト
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。