PostgreSQLにおける実行計画の処理範囲についての理解
10646 ワード
スタート
私は1つのテーブルを持っています.約1千万件の記録があります.insertコマンドを実行して別のテーブルにデータを挿入すると、制約とインデックスが存在するため、データの挿入が遅くなります.
しかし、explain planがデータをどのように処理しているかを観察する機会も与えられました.
私のtest 02表には、1千万件のデータがあります.次はデータをコピーします.
データ量が多く、制約も働いているため、長い間実行されています(実際には2日後にクラッシュしました(私は仮想マシンを使っています).
その間、クエリーを実行するために別のセッションを開きました.
実はselect count(*)from ptestを実行すると、前のsessionの挿入動作に時間がかかり、まだ終了していないのでコミットされていません.したがって、返される結果はゼロです.
では、なぜ実行計画が2つのサブテーブルにそれぞれ100万件以上の記録を見たのだろうか.
PostgreSQLの物理ストレージ構造では、oracleのようなrollbackやundo segmentはなく、すべてのデータイメージがデータブロックに格納されています.vacuum操作を行わないと、古いコミットされたデータ、ディスクにブラシされたばかりのコミットされていないデータ、コミットされたデータがデータブロックに表示されます.
もし私がプログラムを作って、1分ごとにテーブルデータの特定のレコードを変更しますが、コミットしません.私のトランザクションが十分に長い限り、これはデータをブロックにブラシし続けます.これはPostgreSQLの弱点であり、非難されているところでもある.
同時性の高いシステムでは、毎回のsql実行において、そのコストの重要な面は、ディスクが物理I/Oにアクセスすることであり、PostgreSQLがデータの前のイメージ(コミットされたものとコミットされていないもの)、現在のイメージ(コミットされたものとコミットされていないもの)を一緒に保存するため、各sqlアクセスが互いに影響し合う(コミットされていないデータが混在しているため)、パフォーマンスが低下したことは間違いありません.
PostgreSQLコミュニティで尋ねた結果、近似の結果を見たいだけなら、あまり代価を払わないでpg_class入手:
の最後の部分
私は1つのテーブルを持っています.約1千万件の記録があります.insertコマンドを実行して別のテーブルにデータを挿入すると、制約とインデックスが存在するため、データの挿入が遅くなります.
しかし、explain planがデータをどのように処理しているかを観察する機会も与えられました.
postgres=# create table ptest(id integer, name varchar(20));
CREATE TABLE
postgres=# create table ctest01(CHECK(id<5000000)) inherits (ptest);
CREATE TABLE
postgres=# create table ctest02(CHECK(id>=5000000)) inherits (ptest);
CREATE TABLE
postgres=#
postgres=# create index on ctest01(id);
CREATE INDEX
postgres=# create index on ctest02(id);
CREATE INDEX
postgres=#
postgres=#
postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS TRIGGER AS $$
postgres$#
postgres$# BEGIN
postgres$#
postgres$# IF ( NEW.id <5000000 ) THEN
postgres$# INSERT INTO ctest01 VALUES (NEW.*);
postgres$# ELSIF ( NEW.id >= 5000000 ) THEN
postgres$# INSERT INTO ctest02 VALUES (NEW.*);
postgres$# ELSE
postgres$# RAISE EXCEPTION 'Error while inserting data';
postgres$# END IF;
postgres$#
postgres$# RETURN NULL;
postgres$# END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=#
postgres=# CREATE TRIGGER insert_ptest_trigger BEFORE INSERT ON ptest FOR EACH ROW
postgres-# EXECUTE PROCEDURE ptest_insert_trigger();
CREATE TRIGGER
postgres=#
私のtest 02表には、1千万件のデータがあります.次はデータをコピーします.
insert into ptest select * from test02;
データ量が多く、制約も働いているため、長い間実行されています(実際には2日後にクラッシュしました(私は仮想マシンを使っています).
その間、クエリーを実行するために別のセッションを開きました.
postgres=# explain select count(*) from ptest;
QUERY PLAN
-----------------------------------------------------------------------------------
Aggregate (cost=55406.40..55406.41 rows=1 width=0)
-> Append (cost=0.00..49601.92 rows=2321793 width=0)
-> Seq Scan on ptest (cost=0.00..0.00 rows=1 width=0)
-> Seq Scan on ctest01 ptest (cost=0.00..24776.52 rows=1159752 width=0)
-> Seq Scan on ctest02 ptest (cost=0.00..24825.40 rows=1162040 width=0)
(5 rows)postgres=#
実はselect count(*)from ptestを実行すると、前のsessionの挿入動作に時間がかかり、まだ終了していないのでコミットされていません.したがって、返される結果はゼロです.
では、なぜ実行計画が2つのサブテーブルにそれぞれ100万件以上の記録を見たのだろうか.
PostgreSQLの物理ストレージ構造では、oracleのようなrollbackやundo segmentはなく、すべてのデータイメージがデータブロックに格納されています.vacuum操作を行わないと、古いコミットされたデータ、ディスクにブラシされたばかりのコミットされていないデータ、コミットされたデータがデータブロックに表示されます.
もし私がプログラムを作って、1分ごとにテーブルデータの特定のレコードを変更しますが、コミットしません.私のトランザクションが十分に長い限り、これはデータをブロックにブラシし続けます.これはPostgreSQLの弱点であり、非難されているところでもある.
同時性の高いシステムでは、毎回のsql実行において、そのコストの重要な面は、ディスクが物理I/Oにアクセスすることであり、PostgreSQLがデータの前のイメージ(コミットされたものとコミットされていないもの)、現在のイメージ(コミットされたものとコミットされていないもの)を一緒に保存するため、各sqlアクセスが互いに影響し合う(コミットされていないデータが混在しているため)、パフォーマンスが低下したことは間違いありません.
PostgreSQLコミュニティで尋ねた結果、近似の結果を見たいだけなら、あまり代価を払わないでpg_class入手:
SELECT sum(reltuples) FROM pg_class WHERE relname IN ('ptest', 'ctest01', 'ctest02');
の最後の部分