PostgreSQLクエリの実行時間取得

質問内容

DECLARE @StartTime datetime,@EndTime datetime

SELECT @StartTime=GETDATE()

select distinct born_on.name
from   born_on,died_on
where (FLOOR(('2012-01-30'-born_on.DOB)/365.25) <= (
    select max(FLOOR((died_on.DOD - born_on.DOB)/365.25))
    from   died_on, born_on
    where (died_on.name=born_on.name))
    )
and   (born_on.name <> All(select name from died_on))

SELECT @EndTime=GETDATE()

SELECT DATEDIFF(ms,@StartTime,@EndTime) AS [Duration in millisecs]

クエリタイムを取得することができません。代わりに以下のエラーが表示されます。

sql:/home/an/Desktop/dbms/query.sql:9: ERROR:  syntax error at or near "@"
LINE 1: DECLARE @StartTime datetime,@EndTime datetime

どのように解決するのですか？

実行時間を測定する方法はさまざまで、それぞれに長所と短所があります。しかし、何をするにしても、ある程度は オブザーバ効果 が適用されます。つまり、測定そのものが結果を歪めてしまう可能性があるのです。

1. EXPLAIN ANALYZE

を前置することができます。 EXPLAIN ANALYZE これは、推定コストと実際に計測された時間を含むクエリプラン全体を報告します。クエリは 実際に実行される (副作用があればそれも含めて!) です。動作は SELECT , INSERT , UPDATE , DELETE .

私が適合させたバージョンのクエリが、実際に速くなったかどうかを確認します。

EXPLAIN ANALYZE
SELECT DISTINCT born_on.name
FROM   born_on b
WHERE  date '2012-01-30' - b.dob <= (
    SELECT max(d1.dod - b1.dob)
    FROM   born_on b1
    JOIN   died_on d1 USING (name)  -- name must be unique!
    )
AND NOT EXISTS (
    SELECT FROM died_on d2
    WHERE  d2.name = b.name
    );

ウォームキャッシュでより比較可能な時間を得るために、2回ほど実行します。 いくつかのオプション があり、詳細度を調整することができます。

主に興味があるのは 総実行時間 であれば、作ってください。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)

大抵の場合 TIMING が重要です -。 マニュアルを

<ブロッククオート

TIMING

実際の起動時間や各ノードでの滞在時間を出力に含めます。 システムクロックを繰り返し読み取るオーバーヘッドは、システムによってはクエリの速度を著しく低下させます。 クエリの速度を著しく低下させる可能性があるため、このパラメータを パラメータを FALSE に設定すると便利です。 に設定すると便利です。ステートメント全体の実行時間は、このオプションでノードレベルのタイミングをオフにした場合でも、常に測定されます。 このオプションでノードレベルのタイミングをオフにした場合でも、ステートメント全体の実行時間は常に測定されます。[...]

EXPLAIN ANALYZE 対策 サーバ上で で、サーバOSのサーバ時刻を使用します。 ネットワーク遅延を除く . しかし EXPLAIN はクエリプランを出力するために若干のオーバーヘッドを追加します。

2. psql で \timing

または \timing を psql で使用することもできます。 ピーターが実演しているように

マニュアルです。

<ブロッククオート

\timing [ on | off ]

パラメータを指定すると、各SQL文の所要時間の表示を の表示をオンまたはオフにします。パラメータを指定しない場合は、表示のオン・オフを切り替えます。 とOFFを切り替えます。表示はミリ秒単位で、1秒以上の場合は分：秒の形式で表示されます。 秒以上の場合は、分：秒の形式で表示されます。 フィールドが追加されます。

重要な違いです。 psqlのメジャー クライアント上で はローカル OS からのローカルタイムを使用しているので、時間 はネットワーク遅延を含む . これは無視できるほどの差であったり 巨大な になる可能性があります。

3. 有効化 log_duration

これは、おそらく測定あたりのオーバーヘッドが最も少なく、最も歪んだタイミングを生成します。しかし、スーパーユーザーでなければならず、サーバーの設定を調整しなければならず、単一のクエリの実行だけを対象とすることはできず、サーバーログを読まなければならないので、少し強引です (ただし、リダイレクトして stdout ).

マニュアルです。

<ブロッククオート

log_duration ( boolean )

完了したステートメントの期間をログに記録するようにします。デフォルトは デフォルトは off . スーパーユーザのみがこの設定を変更することができます。

拡張クエリプロトコルを使用するクライアントでは、Parse、Bind、およびExecuteの各ステップの継続時間は独立して記録されます。 バインド、および実行の各ステップは独立してログに記録されます。

のような関連する設定があります。 log_min_duration_statement .

4. による精密な手動測定 clock_timestamp()

マニュアルです。

<ブロッククオート

clock_timestamp() は実際の現在時刻を返すので、その値は1つのSQLコマンド内でも変化します。

filiprem提供 は、アドホッククエリの実行時間を可能な限り正確に取得するための素晴らしい方法です。最新のハードウェアでは、タイミングのオーバーヘッド は は重要ではありませんが、ホスト OS によっては大きく変化することがあります。サーバー アプリケーションで確認する pg_test_timing .

その他は、ほとんどこのようにオーバーヘッドをフィルタリングすることができます。

DO
$do$
DECLARE
   _timing1  timestamptz;
   _start_ts timestamptz;
   _end_ts   timestamptz;
   _overhead numeric;     -- in ms
   _timing   numeric;     -- in ms
BEGIN
   _timing1  := clock_timestamp();
   _start_ts := clock_timestamp();
   _end_ts   := clock_timestamp();
   -- take minimum duration as conservative estimate
   _overhead := 1000 * extract(epoch FROM LEAST(_start_ts - _timing1
                                              , _end_ts   - _start_ts));

   _start_ts := clock_timestamp();
   PERFORM 1;  -- your query here, replacing the outer SELECT with PERFORM
   _end_ts   := clock_timestamp();
   
-- RAISE NOTICE 'Timing overhead in ms = %', _overhead;
   RAISE NOTICE 'Execution time in ms = %' , 1000 * (extract(epoch FROM _end_ts - _start_ts)) - _overhead;
END
$do$;

繰り返し時間をとり(ここでは3つのタイムスタンプで最低限)、最小間隔を選んでタイミングオーバーヘッドの保守的な見積もりとします。また、関数を実行することで clock_timestamp() を数回実行することで、ウォームアップすることができます(お使いのOSにとって重要な場合)。

ペイロードクエリの実行時間を測定した後、実際の時間に近づけるために推定オーバーヘッドを差し引きます。

もちろん、安いクエリでは100000回ループしたり、できることなら100000行のテーブルで実行したほうが、気が散るノイズを取る意味があるのですが。