パート1.衝突しないでください/更新と行レベルのセキュリティ 。
パート3.グループ化セット、キューブ、ロールアップ
著者から
第二部のリリースが遅れたことをおaび申し上げます。 最初は、最初の記事の1週間後に記事の2番目の部分をリリースする予定でしたが、作業負荷が高いため、これを行うことができませんでした。 したがって、私は大きな記事ではなく、小さな部分で、より頻繁に発行することにしました。
テーブルからいくつかのランダムエントリを選択する必要があるタスクがある場合があります。このために、洗練されたクエリを作成しました（本当にランダムなデータを取得するために、たくさん汗をかく必要があります）。 PostgreSQL 9.5のリリースにより、このタスクはより簡単になります。
TABLESAMPLEキーワードを使用すると、テーブルからすべてのデータを選択することはできませんが、サンプルの一部を選択することができます。
構文は次のようになります。
SELECT ... FROM TABLE_NAME ... TABLESAMPLE sampling_method （ argument [ 、 ... ] ） [ REPEATABLE （ seed ） ]
sampling_methodはサンプリングメソッドであり、PostgreSQL 9.5ではデフォルトでSYSTEMとBERNOULLIの 2つがあります。これらは引数として浮動小数点数（または数値を生成する有効な式）を取り、サンプリングのパーセンテージとして解釈されます：0から100まで。
PostgreSQL 9.5でサンプリングがどのように機能するかの例を見てみましょう。
トランザクションID、トランザクション量、およびトランザクションが完了した日付と時刻が保存されているトランザクションを含むテーブルがあるとします。 テーブルに100,000エントリを追加します。
CREATE TABLEトランザクション（
idシリアルプライマリ キー 、
量NUMERIC （ 15、2 ） 、
ending_time TIMESTAMP
） ;
INSERT INTOトランザクション（ amount 、 ending_time ）
選択
（ラウンド（ CAST （ random （ ） * 100000 AS NUMERIC ） 、 2 ） ）） 、
now （ ） - random （ ） * CAST （ '1 day' AS INTERVAL ）
FROM generate_series （ 1、100000 ） ;
元のテーブル（100レコード）から0.1％のレコードのサンプルを取得してみましょう。
SELECT * FROMトランザクションTABLESAMPLE SYSTEM （ 0.1 ）
合計クエリランタイム： 213ミリ秒。
157行が取得されました。
なぜ100レコードを取得できなかったのに157 事実、PostgreSQL はテーブルデータを8 kbページの配列の形式で保存し （デフォルトでは、ソースコードからサーバーを構築するときにこのパラメーターを変更できます）、サンプリングメソッドSYSTEMを使用すると、指定されたパーセント数のランダムページ数を取得し、 「そのままです。」 この場合、157エントリが1ページに配置されます。 サンプルに対して2倍のレコードを要求すると、2ページのデータが取得されます。
SELECT * FROMトランザクションTABLESAMPLE SYSTEM （ 0.2 ）
合計クエリランタイム： 21ミリ秒。
314行が取得されました。
異なるページには異なる数のレコードを格納できるため、返されるレコードの数はリクエストごとに異なる可能性があることを理解してください。
レコードの正確な数を取得するには、 LIMIT式を使用できますが、この場合はいずれにしても、1ページからレコードを取得することを理解する価値があります。 したがって、レコードの値がこれらのレコードが挿入された順序に依存している場合、またはレコード自体の値の性質が時系列である場合（この場合、ending_timeフィールドの場合のように）、サンプルを作成することで無意味な結果が得られる可能性が高くなります。 たとえば、サンプルを介してトランザクションが行われた最大日付を調べて、同じリクエストを数回行うと、まったく異なる結果が得られます。
SELECT MAX （ ending_time ） FROMトランザクションTABLESAMPLE SYSTEM （ 0.1 ）
| 最大 |
|---|
| 2014-11-08 22：30：32.720855 |
SELECT MAX （ ending_time ） FROMトランザクションTABLESAMPLE SYSTEM （ 0.1 ）
| 最大 |
|---|
| 2014-12-02 11：42：32.720855 |
SELECT MAX （ ending_time ） FROMトランザクションTABLESAMPLE SYSTEM （ 0.1 ）
| 最大 |
|---|
| 2014-10-21 09：40：32.720855 |
実際の値は次のとおりです。
SELECT MAX （ ending_time ） FROMトランザクション
| 最大 |
|---|
| 2014-12-07 04：04：32.720855 |
より分散されたサンプルを取得するには、 BERNOULLIサンプリングメソッドを使用して、テーブル全体をスキャンし（実際、各レコードについて「コインを投げる」）、ランダムなレコードを選択します。
SELECT MAX （ ending_time ） FROMトランザクションTABLESAMPLE BERNOULLI （ 0.1 ）
| 最大 |
|---|
| 2014-12-07 00：06：32.720855 |
次に、パフォーマンスを見て、3つの方法で平均取引額の受領を分析し、平均自体を取得してみましょう。
1）サンプルなし：
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction
「集計（コスト= 1887.00..1887.01行= 1幅= 8）（実際の時間= 25.795..25.795行= 1ループ= 1）」
「->トランザクションのシーケンススキャン（コスト= 0.00..1637.00行= 100000幅= 8）（実際の時間= 0.005..12.438行= 100000ループ= 1）」
「計画時間：0.055ミリ秒」
「実行時間：25.816ミリ秒」
トランザクションからSELECT AVG （金額）
50028.8742828
2） SYSTEMメソッドによるサンプル：
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE SYSTEM （ 0.1 ）
「集計（コスト= 1.25..1.26行= 1幅= 8）（実際の時間= 0.088..0.088行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（システム）（コスト= 0.00..1.00行= 100幅= 8）（実際の時間= 0.017..0.048行= 157ループ= 1）」
「計画時間：0.068ミリ秒」
「実行時間：0.120 ms」
トランザクションTABLESAMPLEシステムからのAVG （金額）の 選択 （ 0.1 ）
53628.223694267516
3） BERNOULLIサンプル ：
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE BERNOULLI （ 0.1 ）
「集計（コスト= 638.25..638.26行= 1幅= 8）（実際の時間= 2.847..2.847行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（bernoulli）（コスト= 0.00..638.00行= 100幅= 8）（実際の時間= 0.020..2.780行= 104ループ= 1）」
「計画時間：0.145ミリ秒」
「実行時間：2.872ミリ秒」
トランザクションからのAVG （金額）の 選択 TABLESAMPLE BERNOULLI （ 0.1 ）
50285.863240740741
SYSTEMメソッドを使用したサンプリングは高速ですが、同時にその精度は低く、 BERNOULLIメソッドによるサンプリングは低速ですが、精度は高いことがわかります。 速度と精度の間で妥協点を選択できます。 また、サンプリングには新しいタイプのスキャン（サンプルスキャン）が使用されることに注意してください。
テーブルにさらにレコードを追加し、2,000万件のレコードを追加します。
INSERT INTOトランザクション（ amount 、 ending_time ）
選択
（ラウンド（ CAST （ random （ ） * 100000 AS DECIMAL ） 、 2 ） ）） 、
今（ ） - 間隔 '1年' + （ i * 間隔 '1分' ）
FROM generate_series （ 100001、20000000 ） i;
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction;
「集計（コスト= 377372.70..377372.71行= 1幅= 8）（実際の時間= 4604.297..4604.297行= 1ループ= 1）」
"->トランザクションのシーケンススキャン（コスト= 0.00..327375.96行= 19998696幅= 8）（実際の時間= 0.027..2043.846行= 20,000,000ループ= 1）"
「計画時間：0.063ミリ秒」
「実行時間：4604.325 ms」
SELECT AVG （ amount ） FROM transaction;
50002.888681451
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE SYSTEM （ 0.1 ）
「集計（コスト= 757.99..758.00行= 1幅= 8）（実際の時間= 7.309..7.309行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（システム）（コスト= 0.00..707.99行= 19999幅= 8）（実際の時間= 0.057..4.588行= 20096ループ= 1）」
「計画時間：0.073ミリ秒」
「実行時間：7.340ミリ秒」
トランザクションTABLESAMPLEシステムからのAVG （金額）の 選択 （ 0.1 ）
50323.198322551752
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE BERNOULLI （ 0.1 ）
「集計（コスト= 127638.99..127639.00行= 1幅= 8）（実際の時間= 751.831..751.832行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（bernoulli）（コスト= 0.00..127588.99行= 19999幅= 8）（実際の時間= 0.260..747.682行= 19899ループ= 1）」
「計画時間：0.055ミリ秒」
「実行時間：751.879ミリ秒」
トランザクションからのAVG （金額）の 選択 TABLESAMPLE BERNOULLI （ 0.1 ）
50043.386386377336
レコード数が増えると、 BERNOULLIメソッドのパフォーマンスが低下することがわかります。 これは、実際には全テーブルスキャンを実行するのに対し、 SYSTEMは数ページを返すだけだからです。
次に、レコードを選択する割合を増やしてみましょう。
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE SYSTEM （ 1 ）
「集計（コスト= 7591.84..7591.85行= 1幅= 8）（実際の時間= 65.055..65.055行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（システム）（コスト= 0.00..7091.87行= 199987幅= 8）（実際の時間= 0.043..37.939行= 200018ループ= 1）」
「計画時間：0.053ミリ秒」
「実行時間：65.083ミリ秒」
EXPLAIN ANALYZE SELECT AVG （ amount ） from transaction TABLESAMPLE BERNOULLI （ 1 ）
「集計（コスト= 129888.84..129888.85行= 1幅= 8）（実際の時間= 799.826..799.826行= 1ループ= 1）」
「->トランザクションのサンプルスキャン（bernoulli）（コスト= 0.00..129388.87行= 199987幅= 8）（実際の時間= 0.035..769.899行= 199682ループ= 1）」
「計画時間：0.063ミリ秒」
「実行時間：799.859ミリ秒」
ご覧のとおり、 SYSTEMメソッドは、サンプルの割合を増やすとパフォーマンスが低下します。 これは論理的です。BERNOULLIはフルスキャンと同じように動作し、 SYSTEMは10倍のページを返す必要があるためです。
その結果、サンプルのごく一部のSYSTEMメソッドはBERNOULLIよりもはるかに高速に動作しますが、同時にレコードのランダムな選択が少なくなることに注意できます。 しかし、関心が高まると、この利点は失われます。
オプションのREPEATABLEキーワードを使用して、ランダムジェネレーターのシードを指定できます。 2つのクエリのサンプリング方法、サンプリングの割合、およびseedが同じ場合、これらの2つのクエリに対して同じサンプルが選択されます。
トランザクションテーブルサンプルシステムからのMAX （金額）の 選択 （ 0.1 ）繰り返し可能（ 50 ）
99997.91
トランザクションテーブルサンプルシステムからのMAX （金額）の 選択 （ 0.1 ）繰り返し可能（ 300 ）
99999.15
トランザクションからのMAX （金額）の 選択 TABLESAMPLE BERNOULLI （ 0.1 ）繰り返し可能（ 50 ）
99995.9
トランザクションテーブルサンプルシステムからのMAX （金額）の 選択 （ 0.1 ）繰り返し可能（ 50 ）
99997.91
上で見たように、 REPEATABLEキーワードが指定されていない場合、毎回選択が前のものと異なります。
これとは別に、 WHERE句の前にサンプリングが実行されることに注意してください。つまり、条件によってサンプルを選択することはできません。 この場合、サンプルが最初に選択され、次にWHERE条件が適用されますが、20,000,000レコードのテーブルからid <100のレコードを取得する確率は非常に小さいため、結果は空になります。
SELECT * FROMトランザクションTABLESAMPLE SYSTEM （ 1 ） WHERE id < 100
合計クエリランタイム： 31ミリ秒。
0行が取得されました。
SYSTEMとBERNOULLIだけがサンプリングオプションではありません。必要に応じて、独自のサンプリング方法を作成できます。 このドキュメントはこちらにあります 。 さらに、カスタムサンプリングメソッドは、複数の引数をとるか、まったくとらない場合があります。 カスタムメソッドは、 REPEATABLEキーワードも無視する場合があります。
これで、PostgreSQL 9.5でのサンプリングに関する短い話は終わりです。 ご清聴ありがとうございました！
PSサンプルを使用して、テーブル内のレコード数を概算できます:)
SELECT COUNT （ * ） * 100.0 FROMトランザクションTABLESAMPLE SYSTEM （ 1 ） ;
20001800
PPS上記のようにしないでください。これは冗談であり、常に正常に機能するとは限りません。
次の部分： GROUPINS SETS、ROLLUP、CUBE