まず、データベーススキーマを作成し、次に2つの方法（「通常」およびpg_pathman）でパーティション化し、次にデータを入力して、パーティションテーブルでのクエリの動作を確認します。
また、この素晴らしい拡張機能を、パーティションで既に「通常の」方法で破られているデータスキームに導入する方法についても説明します。
1.データスキーマを作成する
私たちの小さなスキームは、3つのテーブルで構成されます。
•3つの部分に分割された親テーブル、
•同じ方法でそれを参照する子テーブル、
•パーティション番号を持つ親レコードへのリンクを含むいくつかの結果の通常の表。
親テーブルと子テーブルは、partition_idによってパーティション化されます。 特定のデータparent_dataとchild_dataを持つ列がそれぞれこれらのテーブルに追加されます。 また、インデックス付きフィールドchild_indexがサブクエリで使用するために子テーブルに追加されました。
create table public.parent ( parent_id numeric not null, parent_data varchar(100), partition_id numeric not null, primary key (parent_id) ); create table public.child ( child_id numeric not null, parent_id numeric not null, child_data varchar(100), child_index numeric, partition_id numeric not null, primary key (child_id) ); create index ix_child_parent on public.child(parent_id); create index ix_child_index on public.child(child_index); create table public.result ( result_id numeric not null, parent_id numeric not null, partition_id numeric not null ); create index idx_result on public.result(result_id);
2.「通常の」方法でスキームを分割する
ですから、見出しに目が留められていて、まだこの記事を見ることにしたのであれば、おそらくpostgresには組み込みのパーティショニングがないことをご存じでしょう。 代わりに、既存の継承メカニズムを使用することが提案されています。
これは次のようなものです。
create table public.parent_1 ( like public.parent including indexes, check(partition_id = 1) ) inherits (public.parent); create table public.parent_2 ( like public.parent including indexes, check(partition_id = 2) ) inherits (public.parent); create table public.parent_3 ( like public.parent including indexes, check(partition_id = 3) ) inherits (public.parent); create table public.child_1 ( like public.child including indexes, check(partition_id = 1)) inherits (public.child); create table public.child_2 ( like public.child including indexes, check(partition_id = 2)) inherits (public.child); create table public.child_3 ( like public.child including indexes, check(partition_id = 3)) inherits (public.child); create or replace function public.trigger_function() returns trigger as $trg_func$ begin execute 'insert into ' || tg_table_name || '_' || new.partition_id ||' values ($1.*)' using new; return null; end;$trg_func$ language plpgsql security definer; create trigger parent_trigger before insert on public.parentfor each row execute procedure public.trigger_function(); create trigger child_trigger before insert on public.childfor each row execute procedure public.trigger_function();
2つのテーブルをそれぞれ3つの部分に分割します。より多くのパーティションは作成しませんでした。さもないと、クエリプランによって記事が無限のシートになります。 主キーとインデックスが継承されたテーブルにコピーされるように、テーブルはインデックスモードを含むように継承されます。 ローカルPKはグローバルな一意性を与えませんが、パーティションとローカルインデックスにはローカルな一意性があります。 また、継承されたテーブルでは、パーティション識別子に制限が作成されるため、それらを切断する標準的なメカニズムが機能します。
挿入中にパーティションによってレコードを分散させるために、トリガーのメカニズムとユニバーサルトリガー関数public.trigger_functionが使用されます。 このメカニズムの欠点は、メインテーブルに挿入されたときに、挿入されたレコードの数が0で返されることです。
3. pg_pathmanを使用して回線を分割します
postgresでこの拡張機能を使用してテーブルをパーティション分割するには、create_range_partitions特殊関数を呼び出します。
select public.create_range_partitions(c.oid, 'partition_id', 1, 1, 3) from pg_class c inner join pg_namespace n on c.relnamespace = n.oid and n.nspname = 'public' where c.relname in ('parent', 'child');
このパーティション分割により、ソーステーブルのすべてのインデックスがパーティションテーブルにコピーされ、この拡張機能が機能するための特別な制限が作成されます。
4.テスト手法
結果を表示する前に、スタンドとテスト方法について説明します。
テストしたスタンドは、2ギガバイトのRAMを備えたVMware仮想マシンです。 Red Hat Server 6.7、Postresql 9.6.3、およびpg_pathman 1.4.2が含まれています。 postgres構成では、shared_buffers = 512M、effective_cache_size = 1GB、constraint_exclusion =パーティションです。
挿入ブロックと各リクエストは次のようにテストされました。
1.リクエストをテストするとき、リクエスト自体が最初に実行され、そのデータがホストWindowsシステムのディスクキャッシュに送り込まれ、挿入は行われません。 このキャッシュを仮想ファイルにドロップして残りのアプリケーションに残すことはできませんでしたが、このキャッシュを完全にドロップすると、大きなブレーキがかかり、何も正しく測定できませんでした。 したがって、リクエストの実行時間にはあまり焦点を当てず、リクエストによって表示されるデータブロックの数を調べます。 通常の大規模システムでのこの量は、ディスク読み取りの数に比例するため、そこでのクエリ実行時間に比例します。
2. echo 3> / proc / sys / vm / drop_cachesコマンドにより、Linuxキャッシュがドロップされます。
3.キャッシュを破棄するためにpostgresを再起動します。
4.前のExplainステートメント（分析、冗長、バッファー）でクエリまたは挿入が実行され、実行の計画、時間、およびその他の特性が決定されます。
5.データを挿入する
次のスクリプトを使用してデータを貼り付けます。
insert into public.parent (parent_id, parent_data, partition_id) select a, gen_random_bytes(25), trunc(random() * 3) + 1 from generate_series(1, 10000000) a; insert into public.child (child_id, parent_id, child_data, child_index, partition_id) select parent_id * 20 + a, parent_id, gen_random_bytes(25), trunc(random() * 100000) + 1, partition_id from public.parent, generate_series(1, 20) a where random() < 0.1; insert into public.result (result_id, parent_id, partition_id) select a, parent_id, partition_id from generate_series(1,100) a, public.parent where random() < 0.001;
親テーブルに1,000万件のレコードを挿入します。子では約2倍になります。
child_index値ごとに、子ごとに約200のエントリがあります。 result_idの1つの値は、100万の親レコードです。
レコードを挿入した後、すべてのテーブルを分析します。
analyze public.parent; analyze public.parent_1; analyze public.parent_2; analyze public.parent_3; analyze public.child; analyze public.child_1; analyze public.child_2; analyze public.child_3; analyze public.result;
したがって、postgresの標準的な方法でパーティション化された2つのテーブルへの挿入は38:39で、pg_pathman-15:10で続きました。 つまり、挿入速度が2.5倍に増加しました。 pg_pathmanの挿入プランでは、挿入されたレコードをリダイレクトする特別なPartitionFilterステップが表示され、その効果が表示されます。 トリガーは不要になりました。 また、挿入されたレコードをリダイレクトするトリガーを除き、通常のテーブルに貼り付けるときのように、挿入されたレコードの数を調べることができます。 たとえば、親テーブルに挿入プランを示します。
"Insert on parent (cost=0.00..45.00 rows=1000 width=282)" " -> Custom Scan (PartitionFilter) (cost=0.00..45.00 rows=1000 width=282)" " -> Subquery Scan on "*SELECT*" (cost=0.00..45.00 rows=1000 width=282)" " -> Function Scan on generate_series a (cost=0.00..22.50 rows=1000 width=44)"
6.すでにパーティション化されたスキームへのpg_pathmanの導入
パーティションのパフォーマンスを正しく比較するには、同じデータが必要です。 これを行うには、標準のパーティションスキームを作成し、上記の方法を使用してリクエストをテストします。 そして、この回路にpg_pathman拡張機能を導入してテストします。
この拡張機能には、空のテーブルのパーティション分割と、データを含むテーブルの並列パーティション分割の両方の機能があります。 ただし、すでにパーティション分割され、データが入力されているテーブルを登録するメカニズムはありません。これは悲しいことです。
メインテーブルをすぐに登録することはできません。 pg_pathmanは、継承メカニズムを使用して、メインテーブルとのパーティションの関係を示します。
また、メインテーブルを登録しようとすると、pg_pathmanはパーティションの制限を見つけられず、エラーが発生します。
ただし、この問題は次の方法で解決できます。
1）最初のステップは、メイン相続人からすべてのパーティションを削除することです。
alter table parent_1 no inherit parent; alter table parent_2 no inherit parent; alter table parent_3 no inherit parent; alter table child_1 no inherit child; alter table child_2 no inherit child; alter table child_3 no inherit child;
2）次に、pg_pathmanにメインテーブルを登録する必要があります。
select public.add_to_pathman_config(c.oid, 'partition_id', '1') from pg_class c inner join pg_namespace n on n.oid = c.relnamespace and n.nspname = 'public' where c.relname in ('parent', 'child');
3）pg_pathmanにパーティションを登録します。
select public.attach_range_partition(parent.oid, child.oid, partition_id.partition_id::numeric, (partition_id.partition_id + 1)::numeric) from pg_namespace n inner join pg_class parent on n.oid = parent.relnamespace and parent.relname in ('parent', 'child') inner join generate_series(1,3) partition_id on 1=1 inner join pg_class child on n.oid = child.relnamespace and child.relname = parent.relname || '_' || partition_id.partition_id where n.nspname = 'public';
4）これで、すべてのトリガーとトリガー機能を削除できます。
drop trigger parent_trigger on public.parent; drop trigger child_trigger on public.child; drop function trigger_function();
5）最後に、挿入とクエリを確認する必要があります。 すべてが機能するはずです。
7.リクエストのテスト
標準のパーティションメカニズムは、パーティションでの制限を介して機能し、リクエストで指定された定数と比較します。
この記事では、pg_pathmanのバージョン1.4.2を使用しています。 拡張ドキュメントでは、標準のAppendの代わりに使用されるRuntimeAppendリクエストプランの新しい特別なステップタイプについて説明します。これは、リクエストの実行中に目的のキーでパーティションを選択的にスキャンすることで異なります。 つまり、この場合のパーティションのクリッピングは、各レコードのパーティションキーによって動的に機能するはずです。
そのため、最初にパーティションキーの1つの定数値でクエリがどのように機能するかを調べ、次に2つの定数値で、最後に通常のテーブルのパーティションキーの値を使用します。 クエリプランには実行統計が含まれます。 最初に、通常のパーティション化メカニズム（postgres）のクエリ結果が表示され、次にpg_pathmanが表示されます。
7.1。 fromでパーティションテーブルを結合するクエリ
パーティションキーごとに1つの定数：
select * from public.parent p, public.child c where c.partition_id = p.partition_id and c.parent_id = p.parent_id and p.partition_id = 3 limit 100;
postgres：
"Limit (cost=20.41..31.31 rows=100 width=181) (actual time=2.517..3.093 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=4 read=9" ... "Planning time: 19.035 ms" "Execution time: 3.607 ms"
完全に
"Limit (cost=20.41..31.31 rows=100 width=181) (actual time=2.517..3.093 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=4 read=9" " -> Merge Join (cost=20.41..726234.12 rows=6662376 width=181) (actual time=2.516..3.079 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Merge Cond: (p.parent_id = c.parent_id)" " Buffers: shared hit=4 read=9" " -> Merge Append (cost=0.56..184529.87 rows=3332147 width=84) (actual time=1.397..1.414 rows=47 loops=1)" " Sort Key: p.parent_id" " Buffers: shared hit=1 read=4" " -> Index Scan using parent_pkey on public.parent p (cost=0.12..8.14 rows=1 width=282) (actual time=0.006..0.006 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared hit=1" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.43..142869.88 rows=3332146 width=84) (actual time=1.389..1.400 rows=47 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = '3'::numeric)" " Buffers: shared read=4" " -> Materialize (cost=0.57..450113.45 rows=6662376 width=97) (actual time=1.094..1.605 rows=100 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=3 read=5" " -> Merge Append (cost=0.57..433457.51 rows=6662376 width=97) (actual time=1.089..1.590 rows=100 loops=1)" " Sort Key: c.parent_id" " Buffers: shared hit=3 read=5" " -> Index Scan using ix_child_parent on public.child c (cost=0.12..8.14 rows=1 width=346) (actual time=0.004..0.004 rows=0 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Filter: (c.partition_id = '3'::numeric)" " Buffers: shared hit=1" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_1 (cost=0.43..350169.66 rows=6662375 width=97) (actual time=1.083..1.554 rows=100 loops=1)" " Output: c_1.child_id, c_1.parent_id, c_1.child_data, c_1.child_index, c_1.partition_id" " Filter: (c_1.partition_id = '3'::numeric)" " Buffers: shared hit=2 read=5" "Planning time: 19.035 ms" "Execution time: 3.607 ms"
pg_pathman：
"Limit (cost=15.99..25.01 rows=100 width=628) (actual time=0.934..1.111 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=9" ... "Planning time: 16.164 ms" "Executiontime: 1.174 ms"
完全に
"Limit (cost=15.99..25.01 rows=100 width=628) (actual time=0.934..1.111 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=9" " -> Merge Join (cost=15.99..601209.25 rows=6662375 width=628) (actual time=0.934..1.101 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Merge Cond: (p.parent_id = c.parent_id)" " Buffers: shared hit=2 read=9" " -> Merge Append (cost=0.44..142832.56 rows=3332146 width=84) (actual time=0.464..0.480 rows=47 loops=1)" " Sort Key: p.parent_id" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p (cost=0.43..142832.55 rows=3332146 width=84) (actual time=0.464..0.477 rows=47 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared read=4" " -> Materialize (cost=0.44..366781.74 rows=6662375 width=97) (actual time=0.464..0.583 rows=100 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=5" " -> Merge Append (cost=0.44..350125.80 rows=6662375 width=97) (actual time=0.459..0.563 rows=100 loops=1)" " Sort Key: c.parent_id" " Buffers: shared hit=2 read=5" " -> Index Scan using child_3_parent_id_idx on public.child_3 c (cost=0.43..350125.79 rows=6662375 width=97) (actual time=0.459..0.550 rows=100 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Filter: (c.partition_id = '3'::numeric)" " Buffers: shared hit=2 read=5" "Planning time: 16.164 ms" "Executiontime: 1.174 ms"
パーティションキーの条件の2つの定数：
select * from public.parent p, public.child c where c.partition_id = p.partition_id and c.parent_id = p.parent_id and p.partition_id in (2, 3) limit 100;
postgres：
"Limit (cost=44.98..78.34 rows=100 width=181) (actual time=8.167..8.543 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=21" ... "Planning time: 17.618 ms" "Execution time: 8.870 ms"
完全に
"Limit (cost=44.98..78.34 rows=100 width=181) (actual time=8.167..8.543 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=21" " -> Merge Join (cost=44.98..2223585.66 rows=6665904 width=181) (actual time=8.165..8.534 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Merge Cond: (p.parent_id = c.parent_id)" " Join Filter: (p.partition_id = c.partition_id)" " Buffers: shared hit=2 read=21" " -> Merge Append (cost=1.01..408142.24 rows=6665413 width=84) (actual time=5.390..5.416 rows=53 loops=1)" " Sort Key: p.parent_id" " Buffers: shared hit=1 read=8" " -> Index Scan using parent_pkey on public.parent p (cost=0.12..8.14 rows=1 width=282) (actual time=0.004..0.004 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared hit=1" " -> Index Scan using parent_2_pkey on public.parent_2 p_1 (cost=0.43..142956.36 rows=3333266 width=84) (actual time=3.594..3.598 rows=28 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p_2 (cost=0.43..142869.88 rows=3332146 width=84) (actual time=1.786..1.798 rows=26 loops=1)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Filter: (p_2.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Materialize (cost=1.46..1498856.74 rows=19997711 width=97) (actual time=2.746..3.039 rows=173 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=1 read=13" " -> Merge Append (cost=1.46..1448862.46 rows=19997711 width=97) (actual time=2.736..2.987 rows=173 loops=1)" " Sort Key: c.parent_id" " Buffers: shared hit=1 read=13" " -> Index Scan using ix_child_parent on public.child c (cost=0.12..8.14 rows=1 width=346) (actual time=0.004..0.004 rows=0 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=1" " -> Index Scan using child_1_parent_id_idx on public.child_1 c_1 (cost=0.43..331779.20 rows=6666670 width=97) (actual time=0.559..0.744 rows=74 loops=1)" " Output: c_1.child_id, c_1.parent_id, c_1.child_data, c_1.child_index, c_1.partition_id" " Buffers: shared read=5" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_2 (cost=0.43..333612.86 rows=6668665 width=97) (actual time=1.022..1.029 rows=51 loops=1)" " Output: c_2.child_id, c_2.parent_id, c_2.child_data, c_2.child_index, c_2.partition_id" " Buffers: shared read=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_3 (cost=0.43..333513.72 rows=6662375 width=97) (actual time=1.146..1.152 rows=50 loops=1)" " Output: c_3.child_id, c_3.parent_id, c_3.child_data, c_3.child_index, c_3.partition_id" " Buffers: shared read=4" "Planning time: 17.618 ms" "Execution time: 8.870 ms"
pg_pathman：
"Limit (cost=32.45..57.22 rows=100 width=628) (actual time=2.457..2.714 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared read=21" ... "Planning time: 18.944 ms" "Executiontime: 2.798 ms"
完全に
"Limit (cost=32.45..57.22 rows=100 width=628) (actual time=2.457..2.714 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared read=21" " -> Merge Join (cost=32.45..1651206.45 rows=6665904 width=628) (actual time=2.455..2.705 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Merge Cond: (p.parent_id = c.parent_id)" " Join Filter: (p.partition_id = c.partition_id)" " Buffers: shared read=21" " -> Merge Append (cost=0.87..285722.16 rows=6665412 width=84) (actual time=1.011..1.037 rows=53 loops=1)" " Sort Key: p.parent_id" " Buffers: shared read=8" " -> Index Scan using parent_2_pkey on public.parent_2 p (cost=0.43..142889.60 rows=3333266 width=84) (actual time=0.522..0.528 rows=28 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.43..142832.55 rows=3332146 width=84) (actual time=0.486..0.493 rows=26 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Materialize (cost=1.32..1048885.37 rows=19997710 width=97) (actual time=1.437..1.595 rows=173 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared read=13" " -> Merge Append (cost=1.32..998891.10 rows=19997710 width=97) (actual time=1.432..1.561 rows=173 loops=1)" " Sort Key: c.parent_id" " Buffers: shared read=13" " -> Index Scan using child_1_parent_id_idx on public.child_1 c (cost=0.43..331772.15 rows=6666670 width=97) (actual time=0.433..0.510 rows=74 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared read=5" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_1 (cost=0.43..333608.02 rows=6668665 width=97) (actual time=0.561..0.563 rows=51 loops=1)" " Output: c_1.child_id, c_1.parent_id, c_1.child_data, c_1.child_index, c_1.partition_id" " Buffers: shared read=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_2 (cost=0.43..333510.91 rows=6662375 width=97) (actual time=0.437..0.442 rows=50 loops=1)" " Output: c_2.child_id, c_2.parent_id, c_2.child_data, c_2.child_index, c_2.partition_id" " Buffers: shared read=4" "Planning time: 18.944 ms" "Executiontime: 2.798 ms"
テーブルのパーティションキー：
select * from public.result r, public.parent p where r.partition_id = p.partition_id and r.parent_id = p.parent_id and r.result_id = 50 limit 100;
postgres：
"Limit (cost=199.17..6528.51 rows=100 width=100) (actual time=14.815..65.450 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=651 read=431" ... "Planning time: 13.773 ms" "Execution time: 65.750 ms"
完全に
"Limit (cost=199.17..6528.51 rows=100 width=100) (actual time=14.815..65.450 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=651 read=431" " -> Nested Loop (cost=199.17..192800.93 rows=3043 width=100) (actual time=14.813..65.407 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=651 read=431" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=16) (actual time=11.301..17.396 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=49" " Buffers: shared read=87" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=10.438..10.438 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Append (cost=0.00..20.31 rows=4 width=84) (actual time=0.255..0.477 rows=1 loops=100)" " Buffers: shared hit=651 read=344" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.000..0.000 rows=0 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: ((r.partition_id = p.partition_id) AND (r.parent_id = p.parent_id))" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..6.77 rows=1 width=84) (actual time=0.252..0.253 rows=1 loops=100)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = r.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=222 read=178" " -> Index Scan using parent_2_pkey on public.parent_2 p_2 (cost=0.43..6.77 rows=1 width=84) (actual time=0.112..0.112 rows=0 loops=99)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Index Cond: (p_2.parent_id = r.parent_id)" " Filter: (r.partition_id = p_2.partition_id)" " Buffers: shared hit=214 read=84" " -> Index Scan using parent_3_pkey on public.parent_3 p_3 (cost=0.43..6.77 rows=1 width=84) (actual time=0.108..0.108 rows=0 loops=99)" " Output: p_3.parent_id, p_3.parent_data, p_3.partition_id" " Index Cond: (p_3.parent_id = r.parent_id)" " Filter: (r.partition_id = p_3.partition_id)" " Buffers: shared hit=215 read=82" "Planning time: 13.773 ms" "Execution time: 65.750 ms"
pg_pathman：
"Limit (cost=199.60..2457.44 rows=100 width=298) (actual time=7.354..30.780 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=222 read=265" ... "Planning time: 11.799 ms" "Executiontime: 30.920 ms"
完全に
"Limit (cost=199.60..2457.44 rows=100 width=298) (actual time=7.354..30.780 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=222 read=265" " -> Nested Loop (cost=199.60..68905.63 rows=3043 width=298) (actual time=7.352..30.742 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=222 read=265" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=16) (actual time=6.838..10.367 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=49" " Buffers: shared read=87" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=5.997..5.997 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Custom Scan (RuntimeAppend) (cost=0.43..6.77 rows=1 width=84) (actual time=0.194..0.196 rows=1 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Prune by: (r.partition_id = p.partition_id)" " Buffers: shared hit=222 read=178" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..6.77 rows=1 width=84) (actual time=0.193..0.194 rows=1 loops=100)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = r.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=222 read=178" "Planning time: 11.799 ms" "Executiontime: 30.920 ms"
標準のパーティション分割方法では、1つの定数が関連付けられたパーティションテーブルに拡張され、不適切な部分を切り捨ててパーティションをクリッピングします。 ただし、パーティション定数がないため、空のメインテーブルは表示されたままです。 2つの定数を使用したクエリでは、プルーニングは、条件が設定されているパーティションキーの親テーブルでのみ機能しました。 接続された子テーブルは完全に見通されました。 つまり、結合テーブルのパーティションのクリッピングは、パーティションキーの単一の定数値を指定する場合にのみ機能します。 リンクテーブルのパーティションキーを使用したクエリでは、クリッピングはまったく機能しませんでしたが、ドキュメントとは一致しますが、これは非常に悪いことです。
pg_pathman拡張機能は空のメインテーブルを考慮から削除しましたが、最初のクエリと2番目のクエリでプラン構造が変更されなかったため、ブロック読み取りの数はほぼ同じであり、驚くことではありません。
pg_pathman : Merge Join (width=182 = 84 + 98), . – parent child (width=628 = 282 + 346). , pg_pathman .
child , pruning RuntimeAppend - .
RuntimeAppend, , . , Buffers: shared hit=651 read=431 Buffers: shared hit=222 read=265.
inner left jon , .
7.2. .
:
select *, (select min(c.child_data) from public.child c where c.partition_id = p.partition_id and c.parent_id = p.parent_id) from public.parent p where p.partition_id = 3 limit 100;
postgres:
"Limit (cost=0.00..2588.75 rows=100 width=116) (actual time=1.939..4.835 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=977 read=18" ... "Planning time: 14.819 ms" "Execution time: 4.935 ms"
"Limit (cost=0.00..2588.75 rows=100 width=116) (actual time=1.939..4.835 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=977 read=18" " -> Result (cost=0.00..86260794.66 rows=3332147 width=116) (actual time=1.936..4.817 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, (SubPlan 1)" " Buffers: shared hit=977 read=18" " -> Append (cost=0.00..89615.83 rows=3332147 width=84) (actual time=0.026..0.130 rows=100 loops=1)" " Buffers: shared read=2" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.002..0.002 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " -> Seq Scan on public.parent_3 p_1 (cost=0.00..89615.83 rows=3332146 width=84) (actual time=0.024..0.117 rows=100 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = '3'::numeric)" " Buffers: shared read=2" " SubPlan 1" " -> Aggregate (cost=25.84..25.85 rows=1 width=32) (actual time=0.046..0.046 rows=1 loops=100)" " Output: min((c.child_data)::text)" " Buffers: shared hit=977 read=16" " -> Append (cost=0.00..25.78 rows=25 width=79) (actual time=0.042..0.044 rows=2 loops=100)" " Buffers: shared hit=977 read=16" " -> Seq Scan on public.child c (cost=0.00..0.00 rows=1 width=218) (actual time=0.000..0.000 rows=0 loops=100)" " Output: c.child_data" " Filter: ((c.partition_id = p.partition_id) AND (c.parent_id = p.parent_id))" " -> Index Scan using child_1_parent_id_idx on public.child_1 c_1 (cost=0.43..8.59 rows=8 width=73) (actual time=0.013..0.013 rows=0 loops=100)" " Output: c_1.child_data" " Index Cond: (c_1.parent_id = p.parent_id)" " Filter: (c_1.partition_id = p.partition_id)" " Buffers: shared hit=296 read=4" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_2 (cost=0.43..8.59 rows=8 width=73) (actual time=0.012..0.012 rows=0 loops=100)" " Output: c_2.child_data" " Index Cond: (c_2.parent_id = p.parent_id)" " Filter: (c_2.partition_id = p.partition_id)" " Buffers: shared hit=296 read=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_3 (cost=0.43..8.59 rows=8 width=73) (actual time=0.015..0.016 rows=2 loops=100)" " Output: c_3.child_data" " Index Cond: (c_3.parent_id = p.parent_id)" " Filter: (c_3.partition_id = p.partition_id)" " Buffers: shared hit=385 read=8" "Planning time: 14.819 ms" "Execution time: 4.935 ms"
pg_pathman:
"Limit (cost=0.00..865.96 rows=100 width=314) (actual time=1.569..3.879 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=385 read=10" ... "Planning time: 22.729 ms" "Executiontime: 4.006 ms"
"Limit (cost=0.00..865.96 rows=100 width=314) (actual time=1.569..3.879 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=385 read=10" " -> Result (cost=0.00..28854985.31 rows=3332146 width=314) (actual time=1.564..3.858 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, (SubPlan 1)" " Buffers: shared hit=385 read=10" " -> Append (cost=0.00..89615.83 rows=3332146 width=84) (actual time=0.403..0.531 rows=100 loops=1)" " Buffers: shared read=2" " -> Seq Scan on public.parent_3 p (cost=0.00..89615.83 rows=3332146 width=84) (actual time=0.401..0.518 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared read=2" " SubPlan 1" " -> Aggregate (cost=8.61..8.62 rows=1 width=32) (actual time=0.031..0.032 rows=1 loops=100)" " Output: min((c.child_data)::text)" " Buffers: shared hit=385 read=8" " -> Custom Scan (RuntimeAppend) (cost=0.43..8.59 rows=8 width=218) (actual time=0.024..0.025 rows=2 loops=100)" " Output: c.child_data" " Prune by: (c.partition_id = p.partition_id)" " Buffers: shared hit=385 read=8" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_1 (cost=0.43..8.59 rows=8 width=73) (actual time=0.023..0.024 rows=2 loops=100)" " Output: c_1.child_data, c_1.partition_id" " Index Cond: (c_1.parent_id = p.parent_id)" " Filter: (c_1.partition_id = p.partition_id)" " Buffers: shared hit=385 read=8" "Planning time: 22.729 ms" "Executiontime: 4.006 ms"
:
select *, (select min(c.child_data) from public.child c where c.partition_id = p.partition_id and c.parent_id = p.parent_id) from public.parent p where p.partition_id in (2, 3) limit 100;
postgres:
"Limit (cost=0.00..2588.75 rows=100 width=116) (actual time=1.420..2.874 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=978 read=15" ... "Planning time: 15.798 ms" "Execution time: 2.988 ms"
"Limit (cost=0.00..2588.75 rows=100 width=116) (actual time=1.420..2.874 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=978 read=15" " -> Result (cost=0.00..172550562.28 rows=6665413 width=116) (actual time=1.418..2.868 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, (SubPlan 1)" " Buffers: shared hit=978 read=15" " -> Append (cost=0.00..179266.65 rows=6665413 width=84) (actual time=0.122..0.170 rows=100 loops=1)" " Buffers: shared read=2" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.002..0.002 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " -> Seq Scan on public.parent_2 p_1 (cost=0.00..89650.83 rows=3333266 width=84) (actual time=0.119..0.153 rows=100 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=2" " -> Seq Scan on public.parent_3 p_2 (cost=0.00..89615.83 rows=3332146 width=84) (never executed)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Filter: (p_2.partition_id = ANY ('{2,3}'::numeric[]))" " SubPlan 1" " -> Aggregate (cost=25.84..25.85 rows=1 width=32) (actual time=0.026..0.026 rows=1 loops=100)" " Output: min((c.child_data)::text)" " Buffers: shared hit=978 read=13" " -> Append (cost=0.00..25.78 rows=25 width=79) (actual time=0.018..0.025 rows=2 loops=100)" " Buffers: shared hit=978 read=13" " -> Seq Scan on public.child c (cost=0.00..0.00 rows=1 width=218) (actual time=0.000..0.000 rows=0 loops=100)" " Output: c.child_data" " Filter: ((c.partition_id = p.partition_id) AND (c.parent_id = p.parent_id))" " -> Index Scan using child_1_parent_id_idx on public.child_1 c_1 (cost=0.43..8.59 rows=8 width=73) (actual time=0.006..0.006 rows=0 loops=100)" " Output: c_1.child_data" " Index Cond: (c_1.parent_id = p.parent_id)" " Filter: (c_1.partition_id = p.partition_id)" " Buffers: shared hit=297 read=3" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_2 (cost=0.43..8.59 rows=8 width=73) (actual time=0.011..0.011 rows=2 loops=100)" " Output: c_2.child_data" " Index Cond: (c_2.parent_id = p.parent_id)" " Filter: (c_2.partition_id = p.partition_id)" " Buffers: shared hit=384 read=7" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_3 (cost=0.43..8.59 rows=8 width=73) (actual time=0.007..0.007 rows=0 loops=100)" " Output: c_3.child_data" " Index Cond: (c_3.parent_id = p.parent_id)" " Filter: (c_3.partition_id = p.partition_id)" " Buffers: shared hit=297 read=3" "Planning time: 15.798 ms" "Execution time: 2.988 ms"
pg_pathman:
"Limit (cost=0.00..865.96 rows=100 width=314) (actual time=0.672..1.692 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=384 read=9" ... "Planning time: 17.897 ms" "Execution time: 1.774 ms"
"Limit (cost=0.00..865.96 rows=100 width=314) (actual time=0.672..1.692 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, ((SubPlan 1))" " Buffers: shared hit=384 read=9" " -> Result (cost=0.00..57719674.22 rows=6665412 width=314) (actual time=0.670..1.683 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, (SubPlan 1)" " Buffers: shared hit=384 read=9" " -> Append (cost=0.00..179266.65 rows=6665412 width=84) (actual time=0.126..0.270 rows=100 loops=1)" " Buffers: shared read=2" " -> Seq Scan on public.parent_2 p (cost=0.00..89650.83 rows=3333266 width=84) (actual time=0.124..0.257 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=2" " -> Seq Scan on public.parent_3 p_1 (cost=0.00..89615.83 rows=3332146 width=84) (never executed)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " SubPlan 1" " -> Aggregate (cost=8.61..8.62 rows=1 width=32) (actual time=0.013..0.014 rows=1 loops=100)" " Output: min((c.child_data)::text)" " Buffers: shared hit=384 read=7" " -> Custom Scan (RuntimeAppend) (cost=0.43..8.59 rows=8 width=218) (actual time=0.010..0.010 rows=2 loops=100)" " Output: c.child_data" " Prune by: (c.partition_id = p.partition_id)" " Buffers: shared hit=384 read=7" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_1 (cost=0.43..8.59 rows=8 width=73) (actual time=0.009..0.010 rows=2 loops=100)" " Output: c_1.child_data, c_1.partition_id" " Index Cond: (c_1.parent_id = p.parent_id)" " Filter: (c_1.partition_id = p.partition_id)" " Buffers: shared hit=384 read=7" "Planning time: 17.897 ms" "Execution time: 1.774 ms"
:
select *, (select min(p.parent_data) from public.parent p where r.partition_id = p.partition_id and r.parent_id = p.parent_id) from public.result r where r.result_id = 50 limit 100;
postgres:
"Limit (cost=0.00..2743.60 rows=100 width=48) (actual time=1.751..41.373 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, ((SubPlan 1))" " Buffers: shared hit=655 read=409" ... "Planning time: 10.229 ms" "Execution time: 41.462 ms"
"Limit (cost=0.00..2743.60 rows=100 width=48) (actual time=1.751..41.373 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, ((SubPlan 1))" " Buffers: shared hit=655 read=409" " -> Seq Scan on public.result r (cost=0.00..250463.09 rows=9129 width=48) (actual time=1.748..41.339 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, (SubPlan 1)" " Filter: (r.result_id = '50'::numeric)" " Rows Removed by Filter: 9709" " Buffers: shared hit=655 read=409" " SubPlan 1" " -> Aggregate (cost=25.36..25.37 rows=1 width=32) (actual time=0.382..0.382 rows=1 loops=100)" " Output: min((p.parent_data)::text)" " Buffers: shared hit=655 read=346" " -> Append (cost=0.00..25.35 rows=4 width=109) (actual time=0.221..0.379 rows=1 loops=100)" " Buffers: shared hit=655 read=346" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=218) (actual time=0.000..0.000 rows=0 loops=100)" " Output: p.parent_data" " Filter: ((r.partition_id = p.partition_id) AND (r.parent_id = p.parent_id))" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..8.45 rows=1 width=73) (actual time=0.218..0.219 rows=1 loops=100)" " Output: p_1.parent_data" " Index Cond: (r.parent_id = p_1.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=222 read=178" " -> Index Scan using parent_2_pkey on public.parent_2 p_2 (cost=0.43..8.45 rows=1 width=73) (actual time=0.078..0.078 rows=0 loops=100)" " Output: p_2.parent_data" " Index Cond: (r.parent_id = p_2.parent_id)" " Filter: (r.partition_id = p_2.partition_id)" " Buffers: shared hit=216 read=85" " -> Index Scan using parent_3_pkey on public.parent_3 p_3 (cost=0.43..8.45 rows=1 width=73) (actual time=0.077..0.077 rows=0 loops=100)" " Output: p_3.parent_data" " Index Cond: (r.parent_id = p_3.parent_id)" " Filter: (r.partition_id = p_3.partition_id)" " Buffers: shared hit=217 read=83" "Planning time: 10.229 ms" "Execution time: 41.462 ms"
pg_pathman:
"Limit (cost=0.00..1052.85 rows=100 width=48) (actual time=0.946..27.732 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, ((SubPlan 1))" " Buffers: shared hit=222 read=241" ... "Planning time: 13.332 ms" "Executiontime: 27.792 ms"
"Limit (cost=0.00..1052.85 rows=100 width=48) (actual time=0.946..27.732 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, ((SubPlan 1))" " Buffers: shared hit=222 read=241" " -> Seq Scan on public.result r (cost=0.00..96114.52 rows=9129 width=48) (actual time=0.944..27.685 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, (SubPlan 1)" " Filter: (r.result_id = '50'::numeric)" " Rows Removed by Filter: 9709" " Buffers: shared hit=222 read=241" " SubPlan 1" " -> Aggregate (cost=8.45..8.46 rows=1 width=32) (actual time=0.245..0.245 rows=1 loops=100)" " Output: min((p.parent_data)::text)" " Buffers: shared hit=222 read=178" " -> Custom Scan (RuntimeAppend) (cost=0.43..8.45 rows=1 width=218) (actual time=0.232..0.233 rows=1 loops=100)" " Output: p.parent_data" " Prune by: (r.partition_id = p.partition_id)" " Buffers: shared hit=222 read=178" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..8.45 rows=1 width=73) (actual time=0.230..0.231 rows=1 loops=100)" " Output: p_1.parent_data, p_1.partition_id" " Index Cond: (r.parent_id = p_1.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=222 read=178" "Planning time: 13.332 ms" "Executiontime: 27.792 ms"
: , . pg_pathman RuntimeAppend 400-500 1000. .
Append postgres: , 3 (never executed), , .
7.3. , union
:
select * from public.parent p, ( select partition_id, parent_id, parent_data from public.parent union all select partition_id, parent_id, child_data from public.child ) u where u.partition_id = p.partition_id and u.parent_id = p.parent_id and p.partition_id = 3 limit 100;
postgres:
"Limit (cost=1.72..12.33 rows=100 width=168) (actual time=1.448..1.795 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=9 read=9" ... "Planning time: 10.951 ms" "Execution time: 1.945 ms"
"Limit (cost=1.72..12.33 rows=100 width=168) (actual time=1.448..1.795 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=9 read=9" " -> Merge Join (cost=1.72..1060723.28 rows=9994523 width=168) (actual time=1.447..1.785 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Merge Cond: (p.parent_id = parent.parent_id)" " Buffers: shared hit=9 read=9" " -> Merge Append (cost=0.56..184533.19 rows=3332147 width=84) (actual time=0.660..0.677 rows=32 loops=1)" " Sort Key: p.parent_id" " Buffers: shared hit=1 read=4" " -> Index Scan using parent_pkey on public.parent p (cost=0.12..8.14 rows=1 width=282) (actual time=0.003..0.003 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared hit=1" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.43..142873.20 rows=3332146 width=84) (actual time=0.656..0.667 rows=32 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = '3'::numeric)" " Buffers: shared read=4" " -> Materialize (cost=1.15..742928.19 rows=9994523 width=84) (actual time=0.781..1.064 rows=100 loops=1)" " Output: parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=8 read=5" " -> Merge Append (cost=1.15..717941.88 rows=9994523 width=84) (actual time=0.773..1.025 rows=100 loops=1)" " Sort Key: parent.parent_id" " Buffers: shared hit=8 read=5" " -> Index Scan using parent_pkey on public.parent (cost=0.12..8.14 rows=1 width=282) (actual time=0.004..0.004 rows=0 loops=1)" " Output: parent.partition_id, parent.parent_id, parent.parent_data" " Filter: (parent.partition_id = '3'::numeric)" " Buffers: shared hit=1" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..142873.20 rows=3332146 width=84) (actual time=0.008..0.023 rows=33 loops=1)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Filter: (parent_3.partition_id = '3'::numeric)" " Buffers: shared hit=4" " -> Index Scan using ix_child_parent on public.child (cost=0.12..8.14 rows=1 width=282) (actual time=0.004..0.004 rows=0 loops=1)" " Output: child.partition_id, child.parent_id, child.child_data" " Filter: (child.partition_id = '3'::numeric)" " Buffers: shared hit=1" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..350175.59 rows=6662375 width=84) (actual time=0.755..0.953 rows=68 loops=1)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Filter: (child_3.partition_id = '3'::numeric)" " Buffers: shared hit=2 read=5" "Planning time: 10.951 ms" "Execution time: 1.945 ms"
pg_pathman:
"Limit (cost=1.31..794053.33 rows=2 width=564) (actual time=0.926..1.108 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Buffers: shared hit=6 read=9" ... "Planning time: 13.963 ms" "Executiontime: 1.197 ms"
"Limit (cost=1.31..794053.33 rows=2 width=564) (actual time=0.926..1.108 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Buffers: shared hit=6 read=9" " -> Merge Join (cost=1.31..794053.33 rows=2 width=564) (actual time=0.925..1.097 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Merge Cond: (p.parent_id = parent_3.parent_id)" " Buffers: shared hit=6 read=9" " -> Merge Append (cost=0.44..142832.56 rows=3332146 width=84) (actual time=0.435..0.448 rows=32 loops=1)" " Sort Key: p.parent_id" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p (cost=0.43..142832.55 rows=3332146 width=84) (actual time=0.435..0.445 rows=32 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared read=4" " -> Materialize (cost=0.87..517958.89 rows=9994521 width=282) (actual time=0.482..0.609 rows=100 loops=1)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Buffers: shared hit=6 read=5" " -> Merge Append (cost=0.87..492972.59 rows=9994521 width=282) (actual time=0.477..0.586 rows=100 loops=1)" " Sort Key: parent_3.parent_id" " Buffers: shared hit=6 read=5" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..142832.55 rows=3332146 width=84) (actual time=0.008..0.017 rows=33 loops=1)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Filter: (parent_3.partition_id = '3'::numeric)" " Buffers: shared hit=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..350140.00 rows=6662375 width=84) (actual time=0.469..0.549 rows=68 loops=1)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Filter: (child_3.partition_id = '3'::numeric)" " Buffers: shared hit=2 read=5" "Planning time: 13.963 ms" "Executiontime: 1.197 ms"
:
select * from public.parent p, ( select partition_id, parent_id, parent_data from public.parent union all select partition_id, parent_id, child_data from public.child ) u where u.partition_id = p.partition_id and u.parent_id = p.parent_id and p.partition_id in (2, 3) limit 100;
postgres:
"Limit (cost=3.97..2222.49 rows=100 width=168) (actual time=6.006..6.250 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=11 read=24" ... "Planning time: 15.399 ms" "Execution time: 6.391 ms"
"Limit (cost=3.97..2222.49 rows=100 width=168) (actual time=6.006..6.250 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=11 read=24" " -> Merge Join (cost=3.97..3327548.38 rows=149989 width=168) (actual time=6.006..6.235 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Merge Cond: (p.parent_id = parent.parent_id)" " Join Filter: (p.partition_id = parent.partition_id)" " Buffers: shared hit=11 read=24" " -> Merge Append (cost=1.01..408151.48 rows=6665413 width=84) (actual time=1.012..1.029 rows=35 loops=1)" " Sort Key: p.parent_id" " Buffers: shared hit=1 read=8" " -> Index Scan using parent_pkey on public.parent p (cost=0.12..8.14 rows=1 width=282) (actual time=0.003..0.003 rows=0 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared hit=1" " -> Index Scan using parent_2_pkey on public.parent_2 p_1 (cost=0.43..142962.29 rows=3333266 width=84) (actual time=0.516..0.520 rows=18 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p_2 (cost=0.43..142873.20 rows=3332146 width=84) (actual time=0.491..0.500 rows=18 loops=1)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Filter: (p_2.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Materialize (cost=2.96..2452767.42 rows=29997730 width=84) (actual time=4.978..5.135 rows=173 loops=1)" " Output: parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=10 read=16" " -> Merge Append (cost=2.96..2377773.10 rows=29997730 width=84) (actual time=4.964..5.057 rows=173 loops=1)" " Sort Key: parent.parent_id" " Buffers: shared hit=10 read=16" " -> Index Scan using parent_pkey on public.parent (cost=0.12..8.14 rows=1 width=282) (actual time=0.003..0.003 rows=0 loops=1)" " Output: parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=1" " -> Index Scan using parent_1_pkey on public.parent_1 (cost=0.43..134721.74 rows=3334606 width=84) (actual time=0.411..0.415 rows=25 loops=1)" " Output: parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared read=4" " -> Index Scan using parent_2_pkey on public.parent_2 (cost=0.43..134629.12 rows=3333266 width=84) (actual time=0.005..0.006 rows=19 loops=1)" " Output: parent_2.partition_id, parent_2.parent_id, parent_2.parent_data" " Buffers: shared hit=4" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..134542.83 rows=3332146 width=84) (actual time=0.005..0.010 rows=18 loops=1)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Buffers: shared hit=4" " -> Index Scan using ix_child_parent on public.child (cost=0.12..8.14 rows=1 width=282) (actual time=0.001..0.001 rows=0 loops=1)" " Output: child.partition_id, child.parent_id, child.child_data" " Buffers: shared hit=1" " -> Index Scan using child_1_parent_id_idx on public.child_1 (cost=0.43..331794.06 rows=6666670 width=84) (actual time=3.310..3.321 rows=50 loops=1)" " Output: child_1.partition_id, child_1.parent_id, child_1.child_data" " Buffers: shared read=4" " -> Index Scan using child_2_parent_id_idx on public.child_2 (cost=0.43..333623.06 rows=6668665 width=84) (actual time=0.553..0.553 rows=29 loops=1)" " Output: child_2.partition_id, child_2.parent_id, child_2.child_data" " Buffers: shared read=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..333519.65 rows=6662375 width=84) (actual time=0.670..0.679 rows=37 loops=1)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Buffers: shared read=4" "Planning time: 15.399 ms" "Execution time: 6.391 ms"
pg_pathman:
"Limit (cost=3.54..2230075.76 rows=1 width=564) (actual time=3.679..4.028 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=8 read=24" ... "Planning time: 20.042 ms" "Execution time: 4.162 ms"
"Limit (cost=3.54..2230075.76 rows=1 width=564) (actual time=3.679..4.028 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=8 read=24" " -> Merge Join (cost=3.54..2230075.76 rows=1 width=564) (actual time=3.678..4.014 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Merge Cond: (p.parent_id = parent_1.parent_id)" " Join Filter: (p.partition_id = parent_1.partition_id)" " Buffers: shared hit=8 read=24" " -> Merge Append (cost=0.87..285722.16 rows=6665412 width=84) (actual time=1.279..1.299 rows=35 loops=1)" " Sort Key: p.parent_id" " Buffers: shared read=8" " -> Index Scan using parent_2_pkey on public.parent_2 p (cost=0.43..142889.60 rows=3333266 width=84) (actual time=0.492..0.498 rows=18 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.43..142832.55 rows=3332146 width=84) (actual time=0.786..0.793 rows=18 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Materialize (cost=2.67..1477724.15 rows=29997728 width=282) (actual time=2.381..2.618 rows=173 loops=1)" " Output: parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=8 read=16" " -> Merge Append (cost=2.67..1402729.83 rows=29997728 width=282) (actual time=2.371..2.575 rows=173 loops=1)" " Sort Key: parent_1.parent_id" " Buffers: shared hit=8 read=16" " -> Index Scan using parent_1_pkey on public.parent_1 (cost=0.43..134675.44 rows=3334606 width=84) (actual time=0.482..0.508 rows=25 loops=1)" " Output: parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared read=4" " -> Index Scan using parent_2_pkey on public.parent_2 (cost=0.43..134599.76 rows=3333266 width=84) (actual time=0.007..0.016 rows=19 loops=1)" " Output: parent_2.partition_id, parent_2.parent_id, parent_2.parent_data" " Buffers: shared hit=4" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..134526.41 rows=3332146 width=84) (actual time=0.006..0.016 rows=18 loops=1)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Buffers: shared hit=4" " -> Index Scan using child_1_parent_id_idx on public.child_1 (cost=0.43..331789.90 rows=6666670 width=84) (actual time=0.473..0.483 rows=50 loops=1)" " Output: child_1.partition_id, child_1.parent_id, child_1.child_data" " Buffers: shared read=4" " -> Index Scan using child_2_parent_id_idx on public.child_2 (cost=0.43..333620.20 rows=6668665 width=84) (actual time=0.716..0.724 rows=29 loops=1)" " Output: child_2.partition_id, child_2.parent_id, child_2.child_data" " Buffers: shared read=4" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..333517.99 rows=6662375 width=84) (actual time=0.679..0.691 rows=37 loops=1)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Buffers: shared read=4" "Planning time: 20.042 ms" "Execution time: 4.162 ms"
:
select * from public.result r, ( select partition_id, parent_id, parent_data from public.parent union all select partition_id, parent_id, child_data from public.child ) u where u.partition_id = r.partition_id and u.parent_id = r.parent_id and r.result_id = 50 limit 100;
postgres:
"Limit (cost=199.17..27826.77 rows=100 width=100) (actual time=10.168..39.515 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=393 read=271" ... "Planning time: 14.982 ms" "Execution time: 39.747 ms"
"Limit (cost=199.17..27826.77 rows=100 width=100) (actual time=10.168..39.515 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=393 read=271" " -> Nested Loop (cost=199.17..423453.93 rows=1532 width=100) (actual time=10.166..39.486 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent.partition_id, parent.parent_id, parent.parent_data" " Buffers: shared hit=393 read=271" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=16) (actual time=9.257..10.275 rows=31 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=14" " Buffers: shared read=52" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=7.507..7.507 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Append (cost=0.00..45.33 rows=29 width=84) (actual time=0.249..0.939 rows=3 loops=31)" " Buffers: shared hit=393 read=219" " -> Seq Scan on public.parent (cost=0.00..0.00 rows=1 width=282) (actual time=0.001..0.001 rows=0 loops=31)" " Output: parent.partition_id, parent.parent_id, parent.parent_data" " Filter: ((r.partition_id = parent.partition_id) AND (r.parent_id = parent.parent_id))" " -> Index Scan using parent_1_pkey on public.parent_1 (cost=0.43..7.19 rows=1 width=84) (actual time=0.244..0.245 rows=1 loops=31)" " Output: parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Index Cond: (parent_1.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_1.partition_id)" " Buffers: shared hit=71 read=53" " -> Index Scan using parent_2_pkey on public.parent_2 (cost=0.43..7.19 rows=1 width=84) (actual time=0.102..0.102 rows=0 loops=31)" " Output: parent_2.partition_id, parent_2.parent_id, parent_2.parent_data" " Index Cond: (parent_2.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_2.partition_id)" " Buffers: shared hit=68 read=26" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..7.19 rows=1 width=84) (actual time=0.104..0.104 rows=0 loops=31)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Index Cond: (parent_3.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_3.partition_id)" " Buffers: shared hit=67 read=26" " -> Seq Scan on public.child (cost=0.00..0.00 rows=1 width=282) (actual time=0.000..0.000 rows=0 loops=31)" " Output: child.partition_id, child.parent_id, child.child_data" " Filter: ((r.partition_id = child.partition_id) AND (r.parent_id = child.parent_id))" " -> Index Scan using child_1_parent_id_idx on public.child_1 (cost=0.43..7.92 rows=8 width=84) (actual time=0.194..0.194 rows=2 loops=31)" " Output: child_1.partition_id, child_1.parent_id, child_1.child_data" " Index Cond: (child_1.parent_id = r.parent_id)" " Filter: (r.partition_id = child_1.partition_id)" " Buffers: shared hit=64 read=56" " -> Index Scan using child_2_parent_id_idx on public.child_2 (cost=0.43..7.92 rows=8 width=84) (actual time=0.144..0.144 rows=0 loops=30)" " Output: child_2.partition_id, child_2.parent_id, child_2.child_data" " Index Cond: (child_2.parent_id = r.parent_id)" " Filter: (r.partition_id = child_2.partition_id)" " Buffers: shared hit=62 read=28" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..7.92 rows=8 width=84) (actual time=0.145..0.145 rows=0 loops=30)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Index Cond: (child_3.parent_id = r.parent_id)" " Filter: (r.partition_id = child_3.partition_id)" " Buffers: shared hit=61 read=30" "Planning time: 14.982 ms" "Execution time: 39.747 ms"
pg_pathman:
"Limit (cost=199.60..411799.35 rows=1 width=298) (actual time=7.118..37.285 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=393 read=271" ... "Planning time: 20.112 ms" "Executiontime: 37.466 ms"
"Limit (cost=199.60..411799.35 rows=1 width=298) (actual time=7.118..37.285 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=393 read=271" " -> Nested Loop (cost=199.60..411799.35 rows=1 width=298) (actual time=7.115..37.259 rows=100 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id, parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Buffers: shared hit=393 read=271" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=16) (actual time=6.511..7.714 rows=31 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=14" " Buffers: shared read=52" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=5.642..5.642 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Append (cost=0.43..44.07 rows=27 width=282) (actual time=0.209..0.950 rows=3 loops=31)" " Buffers: shared hit=393 read=219" " -> Index Scan using parent_1_pkey on public.parent_1 (cost=0.43..6.77 rows=1 width=84) (actual time=0.207..0.208 rows=1 loops=31)" " Output: parent_1.partition_id, parent_1.parent_id, parent_1.parent_data" " Index Cond: (parent_1.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_1.partition_id)" " Buffers: shared hit=71 read=53" " -> Index Scan using parent_2_pkey on public.parent_2 (cost=0.43..6.77 rows=1 width=84) (actual time=0.102..0.102 rows=0 loops=31)" " Output: parent_2.partition_id, parent_2.parent_id, parent_2.parent_data" " Index Cond: (parent_2.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_2.partition_id)" " Buffers: shared hit=68 read=26" " -> Index Scan using parent_3_pkey on public.parent_3 (cost=0.43..6.77 rows=1 width=84) (actual time=0.121..0.121 rows=0 loops=31)" " Output: parent_3.partition_id, parent_3.parent_id, parent_3.parent_data" " Index Cond: (parent_3.parent_id = r.parent_id)" " Filter: (r.partition_id = parent_3.partition_id)" " Buffers: shared hit=67 read=26" " -> Index Scan using child_1_parent_id_idx on public.child_1 (cost=0.43..7.92 rows=8 width=84) (actual time=0.260..0.261 rows=2 loops=31)" " Output: child_1.partition_id, child_1.parent_id, child_1.child_data" " Index Cond: (child_1.parent_id = r.parent_id)" " Filter: (r.partition_id = child_1.partition_id)" " Buffers: shared hit=64 read=56" " -> Index Scan using child_2_parent_id_idx on public.child_2 (cost=0.43..7.92 rows=8 width=84) (actual time=0.125..0.125 rows=0 loops=30)" " Output: child_2.partition_id, child_2.parent_id, child_2.child_data" " Index Cond: (child_2.parent_id = r.parent_id)" " Filter: (r.partition_id = child_2.partition_id)" " Buffers: shared hit=62 read=28" " -> Index Scan using child_3_parent_id_idx on public.child_3 (cost=0.43..7.92 rows=8 width=84) (actual time=0.129..0.129 rows=0 loops=30)" " Output: child_3.partition_id, child_3.parent_id, child_3.child_data" " Index Cond: (child_3.parent_id = r.parent_id)" " Filter: (r.partition_id = child_3.partition_id)" " Buffers: shared hit=61 read=30" "Planning time: 20.112 ms" "Executiontime: 37.466 ms"
union , pg_pathman , . parent , , . RuntimeAppend . pg_pathman.
7.4. in
:
select * frompublic.parent p where (p.partition_id, p.parent_id) in (select c.partition_id, c.parent_id from public.child c where c.child_index = 5000 and c.partition_id = 3) limit 100;
postgres:
"Limit (cost=273.21..858.33 rows=69 width=84) (actual time=5.816..30.351 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" ... "Planning time: 16.899 ms" "Execution time: 30.710 ms"
"Limit (cost=273.21..858.33 rows=69 width=84) (actual time=5.816..30.351 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" " -> Nested Loop (cost=273.21..858.33 rows=69 width=84) (actual time=5.807..30.321 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" " -> HashAggregate (cost=273.21..273.90 rows=69 width=12) (actual time=5.228..5.276 rows=82 loops=1)" " Output: c.partition_id, c.parent_id" " Group Key: c.partition_id, c.parent_id" " Buffers: shared read=85" " -> Append (cost=0.00..272.87 rows=69 width=12) (actual time=1.185..5.143 rows=82 loops=1)" " Buffers: shared read=85" " -> Seq Scan on public.child c (cost=0.00..0.00 rows=1 width=64) (actual time=0.005..0.005 rows=0 loops=1)" " Output: c.partition_id, c.parent_id" " Filter: ((c.child_index = '5000'::numeric) AND (c.partition_id = '3'::numeric))" " -> Bitmap Heap Scan on public.child_3 c_1 (cost=4.96..272.87 rows=68 width=11) (actual time=1.180..5.118 rows=82 loops=1)" " Output: c_1.partition_id, c_1.parent_id" " Recheck Cond: (c_1.child_index = '5000'::numeric)" " Filter: (c_1.partition_id = '3'::numeric)" " Heap Blocks: exact=82" " Buffers: shared read=85" " -> Bitmap Index Scan on child_3_child_index_idx (cost=0.00..4.94 rows=68 width=0) (actual time=1.120..1.120 rows=82 loops=1)" " Index Cond: (c_1.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Append (cost=0.00..8.45 rows=2 width=84) (actual time=0.302..0.304 rows=1 loops=82)" " Buffers: shared hit=134 read=194" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.000..0.000 rows=0 loops=82)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: ((p.partition_id = '3'::numeric) AND (c.parent_id = p.parent_id))" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.43..8.45 rows=1 width=84) (actual time=0.299..0.300 rows=1 loops=82)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = c.parent_id)" " Filter: (p_1.partition_id = '3'::numeric)" " Buffers: shared hit=134 read=194" "Planning time: 16.899 ms" "Execution time: 30.710 ms"
pg_pathman:
"Limit (cost=273.64..849.17 rows=68 width=282) (actual time=11.865..34.016 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" ... "Planning time: 21.265 ms" "Executiontime: 34.098 ms"
"Limit (cost=273.64..849.17 rows=68 width=282) (actual time=11.865..34.016 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" " -> Nested Loop (cost=273.64..849.17 rows=68 width=282) (actual time=11.862..33.985 rows=82 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=134 read=279" " -> HashAggregate (cost=273.21..273.89 rows=68 width=11) (actual time=11.323..11.364 rows=82 loops=1)" " Output: c.partition_id, c.parent_id" " Group Key: c.partition_id, c.parent_id" " Buffers: shared read=85" " -> Append (cost=4.96..272.87 rows=68 width=11) (actual time=1.940..11.150 rows=82 loops=1)" " Buffers: shared read=85" " -> Bitmap Heap Scan on public.child_3 c (cost=4.96..272.87 rows=68 width=11) (actual time=1.938..11.132 rows=82 loops=1)" " Output: c.partition_id, c.parent_id" " Recheck Cond: (c.child_index = '5000'::numeric)" " Filter: (c.partition_id = '3'::numeric)" " Heap Blocks: exact=82" " Buffers: shared read=85" " -> Bitmap Index Scan on child_3_child_index_idx (cost=0.00..4.94 rows=68 width=0) (actual time=1.655..1.655 rows=82 loops=1)" " Index Cond: (c.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Append (cost=0.43..8.45 rows=1 width=84) (actual time=0.274..0.275 rows=1 loops=82)" " Buffers: shared hit=134 read=194" " -> Index Scan using parent_3_pkey on public.parent_3 p (cost=0.43..8.45 rows=1 width=84) (actual time=0.273..0.274 rows=1 loops=82)" " Output: p.parent_id, p.parent_data, p.partition_id" " Index Cond: (p.parent_id = c.parent_id)" " Filter: (p.partition_id = '3'::numeric)" " Buffers: shared hit=134 read=194" "Planning time: 21.265 ms" "Executiontime: 34.098 ms"
:
select * from public.parent p where (p.partition_id, p.parent_id) in (select c.partition_id, c.parent_id from public.child c where c.child_index = 5000 and c.partition_id in (2, 3)) limit 100;
postgres:
"Limit (cost=538.59..3078.59 rows=100 width=84) (actual time=20.449..90.114 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=510 read=642" ... "Planning time: 14.024 ms" "Execution time: 90.484 ms"
"Limit (cost=538.59..3078.59 rows=100 width=84) (actual time=20.449..90.114 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=510 read=642" " -> Nested Loop (cost=538.59..3967.59 rows=135 width=84) (actual time=20.446..90.045 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=510 read=642" " -> HashAggregate (cost=538.59..539.94 rows=135 width=11) (actual time=19.387..19.486 rows=100 loops=1)" " Output: c.partition_id, c.parent_id" " Group Key: c.partition_id, c.parent_id" " Buffers: shared read=155" " -> Append (cost=0.00..537.91 rows=135 width=11) (actual time=1.090..19.200 rows=149 loops=1)" " Buffers: shared read=155" " -> Seq Scan on public.child c (cost=0.00..0.00 rows=1 width=64) (actual time=0.005..0.005 rows=0 loops=1)" " Output: c.partition_id, c.parent_id" " Filter: ((c.partition_id = ANY ('{2,3}'::numeric[])) AND (c.child_index = '5000'::numeric))" " -> Bitmap Heap Scan on public.child_2 c_1 (cost=4.94..265.05 rows=66 width=11) (actual time=1.083..5.456 rows=67 loops=1)" " Output: c_1.partition_id, c_1.parent_id" " Recheck Cond: (c_1.child_index = '5000'::numeric)" " Filter: (c_1.partition_id = ANY ('{2,3}'::numeric[]))" " Heap Blocks: exact=67" " Buffers: shared read=70" " -> Bitmap Index Scan on child_2_child_index_idx (cost=0.00..4.93 rows=66 width=0) (actual time=1.003..1.003 rows=67 loops=1)" " Index Cond: (c_1.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Bitmap Heap Scan on public.child_3 c_2 (cost=4.96..272.87 rows=68 width=11) (actual time=1.271..13.668 rows=82 loops=1)" " Output: c_2.partition_id, c_2.parent_id" " Recheck Cond: (c_2.child_index = '5000'::numeric)" " Filter: (c_2.partition_id = ANY ('{2,3}'::numeric[]))" " Heap Blocks: exact=82" " Buffers: shared read=85" " -> Bitmap Index Scan on child_3_child_index_idx (cost=0.00..4.94 rows=68 width=0) (actual time=0.781..0.781 rows=82 loops=1)" " Index Cond: (c_2.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Append (cost=0.00..25.35 rows=4 width=84) (actual time=0.615..0.704 rows=1 loops=100)" " Buffers: shared hit=510 read=487" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.000..0.000 rows=0 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: ((c.partition_id = p.partition_id) AND (c.parent_id = p.parent_id))" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..8.45 rows=1 width=84) (actual time=0.193..0.193 rows=0 loops=100)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = c.parent_id)" " Filter: (c.partition_id = p_1.partition_id)" " Buffers: shared hit=171 read=129" " -> Index Scan using parent_2_pkey on public.parent_2 p_2 (cost=0.43..8.45 rows=1 width=84) (actual time=0.225..0.225 rows=0 loops=100)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Index Cond: (p_2.parent_id = c.parent_id)" " Filter: (c.partition_id = p_2.partition_id)" " Buffers: shared hit=170 read=172" " -> Index Scan using parent_3_pkey on public.parent_3 p_3 (cost=0.43..8.45 rows=1 width=84) (actual time=0.280..0.280 rows=1 loops=99)" " Output: p_3.parent_id, p_3.parent_data, p_3.partition_id" " Index Cond: (p_3.parent_id = c.parent_id)" " Filter: (c.partition_id = p_3.partition_id)" " Buffers: shared hit=169 read=186" "Planning time: 14.024 ms" "Execution time: 90.484 ms"
pg_pathman:
"Limit (cost=539.01..1385.69 rows=100 width=282) (actual time=17.913..48.317 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=152 read=403" ... "Planning time: 14.037 ms" "Execution time: 48.413 ms"
"Limit (cost=539.01..1385.69 rows=100 width=282) (actual time=17.913..48.317 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=152 read=403" " -> Nested Loop (cost=539.01..1673.56 rows=134 width=282) (actual time=17.911..48.286 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=152 read=403" " -> HashAggregate (cost=538.58..539.92 rows=134 width=11) (actual time=17.359..17.411 rows=100 loops=1)" " Output: c.partition_id, c.parent_id" " Group Key: c.partition_id, c.parent_id" " Buffers: shared read=155" " -> Append (cost=4.94..537.91 rows=134 width=11) (actual time=0.503..17.149 rows=149 loops=1)" " Buffers: shared read=155" " -> Bitmap Heap Scan on public.child_2 c (cost=4.94..265.05 rows=66 width=11) (actual time=0.503..7.763 rows=67 loops=1)" " Output: c.partition_id, c.parent_id" " Recheck Cond: (c.child_index = '5000'::numeric)" " Filter: (c.partition_id = ANY ('{2,3}'::numeric[]))" " Heap Blocks: exact=67" " Buffers: shared read=70" " -> Bitmap Index Scan on child_2_child_index_idx (cost=0.00..4.93 rows=66 width=0) (actual time=0.392..0.392 rows=67 loops=1)" " Index Cond: (c.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Bitmap Heap Scan on public.child_3 c_1 (cost=4.96..272.87 rows=68 width=11) (actual time=0.591..9.346 rows=82 loops=1)" " Output: c_1.partition_id, c_1.parent_id" " Recheck Cond: (c_1.child_index = '5000'::numeric)" " Filter: (c_1.partition_id = ANY ('{2,3}'::numeric[]))" " Heap Blocks: exact=82" " Buffers: shared read=85" " -> Bitmap Index Scan on child_3_child_index_idx (cost=0.00..4.94 rows=68 width=0) (actual time=0.357..0.357 rows=82 loops=1)" " Index Cond: (c_1.child_index = '5000'::numeric)" " Buffers: shared read=3" " -> Custom Scan (RuntimeAppend) (cost=0.43..8.45 rows=1 width=84) (actual time=0.299..0.300 rows=1 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Prune by: (c.partition_id = p.partition_id)" " Buffers: shared hit=152 read=248" " -> Index Scan using parent_2_pkey on public.parent_2 p_1 (cost=0.43..8.45 rows=1 width=84) (actual time=0.293..0.293 rows=1 loops=42)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = c.parent_id)" " Filter: (c.partition_id = p_1.partition_id)" " Buffers: shared hit=62 read=106" " -> Index Scan using parent_3_pkey on public.parent_3 p_2 (cost=0.43..8.45 rows=1 width=84) (actual time=0.301..0.302 rows=1 loops=58)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Index Cond: (p_2.parent_id = c.parent_id)" " Filter: (c.partition_id = p_2.partition_id)" " Buffers: shared hit=90 read=142" "Planning time: 14.037 ms" "Execution time: 48.413 ms"
:
select * from public.parent p where (p.partition_id, p.parent_id) in (select r.partition_id, r.parent_id from public.result r where r.result_id = 50) limit 100;
postgres:
"Limit (cost=7049.27..9084.40 rows=100 width=84) (actual time=593.094..692.189 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=506 read=5583" ... "Planning time: 8.554 ms" "Execution time: 692.468 ms"
"Limit (cost=7049.27..9084.40 rows=100 width=84) (actual time=593.094..692.189 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=506 read=5583" " -> Nested Loop (cost=7049.27..192836.06 rows=9129 width=84) (actual time=593.091..692.134 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=506 read=5583" " -> HashAggregate (cost=7049.27..7140.51 rows=9124 width=11) (actual time=592.396..592.519 rows=100 loops=1)" " Output: r.partition_id, r.parent_id" " Group Key: r.partition_id, r.parent_id" " Buffers: shared read=5094" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=11) (actual time=10.049..576.423 rows=10051 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=5056" " Buffers: shared read=5094" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=9.087..9.087 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Append (cost=0.00..20.31 rows=4 width=84) (actual time=0.733..0.994 rows=1 loops=100)" " Buffers: shared hit=506 read=489" " -> Seq Scan on public.parent p (cost=0.00..0.00 rows=1 width=282) (actual time=0.000..0.000 rows=0 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: ((r.partition_id = p.partition_id) AND (r.parent_id = p.parent_id))" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..6.77 rows=1 width=84) (actual time=0.315..0.316 rows=0 loops=100)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = r.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=170 read=158" " -> Index Scan using parent_2_pkey on public.parent_2 p_2 (cost=0.43..6.77 rows=1 width=84) (actual time=0.355..0.358 rows=0 loops=99)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Index Cond: (p_2.parent_id = r.parent_id)" " Filter: (r.partition_id = p_2.partition_id)" " Buffers: shared hit=168 read=170" " -> Index Scan using parent_3_pkey on public.parent_3 p_3 (cost=0.43..6.77 rows=1 width=84) (actual time=0.318..0.319 rows=0 loops=99)" " Output: p_3.parent_id, p_3.parent_data, p_3.partition_id" " Index Cond: (p_3.parent_id = r.parent_id)" " Filter: (r.partition_id = p_3.partition_id)" " Buffers: shared hit=168 read=161" "Planning time: 8.554 ms" "Execution time: 692.468 ms"
pg_pathman:
"Limit (cost=7049.70..7728.77 rows=100 width=282) (actual time=531.946..564.520 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=137 read=5358" ... "Planning time: 8.863 ms" "Executiontime: 564.741 ms"
"Limit (cost=7049.70..7728.77 rows=100 width=282) (actual time=531.946..564.520 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=137 read=5358" " -> Nested Loop (cost=7049.70..69008.61 rows=9124 width=282) (actual time=531.943..564.483 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Buffers: shared hit=137 read=5358" " -> HashAggregate (cost=7049.27..7140.51 rows=9124 width=11) (actual time=531.407..531.479 rows=100 loops=1)" " Output: r.partition_id, r.parent_id" " Group Key: r.partition_id, r.parent_id" " Buffers: shared read=5094" " -> Bitmap Heap Scan on public.result r (cost=199.17..7003.62 rows=9129 width=11) (actual time=11.477..518.114 rows=10051 loops=1)" " Output: r.result_id, r.parent_id, r.partition_id" " Recheck Cond: (r.result_id = '50'::numeric)" " Heap Blocks: exact=5056" " Buffers: shared read=5094" " -> Bitmap Index Scan on idx_result (cost=0.00..196.89 rows=9129 width=0) (actual time=10.655..10.655 rows=10051 loops=1)" " Index Cond: (r.result_id = '50'::numeric)" " Buffers: shared read=38" " -> Custom Scan (RuntimeAppend) (cost=0.43..6.77 rows=1 width=84) (actual time=0.320..0.322 rows=1 loops=100)" " Output: p.parent_id, p.parent_data, p.partition_id" " Prune by: (r.partition_id = p.partition_id)" " Buffers: shared hit=137 read=264" " -> Index Scan using parent_1_pkey on public.parent_1 p_1 (cost=0.43..6.77 rows=1 width=84) (actual time=0.329..0.330 rows=1 loops=28)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Index Cond: (p_1.parent_id = r.parent_id)" " Filter: (r.partition_id = p_1.partition_id)" " Buffers: shared hit=37 read=75" " -> Index Scan using parent_2_pkey on public.parent_2 p_2 (cost=0.43..6.77 rows=1 width=84) (actual time=0.313..0.317 rows=1 loops=40)" " Output: p_2.parent_id, p_2.parent_data, p_2.partition_id" " Index Cond: (p_2.parent_id = r.parent_id)" " Filter: (r.partition_id = p_2.partition_id)" " Buffers: shared hit=56 read=105" " -> Index Scan using parent_3_pkey on public.parent_3 p_3 (cost=0.43..6.77 rows=1 width=84) (actual time=0.317..0.317 rows=1 loops=32)" " Output: p_3.parent_id, p_3.parent_data, p_3.partition_id" " Index Cond: (p_3.parent_id = r.parent_id)" " Filter: (r.partition_id = p_3.partition_id)" " Buffers: shared hit=44 read=84" "Planning time: 8.863 ms" "Executiontime: 564.741 ms"
, , . , . partition pruning: RuntimeAppend (42 + 58), , . , - , . . , , ( ), .
exists , :
select * from public.parent p where exists (select 1 from public.child c where p.partition_id = c.partition_id and p.parent_id = c.parent_id and c.child_index = 5000 and c.partition_id in (2, 3)) limit 100;
7.5。 30- .
, RuntimeAppend . 30- pg_pathman. 7.1:
select * from public.parent p, public.child c where c.partition_id = p.partition_id and c.parent_id = p.parent_id and p.partition_id in (2, 3) limit 100;
:
"Limit (cost=17.38..294.92 rows=100 width=628) (actual time=31.483..33.538 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=131" ... "Planning time: 48.911 ms" "Executiontime: 34.143 ms"
"Limit (cost=17.38..294.92 rows=100 width=628) (actual time=31.483..33.538 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=131" " -> Merge Join (cost=17.38..1318082.71 rows=474913 width=628) (actual time=31.482..33.514 rows=100 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id, c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Merge Cond: (p.parent_id = c.parent_id)" " Join Filter: (p.partition_id = c.partition_id)" " Buffers: shared hit=2 read=131" " -> Merge Append (cost=0.85..28624.08 rows=666900 width=84) (actual time=2.625..2.651 rows=48 loops=1)" " Sort Key: p.parent_id" " Buffers: shared read=8" " -> Index Scan using parent_2_pkey on public.parent_2 p (cost=0.42..14289.87 rows=332887 width=84) (actual time=1.362..1.370 rows=28 loops=1)" " Output: p.parent_id, p.parent_data, p.partition_id" " Filter: (p.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Index Scan using parent_3_pkey on public.parent_3 p_1 (cost=0.42..14334.21 rows=334013 width=84) (actual time=1.262..1.272 rows=21 loops=1)" " Output: p_1.parent_id, p_1.parent_data, p_1.partition_id" " Filter: (p_1.partition_id = ANY ('{2,3}'::numeric[]))" " Buffers: shared read=4" " -> Materialize (cost=13.49..1059692.47 rows=20004382 width=98) (actual time=28.789..30.453 rows=1369 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared hit=2 read=123" " -> Merge Append (cost=13.49..1009681.51 rows=20004382 width=98) (actual time=28.782..30.170 rows=1369 loops=1)" " Sort Key: c.parent_id" " Buffers: shared hit=2 read=123" " -> Index Scan using child_1_parent_id_idx on public.child_1 c (cost=0.42..32766.35 rows=664299 width=97) (actual time=0.895..0.909 rows=47 loops=1)" " Output: c.child_id, c.parent_id, c.child_data, c.child_index, c.partition_id" " Buffers: shared read=4" " -> Index Scan using child_2_parent_id_idx on public.child_2 c_1 (cost=0.42..33032.19 rows=664696 width=97) (actual time=0.835..1.048 rows=62 loops=1)" " Output: c_1.child_id, c_1.parent_id, c_1.child_data, c_1.child_index, c_1.partition_id" " Buffers: shared read=5" " -> Index Scan using child_3_parent_id_idx on public.child_3 c_2 (cost=0.42..33625.31 rows=668577 width=98) (actual time=0.849..0.858 rows=39 loops=1)" " Output: c_2.child_id, c_2.parent_id, c_2.child_data, c_2.child_index, c_2.partition_id" " Buffers: shared read=4" " -> Index Scan using child_4_parent_id_idx on public.child_4 c_3 (cost=0.42..32859.69 rows=668831 width=98) (actual time=0.746..0.772 rows=58 loops=1)" " Output: c_3.child_id, c_3.parent_id, c_3.child_data, c_3.child_index, c_3.partition_id" " Buffers: shared read=4" " -> Index Scan using child_5_parent_id_idx on public.child_5 c_4 (cost=0.42..34070.24 rows=667684 width=98) (actual time=1.037..1.043 rows=44 loops=1)" " Output: c_4.child_id, c_4.parent_id, c_4.child_data, c_4.child_index, c_4.partition_id" " Buffers: shared read=4" " -> Index Scan using child_6_parent_id_idx on public.child_6 c_5 (cost=0.42..34413.73 rows=668521 width=98) (actual time=1.248..1.255 rows=63 loops=1)" " Output: c_5.child_id, c_5.parent_id, c_5.child_data, c_5.child_index, c_5.partition_id" " Buffers: shared read=4" " -> Index Scan using child_7_parent_id_idx on public.child_7 c_6 (cost=0.42..33858.17 rows=664992 width=97) (actual time=1.084..1.089 rows=31 loops=1)" " Output: c_6.child_id, c_6.parent_id, c_6.child_data, c_6.child_index, c_6.partition_id" " Buffers: shared read=4" " -> Index Scan using child_8_parent_id_idx on public.child_8 c_7 (cost=0.42..33396.85 rows=666651 width=98) (actual time=0.674..0.804 rows=64 loops=1)" " Output: c_7.child_id, c_7.parent_id, c_7.child_data, c_7.child_index, c_7.partition_id" " Buffers: shared read=5" " -> Index Scan using child_9_parent_id_idx on public.child_9 c_8 (cost=0.42..33535.73 rows=665594 width=98) (actual time=1.142..1.148 rows=53 loops=1)" " Output: c_8.child_id, c_8.parent_id, c_8.child_data, c_8.child_index, c_8.partition_id" " Buffers: shared read=4" " -> Index Scan using child_10_parent_id_idx on public.child_10 c_9 (cost=0.42..34376.76 rows=667885 width=97) (actual time=1.112..1.117 rows=36 loops=1)" " Output: c_9.child_id, c_9.parent_id, c_9.child_data, c_9.child_index, c_9.partition_id" " Buffers: shared read=4" " -> Index Scan using child_11_parent_id_idx on public.child_11 c_10 (cost=0.42..34520.23 rows=667304 width=98) (actual time=0.875..0.883 rows=43 loops=1)" " Output: c_10.child_id, c_10.parent_id, c_10.child_data, c_10.child_index, c_10.partition_id" " Buffers: shared read=4" " -> Index Scan using child_12_parent_id_idx on public.child_12 c_11 (cost=0.42..34516.73 rows=667192 width=97) (actual time=0.725..0.731 rows=37 loops=1)" " Output: c_11.child_id, c_11.parent_id, c_11.child_data, c_11.child_index, c_11.partition_id" " Buffers: shared read=4" " -> Index Scan using child_13_parent_id_idx on public.child_13 c_12 (cost=0.42..34775.47 rows=665684 width=98) (actual time=0.799..0.807 rows=46 loops=1)" " Output: c_12.child_id, c_12.parent_id, c_12.child_data, c_12.child_index, c_12.partition_id" " Buffers: shared read=4" " -> Index Scan using child_14_parent_id_idx on public.child_14 c_13 (cost=0.42..32759.51 rows=667829 width=97) (actual time=0.952..0.966 rows=51 loops=1)" " Output: c_13.child_id, c_13.parent_id, c_13.child_data, c_13.child_index, c_13.partition_id" " Buffers: shared read=4" " -> Index Scan using child_15_parent_id_idx on public.child_15 c_14 (cost=0.42..33834.34 rows=666471 width=97) (actual time=0.713..0.719 rows=30 loops=1)" " Output: c_14.child_id, c_14.parent_id, c_14.child_data, c_14.child_index, c_14.partition_id" " Buffers: shared read=4" " -> Index Scan using child_16_parent_id_idx on public.child_16 c_15 (cost=0.42..33276.75 rows=665648 width=98) (actual time=0.887..0.892 rows=40 loops=1)" " Output: c_15.child_id, c_15.parent_id, c_15.child_data, c_15.child_index, c_15.partition_id" " Buffers: shared read=4" " -> Index Scan using child_17_parent_id_idx on public.child_17 c_16 (cost=0.42..34426.11 rows=666740 width=98) (actual time=0.820..0.835 rows=50 loops=1)" " Output: c_16.child_id, c_16.parent_id, c_16.child_data, c_16.child_index, c_16.partition_id" " Buffers: shared read=4" " -> Index Scan using child_18_parent_id_idx on public.child_18 c_17 (cost=0.42..31990.71 rows=665008 width=98) (actual time=0.780..0.794 rows=52 loops=1)" " Output: c_17.child_id, c_17.parent_id, c_17.child_data, c_17.child_index, c_17.partition_id" " Buffers: shared read=4" " -> Index Scan using child_19_parent_id_idx on public.child_19 c_18 (cost=0.42..34141.36 rows=669231 width=97) (actual time=0.782..0.792 rows=42 loops=1)" " Output: c_18.child_id, c_18.parent_id, c_18.child_data, c_18.child_index, c_18.partition_id" " Buffers: shared read=4" " -> Index Scan using child_20_parent_id_idx on public.child_20 c_19 (cost=0.42..33065.75 rows=666164 width=98) (actual time=1.134..1.143 rows=44 loops=1)" " Output: c_19.child_id, c_19.parent_id, c_19.child_data, c_19.child_index, c_19.partition_id" " Buffers: shared read=4" " -> Index Scan using child_21_parent_id_idx on public.child_21 c_20 (cost=0.42..34236.36 rows=667822 width=98) (actual time=1.122..1.131 rows=53 loops=1)" " Output: c_20.child_id, c_20.parent_id, c_20.child_data, c_20.child_index, c_20.partition_id" " Buffers: shared read=4" " -> Index Scan using child_22_parent_id_idx on public.child_22 c_21 (cost=0.42..33809.84 rows=664828 width=97) (actual time=1.334..1.344 rows=52 loops=1)" " Output: c_21.child_id, c_21.parent_id, c_21.child_data, c_21.child_index, c_21.partition_id" " Buffers: shared read=4" " -> Index Scan using child_23_parent_id_idx on public.child_23 c_22 (cost=0.42..34169.35 rows=668321 width=98) (actual time=1.478..1.482 rows=30 loops=1)" " Output: c_22.child_id, c_22.parent_id, c_22.child_data, c_22.child_index, c_22.partition_id" " Buffers: shared read=4" " -> Index Scan using child_24_parent_id_idx on public.child_24 c_23 (cost=0.42..31693.94 rows=669014 width=98) (actual time=0.936..0.940 rows=30 loops=1)" " Output: c_23.child_id, c_23.parent_id, c_23.child_data, c_23.child_index, c_23.partition_id" " Buffers: shared read=4" " -> Index Scan using child_25_parent_id_idx on public.child_25 c_24 (cost=0.42..34224.95 rows=666004 width=98) (actual time=0.869..0.881 rows=60 loops=1)" " Output: c_24.child_id, c_24.parent_id, c_24.child_data, c_24.child_index, c_24.partition_id" " Buffers: shared read=4" " -> Index Scan using child_26_parent_id_idx on public.child_26 c_25 (cost=0.42..32132.54 rows=665401 width=97) (actual time=1.129..1.138 rows=53 loops=1)" " Output: c_25.child_id, c_25.parent_id, c_25.child_data, c_25.child_index, c_25.partition_id" " Buffers: shared read=4" " -> Index Scan using child_27_parent_id_idx on public.child_27 c_26 (cost=0.42..34930.21 rows=668930 width=97) (actual time=1.307..1.312 rows=42 loops=1)" " Output: c_26.child_id, c_26.parent_id, c_26.child_data, c_26.child_index, c_26.partition_id" " Buffers: shared read=4" " -> Index Scan using child_28_parent_id_idx on public.child_28 c_27 (cost=0.42..33712.49 rows=666743 width=98) (actual time=0.782..0.793 rows=39 loops=1)" " Output: c_27.child_id, c_27.parent_id, c_27.child_data, c_27.child_index, c_27.partition_id" " Buffers: shared read=4" " -> Index Scan using child_29_parent_id_idx on public.child_29 c_28 (cost=0.42..34281.07 rows=665822 width=98) (actual time=0.879..0.886 rows=42 loops=1)" " Output: c_28.child_id, c_28.parent_id, c_28.child_data, c_28.child_index, c_28.partition_id" " Buffers: shared read=4" " -> Index Scan using child_30_parent_id_idx on public.child_30 c_29 (cost=0.42..33248.03 rows=666496 width=98) (actual time=0.816..0.934 rows=65 loops=1)" " Output: c_29.child_id, c_29.parent_id, c_29.child_data, c_29.child_index, c_29.partition_id" " Buffers: shared hit=2 read=5" "Planning time: 48.911 ms" "Executiontime: 34.143 ms"
parent, child pg_pathman, .
8.
1) , , select. , . , , , , , .
2) pg_pathman — . , , .
3) , (100-1000) .
4) pg_pathman, RuntimeAppend, .
5) , , RuntimeAppend , . . , . , , , . , .
6) . , .
, !