postgres SQLチューニングpart2【WHERE句での検証】

postgresでSQLの記述で高速化を図ります。Part2
パラメータ調整で多々あるみたいですが効率の良いSQLについて検証してみます。
前回の検証の続きです。
検証内容は前回に似ています。
もっとより実務的なSQLのチューニングをはかります。

---

検証1

サイト毎の合計view数,conversion数を計算

SQL1	SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view WHERE date BETWEEN '2013/11/01' AND '2013/11/30' GROUP BY site_id) AS T2 USING(site_id) ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

上記のSQL1,SQL2を検証します.
前回とほぼ同一なのでシェルの実行等は省きます.


以下が検証結果です。

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	417.611	376.637	420.722	350.816	316.651	322.067	335.734	307.371	385.674	370.905	307.371	420.722	360.418
sql2.sql	321.847	280.912	303.027	263.667	258.189	276.157	310.888	274.68	277.269	265.227	258.189	321.847	283.186

と検証結果はこのようになりました。
先ほどよりやや差は縮まりましたがやはりSQL２の方が早いです。


では、SQL1の記述は遅いのかを親のデータsiteにWHERE句をかけて試します

SQL1	SELECT * FROM (SELECT site_id, site_name, SUM(view) AS view, SUM(conversion) AS conversion FROM site AS T1 INNER JOIN site_view AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' GROUP BY site_id, site_name ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

このようなSQLです、あえて遅くするために正規表現で記載

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	1709.609	1729.138	1714.069	1862.493	1838.969	1752.116	1668.097	1701.067	1749.651	1714.535	1668.097	1862.493	1743.974
sql2.sql	1074.498	1056.323	1087.329	1060.741	1051.747	1133.521	1133.265	1143.876	1087.005	1065.277	1051.747	1143.876	1089.358

極端とまでは行かないが半分近くSQL2が早い良い書き方はこういったように
効率化できそうである。
と後もう１個検証したい・・・

HAVINGを使うかWHEREを使うかである
検証で早いであろうSQL２をベースに以下のないようで検証します

SQL1	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id HAVING SUM(conversion) > 90000) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;
SQL2	SELECT * FROM (SELECT site_id, site_name, view, conversion FROM site AS T1 INNER JOIN (SELECT site_id, SUM(view) AS view, SUM(conversion) AS conversion FROM site_view GROUP BY site_id) AS T2 USING(site_id) WHERE T1.site_name ~ '[0-5]$' AND T2.conversion > 90000 ORDER BY site_id) AS S1 LIMIT 10 OFFSET 0;

HAVINGを利用したSQLがSQL1でWHERE句を利用したのがSQL2である
何方もどっちな気がしなくはないが検証

実行ファイル	1回目	2回目	3回目	4回目	5回目	6回目	7回目	8回目	9回目	10回目	最小	最大	平均
sql1.sql	1180.76	1383.684	1326.127	1376.931	1359.841	1411.559	1368.94	1324.281	1383.121	1211.007	1180.76	1411.559	1332.625
sql2.sql	1359.779	1306.331	1312.314	1350.094	1327.714	1377.518	1405.109	1362.456	1313.428	1146.364	1146.364	1405.109	1326.111

お〜結論何方もどっちであることが分かりました。


仮にPHPやJavaでWHERE句やHAVINGを動的生成するのならSQL2の方がやりやすそうな気がするのでSQL2に軍配あり？ とま〜長々と検証していきました。


結論としては、GROUP BYを先に行いJOINしましょうに限る
今度は、月別レポートの作成で検証したいと思います。