ぱと隊長日誌

ブログ運用もエンジニアとしての生き方も模索中

PostgreSQL の式インデックスも統計情報の収集対象となる

PostgreSQL の式インデックス(関数インデックス)もテーブルと同様に統計情報の収集対象です。よって、式インデックス作成後に ANALYZE を実行しないと、正しく活用されません。また、pg_stats ビューは式インデックスの統計情報も参照できます。

PostgreSQL のインデックスは列値そのものだけでなく、列値を用いた演算結果も対象にできます。

インデックス列は、基礎をなすテーブルにある列である必要はなく、そのテーブルの1つ以上の列から計算される関数やスカラ式とすることもできます。この機能は、ある演算結果に基づいた高速テーブルアクセスを行う時に有用です。

11.7. 式に対するインデックス

式インデックスも統計情報の収集対象です。なお、通常の(列値に対する)インデックスは統計情報の収集対象外です。

pg_statisticはインデックス式の値についての統計データも格納します。これらはあたかも値が実際のデータ列であるかのように表現されます。特にstarelidはインデックスを参照します。これは元のテーブル列の項目に対して冗長となるので、普通の式を持たないインデックス列では項目は作成されません。現在インデックス式用の項目は常にstainherit = falseを持ちます。

51.49. pg_statistic

式インデックスの統計情報もプランナが参照します。よって、式インデックス作成後に ANALYZE を実行しないと、式インデックスを適切に利用したプランが得られません。

また、pg_stats ビューのマニュアルからはテーブルの統計情報のみ参照できるように読めますが、実際には式インデックスの統計情報も参照できます。
51.88. pg_stats

以降で実際の挙動を確認します。

検証環境は以下の通りです。

テーブルを作成し、データを投入した後、VACUUM ANALYZE を実行します。その後、通常インデックスと式インデックスを作成します。

インデックス作成後の ANALYZE 実行有無によって、各インデックスを利用するクエリのプランがどうなるかを確認します。

=# CREATE TABLE tab1 (id integer);

=# INSERT INTO tab1 SELECT generate_series(1, 100000);

=# VACUUM ANALYZE tab1;

=# CREATE INDEX tab1_id_index ON tab1 (id);

=# CREATE INDEX tab1_id_bigint_index ON tab1 (CAST(id AS bigint));

-- (1) 通常インデックスかつANALYZE前
=# EXPLAIN SELECT * FROM tab1 WHERE id < 100;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Only Scan using tab1_id_index on tab1  (cost=0.29..6.06 rows=101 width=4)
   Index Cond: (id < 100)
(2 rows)

-- (2) 式インデックスかつANALYZE前
=# EXPLAIN SELECT * FROM tab1 WHERE CAST(id AS bigint) < 100;
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Bitmap Heap Scan on tab1  (cost=626.62..1569.62 rows=33333 width=4)
   Recheck Cond: ((id)::bigint < 100)
   ->  Bitmap Index Scan on tab1_id_bigint_index  (cost=0.00..618.29 rows=33333 width=0)
         Index Cond: ((id)::bigint < 100)
(4 rows)

=# ANALYZE tab1;

-- (3) 通常インデックスかつANALYZE後
=# EXPLAIN SELECT * FROM tab1 WHERE id < 100;
                                   QUERY PLAN
--------------------------------------------------------------------------------
 Index Only Scan using tab1_id_index on tab1  (cost=0.29..5.97 rows=96 width=4)
   Index Cond: (id < 100)
(2 rows)

-- (4) 式インデックスかつANALYZE後
testdb=# EXPLAIN SELECT * FROM tab1 WHERE CAST(id AS bigint) < 100;
                                    QUERY PLAN
----------------------------------------------------------------------------------
 Index Scan using tab1_id_bigint_index on tab1  (cost=0.29..9.97 rows=96 width=4)
   Index Cond: ((id)::bigint < 100)
(2 rows)

インデックス作成後に ANALYZE を実行しない場合でも、通常インデックスであれば行数推定は正確でした。ですが、式インデックスの場合は行数推定を大きく外しています。

インデックス作成後に ANALYZE を実行すると、式インデックスの場合でも行数推定が正確になりました。

この検証からもわかるように、式インデックスを作成した後に ANALYZE も実行することが重要といえます。

pg_stats ビューで式インデックスの統計情報も参照できることを確認します。

=# SELECT tablename, attname FROM pg_stats WHERE tablename LIKE '%tab1%';
      tablename       | attname
----------------------+---------
 tab1                 | id
 tab1_id_bigint_index | id
(2 rows)

テーブルと式インデックスの統計情報を参照できました。また、通常インデックスの統計情報が無いことも確認できました。

OSS-DB Silver 合格までの道のり (2022/06/11)

はじめに

2022/06/11 に "OSS-DB Silver Ver.2.0" を受験してきました。そして、無事に合格することができました。

今回の挑戦に向けて、どのような準備を行ったかまとめます。

教材と利用方法

教材の略記は個別に定義しました。

OSS教科書 OSS-DB Silver Ver2.0対応

略記:OSS-DB教科書

書名通り、教科書代わりに使いました。問題集や模擬試験のパートは2回程度解きました。

本書の問題集や模擬試験はひねりの強い傾向があり、本番よりも難しく感じました。なので、問題集としては後述する他の教材を使った方が効率的かもしれません。

Ping-t 最強WEB問題集OSS-DB Silver(Ver2.0)

略記:Ping-t問題集

プレミアムコンテンツのご紹介

プレミアム(有料)コンテンツとして OSS-DB Silver の問題集が提供されています。申し込む利用期間に応じて料金の割引率が高くなるので、勉強の計画を立てたうえで利用したほうが良いです。

解説が丁寧でわかりやすかったです。

間違えた問題だけを繰り返し出題する機能などがあります。また、モバイル対応もされており、通勤通学時にも取り組みやすかったです。

ユーザー登録をすると受験チケット(バウチャー)を割引価格で購入することもできます。
受験チケット(バウチャー)のご紹介 - 割引価格でのご提供 (LinuC / HTML5 / OSS-DB)

サンプル問題/例題解説

サンプル問題/例題解説

略記:公式例題集

試験の開催団体である LPI-Japan から提供されている「Silverの例題解説」はサンプル/例題と思えないほどのボリュームです。また、解説も丁寧です。

勉強時間と進め方

勉強時間の測定には Studyplus のスマホアプリを利用しました。
学習総合サイト Studyplus(スタディプラス)

2022/03 2022/04 2022/05 2022/06 小計
OSS-DB教科書 1.25 9.75 2.5 13.5
Ping-t問題集 10.25 8.75 2.25 21.25
公式例題集 1.5 8.75 18.75 7.25 36.25
小計 2.75 28.75 30.0 9.5 71.0

(単位:時間)

まず、OSS-DB教科書のテキストを読むことから始めました。OSS-DB教科書付属の問題集や模擬試験もこの流れで取り組みましたが、正答率が思った以上に低くて落ち込みました。ただ、本書はひねりの強い問題が多いこともあり、正答率の低さを落ち込む必要はなかったです。

Ping-t問題集はモバイルで使いやすかったので、通勤の合間に勉強しました。細切れ時間しかなかったので、5問とか10問単位で取り組みました。同じ問題に2回連続で正答すると「コンボ」という状態になります。全ての問題が「コンボ」になるまで繰り返し取り組みました。

公式例題集は家で取り組みました。2周目以降は間違えた問題を記録しておき、翌日以降にまた解いて、正答するまで繰り返しました。

試験の振り返り

試験時間は「問題を解く85分」+「アンケート5分」の計90分です。今回は試験会場で受験しました。

試験会場で貸し出されるメモ用のホワイトボードだけでなく、画面上でもメモを残す機能がありました。また、後で見直すためのフラグを立てる機能もありました。試験の際はこれらを活用しました。

1周目はざっと解き、2周目では落ち着いて見直しを行いました。それでも残り時間は20分程度余りました。

分からない問題については PostgreSQL の設計を思い浮かべながら、どの選択肢が最も適切かを判断して選びました。

スコアは94点(合格ライン:64点)で合格となりました。

挑戦の振り返り

勉強中はオプションやメタコマンドを覚えることに意味があるのか…?と疑問を持つこともありました。ですが、業務で psql を使う時にメタコマンドがスラスラと出てきたのは学習の成果であったと感じています。

また、業務では触る機会が少なくて知識の薄かった部分を学習によって補填できたように感じます。

OSS-DB Silver は教材が入手しやすいこともあり、業務で PostgreSQL を使う方には挑戦をお勧めしたい試験といえました。

Excelのシリアル値の計算と活用法

はじめに

Excelは日時をシリアル値で管理しています。本記事ではシリアル値の計算と活用法を解説します。

日付をシリアル値で管理する

Excelは日付をシリアル値という形式で管理しています。

Excel では、日付を計算に使用できるように一連のシリアル番号として保存します。 1900 年 1 月 1 日がシリアル値 1 として保存され、2008 年 1 月 1 日は 1900 年 1 月 1 日から 39,447 日後に当たるので、シリアル値は 39448 になります。 適切な日付を表示するためには、数値形式を変更 (セルを書式設定) する必要があります。

DATE 関数

つまり、Excelの表示が "1900/1/1" になっていたとしても、内部では "1" というシリアル値で管理されています。"1" が入力されたセルの書式設定で表示形式を「日付」にすると "1900/1/1" になるのはこのためです。

シリアル値1の表示形式による違い

1日間は シリアル値で "1" に相当します。つまり、 "1900/1/1"(シリアル値は "1")にシリアル値を "1" 足すということは、翌日である "1900/1/2"(シリアル値は "2")となります。

シリアル値1は1日間相当

時刻もシリアル値で管理する

時刻もシリアル値で表現できます。1日間 = 24時間 = シリアル値は "1" です。時刻は 00:00:00 からの経過時間と考えることができます。そうすると以下のとおり計算できます。
時刻 = 00:00:00からの経過[時間] / 24[時間] = シリアル値
24:00:00 = 24[時間] / 24[時間] = 1
12:00:00 = 12[時間] / 24[時間] = 0.5(0.5日間、つまり半日といえる)
01:00:00 = 1[時間] / 24[時間] = 0.041666...
00:01:00 = 1[分間] / (24 * 60)[分間] = 0.000694...
00:00:01 = 1[秒間] / (24 * 60 * 60)[秒間] = 0.000011...
※時間を計算する際は単位合わせ(時間・分間・秒間)を忘れないでください。

シリアル値を用いた日付や日時の計算例

日付の引き算

日付を引き算することで経過日数が得られる理屈はシリアル値で考えるとわかります。
例えば、"1900/1/31" のセルと "1900/1/1" のセルを引き算すると "30" になります。これは "1900/1/31" がシリアル値では "31" 、"1900/1/1" がシリアル値では "1" であり、その引き算の結果が "30" だからです。

日付の引き算

日付と日数の足し算

"1900/1/1 00:00:00" にシリアル値の "1.5" を足すということは "1.5日間" が経過したということになり、"1900/1/2 12:00:00" になります。

日付と日数の足し算

日時と時間の計算

開始日時と処理時間から終了日時を算出することもできます。以下の例で考えます。

開始日時 処理時間[秒]
2022/06/01 10:00:00 5400

まず、"処理時間[秒]"をシリアル値に変換します。
5400[秒間] / (24 * 60 * 60)[秒間] = 0.0625

シリアル値の計算

そして、開始日時と処理時間[シリアル値]を足し算することで、終了日時を算出できます。5400[秒間] = 90[分間]なので、"2022/06/01 11:30:00" となります。

日時と時間の計算

まとめ

Excelは日時をシリアル値で管理しており、計算(処理)もシリアル値で行われています。Excelで日時の処理に悩んだときはシリアル値で考えてみましょう。