PostgreSQLが遅い実行プランを選択してしまう

PostgreSQLを遅くしている犯人はどこだ？（2/3）

一方正しい統計情報になっている場合、「良い例」のように、左側と右側のrowsの値が近づきます。テーブルに含まれるレコード数が100万件を超えるようなケースや、分布が一定の傾向を示さずにばらばらの状態である場合には、ANALYZEによる統計情報用のサンプル数を増やすために「ALTER TABLE SET STATISTICS」文や設定パラメータ「default_statistics_target」で、設定する値を100より大きくするとよい場合もあります。

ふむふむ。
具体的な「ALTER TABLE SET STATISTICS」の使い方についてはPOSTGRESQL 検索スピードが遅い - BIGLOBEなんでも相談室を参考に。

MySQLで GROUP BYにない列を SELECTした場合、グループ内のどの行の値になるかは分からない

MySQL :: MySQL 5.5 Reference Manual :: 11.16.3 GROUP BY and HAVING with Hidden Columns

When using this feature, all rows in each group should have the same values for the columns that are ommitted from the GROUP BY part. The server is free to return any value from the group, so the results are indeterminate unless all values are the same.

MySQLではSQL標準に反して、GROUP BY句にない列をSELECT句やHAVING句で使うことができる。想定しているのは上記リンク先のように、複数のテーブルを結合した場合等にGROUP BY句にあるキー列により一意に決まる（どれも同じになる）従属項目を使うこと。
これによりMySQLは処理を高速化できる。

しかし、違うデータが入っているかもしれない列を使うと、どの行のデータが使われるかは決められていない。（たぶん「物理的に」一番最初のデータとかが使われそう。）


PostgreSQLも9.1から同様のことができるようになるらしい（MySQLに合わせるため）。

PostgreSQL 9.1 の新機能 — Let's Postgres

標準SQLでは必ず GROUP BY にも列名を書く必要があるのですが、MySQL では許されているため、「なぜ PostgreSQL
では受け付けないのか？」という質問が良くありましたが、互換性が向上したと思われます。ただ、プライマリキーが必須であること、つまり省略した列がラン
ダムに選択されることはないよう制限しているのは、PostgreSQL の設計思想が反映されていると言えるでしょう。

プライマリーキーが必須っていうのは、JOINした場合の親テーブルの従属項目を使うことのみを想定しているということかな？

MySQLや PostgreSQLでバージョン番号を調べる方法

SELECT文で取得できる。

SELECT VERSION()

postgresで pg_stat_statementsが見つからない場合

スロークエリの分析 — Let's Postgresを参考にpostgres9.0でスロークエリを分析しようとしたら、エラーになった。

ERROR:  relation "pg_stat_statements" does not exist

デフォルトでは使えない見たい

pg_stat_statements

このモジュールは追加の共有メモリを必要とするため、postgresql.confのshared_preload_librariesにpg_stat_statementsを追加してモジュールをロードしなければなりません。 このことは、このモジュールを追加もしくは削除するには、サーバを再起動する必要があるということを意味しています。

よく見たらスロークエリの分析 — Let's Postgresにもそのことが書いてあった．．．
.

PostgreSQLではユニークキーを作成すると INDEXも作成される

PostgreSQLで自動的にインデックスが生成される条件 - sternhellerの日記

インデックスが生成される場合

* PRIMARY KEYを指定する
* UNIQUE NOT NULLを指定する
* UNIQUEを指定する

いつもどっちだったか忘れる。

PostgreSQLで グループごとに連番を付けて SELECTする方法

Window関数を使うと便利。

Window関数 — Let's Postgres

PARTITION BY と ORDER BY は同時に使用することもできます。以下の例では、地域ごとに身長の低い順に番号付けしています。

SELECT id, 地域, 身長,
rank() OVER (PARTITION BY 地域 ORDER BY 身長)
FROM tbl;

これはいい!

PostgreSQLで 数字をフォーマットする方法

SQL 文字関数 - 連結、空白削除、置換、切出、検索、長さ、数値変換他 - SAK Streets

■数値から文字列へ変換 (数値フォーマット、書式変換) ・to_char() 関数は、数値を文字列型に変換します。

これは使い方によっては便利。

数字形式の文字列の日時をフォーマットする場合はこんな感じで。

select to_char(cast('201001020304' as decimal), '9999/99/99 99:99')

PostgreSQLの場合、 PDOStatementの rowCount()で SELECTした件数を取得できた

直近の SQL ステートメントによって作用した行数を返す - PHP 5.3 日本語マニュアル

ほとんどのデータベースでは、PDOStatement::rowCount() は SELECT 文によって作用した行数を返しません。

ってPHPのマニュアルには書いてあるけど、手元のPostgreSQLではSELECT件数をrowCount()で取得できたよ。
.

postgresで数値型で LIKE演算子を使ったらエラーになる場合の対策

PostgreSQL - bugs - BUG #4055: Using Like in PostgreSQL 8.2.7 and 8.3.1

ERROR: operator does not exist: bigint ~~ unknown
LINE 1: select * from movimientos where idmovimientos like '178'
^
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.

PostgreSQL 8.2で動いてたSQLが、PostgreSQL 9.0にしたら↑と同じように、bigintでLIKEを使う箇所でエラーになった。

ERROR:  operator does not exist: bigint ~~ unknown
（略）
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

PostgreSQL8.3からは型の使い方が厳密になり、暗黙の型変換をしてくれなくなったために発生したエラーらしい。

キャストを追加してバージョン互換性を保つには — Let's Postgresに対策が書いてあるが、ユーザー定義のCASTではレスポンスが少し心配？(標準のCASTとそれほど変わらない？)

影響箇所が少ない(そもそも数値型にLIKE文なんてレアだ)から、個別対処にしようかな。

PostgreSQLは次期バージョンの 9.1で MERGEをサポートするらしい

PostgreSQLのこれまで、9.0、そしてこれから

9.0 9.1 PostgreSQL 9.0 α ( : ) ?? PostgreSQL 9.1 ( , ) MERGE : UPDATE, INSERT REPLACE, UPSERT SQL “MERGE” Predicate Lock SE-PgSQL : , SQL/MED : , DB , JSON PGCon 2010 http://wiki.postgresql.org/wiki/PgCon_2010_Developer_Meeting JPUG 2010 25

これは朗報

Windowsユーザー管理画面に postgresが表示されない問題の対策

PostgreSQL上にMediaWiki環境を構築 (3) — Let's Postgres

インストーラに任せて postgres ユーザを作成した場合、「コントロール パネル > ユーザー アカウント > アカウントの管理」で表示される一覧に postgres ユーザのアイコンが表示されないと思います。これは異常な動作ではなく、この一覧には Administrators や Users グループに所属するユーザのみが表示されるためです。インストーラが作成する postgres ユーザは、そのままでは何のグループにも所属していません。管理などを行うため一覧に表示させたい場合には、コマンド プロンプトから net localgroup コマンドを使用します。

Windows 2003の頃は普通に表示された気がするけど、Windows 2008あたりで変わったのかな？

postgresは Covering Indexによる高速検索ができないらしい

index-only (別名 index covering) - taediumの日記

tgkさんのとこで、PostgreSQLは「インデックスだけ見てデータは見ない」ということができないらしいという話があり、これが気になっていました。

ちょっとショック。


ところで、

ところで、「インデックスだけ見てデータは見ない」機能なんですが、一言でバシッと言えないとわかりにくいですよね。下の本ではindex-onlyと呼んでいます。教科書として使われている本らしいのでこれに倣えばいいよね。これからはindex-onlyと呼んでいこうと思います。（MySQLやSQL Serverではindex coveringとかcovering indexとか呼んでいますけどね。）

名無しで扱われてることも多い。
Covering Index! Covering Index! Covering Index!

postgresはテーブルを継承できるらしい

PostgreSQLでテーブルの継承 - 都元ダイスケ IT-PRESS

PostgreSQLには、テーブルの継承なんていう、とんがった機能があります。前衛的～。

created_atとかupdated_atとか登録/更新者IDとか登録/更新IPアドレスとか、ビジネスドメイン共通の決め事的な列がある場合には便利かもね。

postgresでの numeric (decimal)の少数部を含めた桁数の指定方法

数値データ型

この後の説明では、次の用語を使用します。 numericの位取りとは、小数点の右側の小数点以下の桁数をいいます。 numericの精度とは数字全体の有効桁数です。 すなわち、小数点をはさんでいる両側の桁数の合計です。 そのため、23.5141という数値の精度は6で位取りは4となります。 整数の位取りは、ゼロであるとみなすことができます。

numeric列の数値の最大精度と最大位取りはともに設定することができます。 列のデータ型をnumericと宣言するには次の構文を使います。

NUMERIC(precision, scale)

ふむふむ
.

postgresの charcterはバイト数でなく文字数

検索したけど見つからないので実験した。
charcter(n)も charcter varying(n)も、マルチバイト文字であるかどうかに関わらず、n文字までデータを入れられる。

postgresでテーブルのデータを dump

pg_dump

pg_dump -- PostgreSQLデータベースをスクリプトファイルまたは他のアーカイブファイルへ抽出する

通常はこんな感じ？（-aオプションはデータのみのdump）

pg_dump -a -t table_name -U user_name db_name > file_name.dump

PHPの pg_connect()で sslmodeとして指定できる値

PHPのマニュアルには明記されていないが、コメント欄にこう書いてあった。

PHP: pg_connect - Manual

The values accepted by pg_connect's sslmode argument are: disable, allow, prefer, require

色々なサイトを調べたら、確かにこの4つは指定しているサンプルがあった。
.