PostgreSQL 高度技術者育成テキスト ch4 (性能監視とパフォーマンス) (1/2)
LPI-Japan OSS-DB Gold 認定教材 PostgreSQL 高度技術者育成テキスト
- 作者:河原 翔
- 発売日: 2014/10/27
- メディア: オンデマンド (ペーパーバック)
アクセス統計情報
- 「何回スキャンした」とか「何行フェッチした」とか「キャッシュヒット率は」とかそういうの
パラメータ
#track_counts = on #track_activities = on #stats_temp_directory = 'pg_stat_tmp'
SELECT name,setting,context FROM pg_settings WHERE name IN ('track_counts','track_activities','stats_temp_directory');
name | setting | context ----------------------+-------------+----------- stats_temp_directory | pg_stat_tmp | sighup track_activities | on | superuser track_counts | on | superuser (3 rows)
stats_temp_directory
をRAMディスク等にすることで性能向上を図ることができる
標準統計情報ビュー
- 同一トランザクション内では同一結果を取得する
pg_stat_clear_snapshot()
関数を呼び出すと最新情報に更新
pg_stat_activity
SELECT * FROM pg_stat_activity;
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start | query_start | state_change | wait_event_type | wait_event | state | backend_xid | backend_xmin | query | backend_type -------+----------+------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+-------------------------------+-------------------------------+-----------------+---------------------+--------+-------------+--------------+---------------------------------+------------------------------ | | 60 | | | | | | | 2020-02-28 05:08:31.562713+00 | | | | Activity | AutoVacuumMain | | | | | autovacuum launcher | | 62 | 10 | postgres | | | | | 2020-02-28 05:08:31.563011+00 | | | | Activity | LogicalLauncherMain | | | | | logical replication launcher 13408 | postgres | 5930 | 10 | postgres | psql | | | -1 | 2020-02-28 13:59:31.380246+00 | 2020-02-28 14:36:17.461076+00 | 2020-02-28 14:36:17.461076+00 | 2020-02-28 14:36:17.461078+00 | | | active | | 724 | SELECT * FROM pg_stat_activity; | client backend | | 58 | | | | | | | 2020-02-28 05:08:31.562336+00 | | | | Activity | BgWriterMain | | | | | background writer | | 57 | | | | | | | 2020-02-28 05:08:31.562139+00 | | | | Activity | CheckpointerMain | | | | | checkpointer | | 59 | | | | | | | 2020-02-28 05:08:31.562557+00 | | | | Activity | WalWriterMain | | | | | walwriter (6 rows)
- クエリでlike検索してバックエンドプロセスのpidを特定したりできる
SELECT pid,query FROM pg_stat_activity WHERE query ~ 'select';
pid | query ------+---------------------------------------------------------------- 5930 | SELECT pid,query FROM pg_stat_activity WHERE query ~ 'select'; (1 row)
- pidを特定したら、クエリを中止したり接続を切断したりできる
- 公式/シグナリング関数
pg_cancel_backend(pid int) pg_terminate_backend(pid int)
- ロングトランザクションの特定には
xact_start
カラムが有用
SELECT pid,xact_start,current_timestamp - xact_start as duration,query FROM pg_stat_activity;
pid | xact_start | duration | query ------+-------------------------------+-----------------+----------------------------------------------------------------------------------------------- 60 | | | 62 | | | 5930 | 2020-02-28 14:44:39.646029+00 | 00:00:00 | SELECT pid,xact_start,current_timestamp - xact_start as duration,query FROM pg_stat_activity; 6266 | 2020-02-28 14:43:26.322961+00 | 00:01:13.323068 | begin; 58 | | | 57 | | | 59 | | | (7 rows)
pg_stat_database
SELECT * FROM pg_stat_database WHERE datname='postgres';
-[ RECORD 1 ]---------+------------------------------ datid | 13408 datname | postgres numbackends | 2 xact_commit | 2534 xact_rollback | 37 blks_read | 118132 blks_hit | 44975739 tup_returned | 33852918 tup_fetched | 29521 tup_inserted | 13000518 tup_updated | 2001360 tup_deleted | 6000331 conflicts | 0 temp_files | 10 temp_bytes | 80207872 deadlocks | 0 checksum_failures | checksum_last_failure | blk_read_time | 0 blk_write_time | 0 stats_reset | 2020-02-28 05:31:21.975402+00
- キャッシュヒット率を算出できる
SELECT 100.0*blks_hit/(blks_hit+blks_read) AS cache_hit_percent FROM pg_stat_database WHERE datname='postgres';
cache_hit_percent --------------------- 99.7380336249316627 (1 row)
- 共有バッファ上にないデータを取得すると
blks_read
が増加する - が、必ずしもディスクアクセスは発生していないことに留意する
- OSのファイルシステムキャッシュ
pg_stat_bgwriter
SELECT * FROM pg_stat_bgwriter;
-[ RECORD 1 ]---------+------------------------------ checkpoints_timed | 116 checkpoints_req | 0 checkpoint_write_time | 1475203 checkpoint_sync_time | 175 buffers_checkpoint | 56492 buffers_clean | 0 maxwritten_clean | 0 buffers_backend | 65991 buffers_backend_fsync | 0 buffers_alloc | 67479 stats_reset | 2020-02-28 05:08:31.361955+00
- dirty pageの書き戻しの3パターン
- checkpointer processによるもの
buffers_checkpoint
- backgroud writer processによるもの
buffers_clean
- background writerによる書き戻しは「クリーニング」という
- backend processによるもの
buffers_backend
- backend processが新しいデータを共有バッファに載せようとして空きがないときに生じる
buffers_backend
が大きいということは、swapが頻発しているようなもの。shared_buffers
のチューニングを検討する
pg_stat_all_tables
SELECT * FROM pg_stat_all_tables WHERE relname='tbl';
-[ RECORD 1 ]-------+------------------------------ relid | 16517 schemaname | public relname | tbl seq_scan | 4 seq_tup_read | 2000000 idx_scan | 0 idx_tup_fetch | 0 n_tup_ins | 1000000 n_tup_upd | 500000 n_tup_del | 0 n_tup_hot_upd | 0 n_live_tup | 1000000 n_dead_tup | 0 n_mod_since_analyze | 0 last_vacuum | last_autovacuum | 2020-02-28 11:40:21.269596+00 last_analyze | 2020-02-28 11:39:47.092236+00 last_autoanalyze | 2020-02-28 11:38:20.543509+00 vacuum_count | 0 autovacuum_count | 1 analyze_count | 2 autoanalyze_count | 1
- 取れる情報
- シーケンシャルスキャン1回あたり読み込んだタプル数
seq_tup_read / seq_scan
- VACUUM対象行数
n_dead_tup
- HOT更新比率
n_hot_hot_upd / n_hot_upd
- シーケンシャルスキャン1回あたり読み込んだタプル数
pg_stat_user_tables
やpg_stat_sys_tables
も同じ情報
SELECT * FROM pg_views WHERE viewname = 'pg_stat_user_tables';
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------ schemaname | pg_catalog viewname | pg_stat_user_tables viewowner | postgres definition | SELECT pg_stat_all_tables.relid, + | pg_stat_all_tables.schemaname, + | pg_stat_all_tables.relname, + | pg_stat_all_tables.seq_scan, + | pg_stat_all_tables.seq_tup_read, + | pg_stat_all_tables.idx_scan, + | pg_stat_all_tables.idx_tup_fetch, + | pg_stat_all_tables.n_tup_ins, + | pg_stat_all_tables.n_tup_upd, + | pg_stat_all_tables.n_tup_del, + | pg_stat_all_tables.n_tup_hot_upd, + | pg_stat_all_tables.n_live_tup, + | pg_stat_all_tables.n_dead_tup, + | pg_stat_all_tables.n_mod_since_analyze, + | pg_stat_all_tables.last_vacuum, + | pg_stat_all_tables.last_autovacuum, + | pg_stat_all_tables.last_analyze, + | pg_stat_all_tables.last_autoanalyze, + | pg_stat_all_tables.vacuum_count, + | pg_stat_all_tables.autovacuum_count, + | pg_stat_all_tables.analyze_count, + | pg_stat_all_tables.autoanalyze_count + | FROM pg_stat_all_tables + | WHERE ((pg_stat_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_stat_all_tables.schemaname !~ '^pg_toast'::text));
pg_statio_all_tables
SELECT * FROM pg_statio_all_tables WHERE relname='tbl';
-[ RECORD 1 ]---+-------- relid | 16517 schemaname | public relname | tbl heap_blks_read | 6643 heap_blks_hit | 2570806 idx_blks_read | 8232 idx_blks_hit | 2679699 toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit |
pg_stat_all_indexes, pg_statio_all_indexes
- テーブル名かインデックス名を指定して、インデックスの統計情報を取得する
SELECT * FROM pg_stat_all_indexes WHERE relname='tbl';
-[ RECORD 1 ]-+--------- relid | 16517 indexrelid | 16520 schemaname | public relname | tbl indexrelname | tbl_pkey idx_scan | 2 idx_tup_read | 2 idx_tup_fetch | 0
- ヒット/ミス数取得
- 利用されていないインデックスを特定できる
SELECT * FROM pg_statio_all_indexes WHERE relname='tbl';
-[ RECORD 1 ]-+--------- relid | 16517 indexrelid | 16520 schemaname | public relname | tbl indexrelname | tbl_pkey idx_blks_read | 8235 idx_blks_hit | 2679702
- ヒット/ミスしたブロック数取得
SELECT * FROM pg_stat_user_indexes NATURAL JOIN pg_statio_user_indexes WHERE relname='tbl';
-[ RECORD 1 ]-+--------- relid | 16517 indexrelid | 16520 schemaname | public relname | tbl indexrelname | tbl_pkey idx_scan | 2 idx_tup_read | 2 idx_tup_fetch | 0 idx_blks_read | 8235 idx_blks_hit | 2679702
- 情報の多くが重複しているので
NATURAL JOIN
するといい感じ
テーブル/カラム統計情報
- 一般的に「統計情報」と言ったときに指すのはこれ
- テーブルのデータ状況に関する集計情報
- 「何行ある」とか「何ページある」とか「行の平均長は」とかそういうの
pg_class
- テーブル統計情報が格納される実表
pg_statistic
- カラム統計情報が格納される実表
pg_stats
システムビューの裏側にあるやつ- superuserのみアクセス可能
- 実データの一部が格納されているため
\dp pg_catalog.pg_statistic
Access privileges -[ RECORD 1 ]-----+-------------------------- Schema | pg_catalog Name | pg_statistic Type | table Access privileges | postgres=arwdDxt/postgres Column privileges | Policies |
pg_statsビュー
pg_statistic
をラップするview- 可読性
- 機密性
\dS+ pg_catalog.pg_stats;
View "pg_catalog.pg_stats" Column | Type | Collation | Nullable | Default | Storage | Description ------------------------+----------+-----------+----------+---------+----------+------------- schemaname | name | | | | plain | tablename | name | | | | plain | attname | name | | | | plain | inherited | boolean | | | | plain | null_frac | real | | | | plain | avg_width | integer | | | | plain | n_distinct | real | | | | plain | most_common_vals | anyarray | | | | extended | most_common_freqs | real[] | | | | extended | histogram_bounds | anyarray | | | | extended | correlation | real | | | | plain | most_common_elems | anyarray | | | | extended | most_common_elem_freqs | real[] | | | | extended | elem_count_histogram | real[] | | | | extended | View definition: SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stainherit AS inherited, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN s.stakind1 = 1 THEN s.stavalues1 WHEN s.stakind2 = 1 THEN s.stavalues2 WHEN s.stakind3 = 1 THEN s.stavalues3 WHEN s.stakind4 = 1 THEN s.stavalues4 WHEN s.stakind5 = 1 THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN s.stakind1 = 1 THEN s.stanumbers1 WHEN s.stakind2 = 1 THEN s.stanumbers2 WHEN s.stakind3 = 1 THEN s.stanumbers3 WHEN s.stakind4 = 1 THEN s.stanumbers4 WHEN s.stakind5 = 1 THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN s.stakind1 = 2 THEN s.stavalues1 WHEN s.stakind2 = 2 THEN s.stavalues2 WHEN s.stakind3 = 2 THEN s.stavalues3 WHEN s.stakind4 = 2 THEN s.stavalues4 WHEN s.stakind5 = 2 THEN s.stavalues5 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN s.stakind1 = 3 THEN s.stanumbers1[1] WHEN s.stakind2 = 3 THEN s.stanumbers2[1] WHEN s.stakind3 = 3 THEN s.stanumbers3[1] WHEN s.stakind4 = 3 THEN s.stanumbers4[1] WHEN s.stakind5 = 3 THEN s.stanumbers5[1] ELSE NULL::real END AS correlation, CASE WHEN s.stakind1 = 4 THEN s.stavalues1 WHEN s.stakind2 = 4 THEN s.stavalues2 WHEN s.stakind3 = 4 THEN s.stavalues3 WHEN s.stakind4 = 4 THEN s.stavalues4 WHEN s.stakind5 = 4 THEN s.stavalues5 ELSE NULL::anyarray END AS most_common_elems, CASE WHEN s.stakind1 = 4 THEN s.stanumbers1 WHEN s.stakind2 = 4 THEN s.stanumbers2 WHEN s.stakind3 = 4 THEN s.stanumbers3 WHEN s.stakind4 = 4 THEN s.stanumbers4 WHEN s.stakind5 = 4 THEN s.stanumbers5 ELSE NULL::real[] END AS most_common_elem_freqs, CASE WHEN s.stakind1 = 5 THEN s.stanumbers1 WHEN s.stakind2 = 5 THEN s.stanumbers2 WHEN s.stakind3 = 5 THEN s.stanumbers3 WHEN s.stakind4 = 5 THEN s.stanumbers4 WHEN s.stakind5 = 5 THEN s.stanumbers5 ELSE NULL::real[] END AS elem_count_histogram FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE NOT a.attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND (c.relrowsecurity = false OR NOT row_security_active(c.oid)); Options: security_barrier=true
- 狙った実行計画がうまく選ばれないときの原因究明に利用する
- データの分布が著しく偏っている場合など
ALTER COLUMN
のSET STATISTICS
でサンプリングを増やすことを検討する
- データの分布が著しく偏っている場合など
SELECT * FROM pg_stats WHERE tablename='tbl';
-[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- schemaname | public tablename | tbl attname | id inherited | f null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {6,11112,20381,29603,38720,48218,58267,66937,77704,87383,97340,109027,118322,127614,137654,147661,156475,166068,175875,185442,195534,205481,215361,225577,234928,244671,255300,265418,275266,285158,295130,304705,315066,325755,335769,346576,355716,365933,375686,386432,396559,407528,419104,429297,439898,451131,462276,472714,482634,492001,501715,510535,521108,531142,540705,550849,561133,570801,582099,592533,602856,611872,622566,632553,643258,653579,663135,673316,682994,692543,702465,712022,722747,732361,742607,753056,762753,773339,783232,792908,802985,813022,823096,832868,842635,852173,862259,873031,881590,891798,901543,911009,921113,930949,940710,951101,960961,971411,981154,991145,999959} correlation | 0.49950376 most_common_elems | most_common_elem_freqs | elem_count_histogram |
n_distinct
CREATE TABLE tbl (col int); INSERT INTO tbl VALUES (generate_series(1,10000)); UPDATE tbl SET col = col / 10 * 10; ANALYZE tbl; SELECT * FROM pg_stats WHERE tablename='tbl';
- マイナス: 異なる値の割合
- 0, 10, 20, ..., 10000 の1001種類を10000行で割ったものなので
-0.1001
- 0, 10, 20, ..., 10000 の1001種類を10000行で割ったものなので
-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- schemaname | public tablename | tbl attname | col inherited | f null_frac | 0 avg_width | 4 n_distinct | -0.1001 most_common_vals | {10,20,30,40,50,60,70,80,90,100,110,120,130,140,150,160,170,180,190,200,210,220,230,240,250,260,270,280,290,300,310,320,330,340,350,360,370,380,390,400,410,420,430,440,450,460,470,480,490,500,510,520,530,540,550,560,570,580,590,600,610,620,630,640,650,660,670,680,690,700,710,720,730,740,750,760,770,780,790,800,810,820,830,840,850,860,870,880,890,900,910,920,930,940,950,960,970,980,990,1000} most_common_freqs | {0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001,0.001} histogram_bounds | {0,1090,1180,1270,1360,1450,1540,1630,1720,1810,1900,1990,2080,2170,2260,2350,2440,2530,2620,2710,2800,2890,2980,3070,3160,3250,3340,3430,3520,3610,3700,3790,3880,3970,4060,4150,4240,4330,4420,4510,4600,4690,4780,4870,4960,5050,5140,5230,5320,5410,5500,5590,5680,5770,5860,5950,6040,6130,6220,6310,6400,6490,6580,6670,6760,6850,6940,7030,7120,7210,7300,7390,7480,7570,7660,7750,7840,7930,8020,8110,8200,8290,8380,8470,8560,8650,8740,8830,8920,9010,9100,9190,9280,9370,9460,9550,9640,9730,9820,9910,10000} correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram |
- プラス: 異なる値の種類
- 0, 100, 200, ..., 10000 の101種類
- 個別値の種類が少ないとこちらになるみたい
UPDATE tbl SET col = col / 100 * 100; ANALYZE tbl; SELECT * FROM pg_stats WHERE tablename='tbl';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ schemaname | public tablename | tbl attname | col inherited | f null_frac | 0 avg_width | 4 n_distinct | 101 most_common_vals | {100,200,300,400,500,600,700,800,900,1000,1100,1200,1300,1400,1500,1600,1700,1800,1900,2000,2100,2200,2300,2400,2500,2600,2700,2800,2900,3000,3100,3200,3300,3400,3500,3600,3700,3800,3900,4000,4100,4200,4300,4400,4500,4600,4700,4800,4900,5000,5100,5200,5300,5400,5500,5600,5700,5800,5900,6000,6100,6200,6300,6400,6500,6600,6700,6800,6900,7000,7100,7200,7300,7400,7500,7600,7700,7800,7900,8000,8100,8200,8300,8400,8500,8600,8700,8800,8900,9000,9100,9200,9300,9400,9500,9600,9700,9800,9900,0} most_common_freqs | {0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.01,0.0099} histogram_bounds | correlation | 0.96761876 most_common_elems | most_common_elem_freqs | elem_count_histogram |
実行計画
- EXPLAIN ANALYZE
- 問い合わせを実際に実行して実行時間等添えるやつ
- INSERT,UPDATE,DELETEは実際に挿入、更新、削除される
- 実データに影響を与えたくない場合はトランザクション内で実行して
ROLLBACK
する
CREATE TABLE tbl (id serial primary key, col int); BEGIN; EXPLAIN ANALYZE INSERT INTO tbl VALUES(generate_series(1,1000000));
QUERY PLAN -------------------------------------------------------------------------------------------------------------- Insert on tbl (cost=0.00..5000.02 rows=1000000 width=8) (actual time=2431.284..2431.284 rows=0 loops=1) -> ProjectSet (cost=0.00..5000.02 rows=1000000 width=8) (actual time=0.002..70.898 rows=1000000 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.022 ms Execution Time: 2431.304 ms (5 rows)
ROLLBACK; SELECT COUNT(*) FROM tbl;
count ------- 0 (1 row)
ビットマップスキャン
- インデックススキャンでは、インデックスとテーブルデータとを行き来する
- 本の索引と本文とを行き来する感じ
- なので、大量行をフェッチする場合は非効率
- ビットマップスキャンの流れ
- まずインデックスに対して(シーケンシャル)アクセスを行う
- 取得対象のデータの格納位置と対応するビットマップを作成する
- ビットマップにシーケンシャルアクセスを行う
- デメリット
- ビットマップを作るぶんのオーバヘッド
- メリット
- 取得行数が多くても効率的
- ビットマップスキャンでは、ビットマップ同士を論理演算することで、条件式でORが指定されてもインデックスを利用できる
- cf. 単純なBtreeインデックスはOR条件で利用できない
EXPLAIN ANALYZE SELECT * FROM tbl WHERE id > 999990;
QUERY PLAN ---------------------------------------------------------------------------------------------------------------- Index Scan using tbl_pkey on tbl (cost=0.42..8.62 rows=11 width=8) (actual time=0.003..0.004 rows=10 loops=1) Index Cond: (id > 999990) Planning Time: 0.059 ms Execution Time: 0.013 ms (4 rows)
EXPLAIN ANALYZE SELECT * FROM tbl WHERE id < 10;
QUERY PLAN -------------------------------------------------------------------------------------------------------------- Index Scan using tbl_pkey on tbl (cost=0.42..8.58 rows=9 width=8) (actual time=0.003..0.004 rows=9 loops=1) Index Cond: (id < 10) Planning Time: 0.072 ms Execution Time: 0.014 ms (4 rows)
EXPLAIN ANALYZE SELECT * FROM tbl WHERE id < 10 OR id > 999990;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on tbl (cost=9.01..85.28 rows=20 width=8) (actual time=0.009..0.012 rows=19 loops=1) Recheck Cond: ((id < 10) OR (id > 999990)) Heap Blocks: exact=2 -> BitmapOr (cost=9.01..9.01 rows=20 width=0) (actual time=0.006..0.007 rows=0 loops=1) -> Bitmap Index Scan on tbl_pkey (cost=0.00..4.49 rows=9 width=0) (actual time=0.002..0.002 rows=9 loops=1) Index Cond: (id < 10) -> Bitmap Index Scan on tbl_pkey (cost=0.00..4.51 rows=11 width=0) (actual time=0.003..0.003 rows=10 loops=1) Index Cond: (id > 999990) Planning Time: 0.084 ms Execution Time: 0.028 ms (10 rows)
- Btreeインデックス自体はORが使えないので、ビットマップスキャンしない場合は、別々に取得してくっつけるしかない
EXPLAIN ANALYZE (SELECT * FROM tbl WHERE id < 10) UNION ALL (SELECT * FROM tbl WHERE id > 999990); QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Append (cost=0.42..17.50 rows=20 width=8) (actual time=0.003..0.009 rows=19 loops=1) -> Index Scan using tbl_pkey on tbl (cost=0.42..8.58 rows=9 width=8) (actual time=0.002..0.004 rows=9 loops=1) Index Cond: (id < 10) -> Index Scan using tbl_pkey on tbl tbl_1 (cost=0.42..8.62 rows=11 width=8) (actual time=0.002..0.003 rows=10 loops=1) Index Cond: (id > 999990) Planning Time: 0.098 ms Execution Time: 0.020 ms (7 rows)
結合
Nested Loop Join
EXPLAIN ANALYZE SELECT * FROM (SELECT generate_series(1,10) AS id) t1 JOIN tbl t2 ON t1.id = t2.id;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------ Nested Loop (cost=0.42..84.59 rows=10 width=12) (actual time=0.015..0.026 rows=10 loops=1) -> ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.002..0.003 rows=10 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) -> Index Scan using tbl_pkey on tbl t2 (cost=0.42..8.44 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=10) Index Cond: (id = (generate_series(1, 10))) Planning Time: 0.098 ms Execution Time: 0.042 ms (7 rows)
- 駆動表の行数が少なく、内部表のインデックスを利用できる場合は速い
Hash Join
EXPLAIN ANALYZE SELECT * FROM (SELECT generate_series(1,10) AS id) t1 JOIN tbl t2 ON t1.id = t2.id + 1;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Hash Join (cost=0.29..19925.29 rows=50000 width=12) (actual time=0.017..126.043 rows=9 loops=1) Hash Cond: ((t2.id + 1) = (generate_series(1, 10))) -> Seq Scan on tbl t2 (cost=0.00..14425.00 rows=1000000 width=8) (actual time=0.008..56.648 rows=1000000 loops=1) -> Hash (cost=0.17..0.17 rows=10 width=4) (actual time=0.006..0.006 rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> ProjectSet (cost=0.00..0.07 rows=10 width=4) (actual time=0.002..0.003 rows=10 loops=1) -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) Planning Time: 0.059 ms Execution Time: 126.063 ms (9 rows)
- 先のクエリで内部表のインデックスが効かないとこれになった
- hashを作るぶんのオーバヘッドがあるが、ルックアップは速い
-> Hash (cost=0.17..0.17 rows=10 width=4) (actual time=0.006..0.006 rows=10 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB
- Batchesが2以上だと一時ファイルが生成される
work_mem
を増やすことで回避できる
Merge Join
EXPLAIN ANALYZE SELECT * FROM tbl t1 JOIN tbl t2 using (id) order by id;
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=0.85..97744.85 rows=1000000 width=12) (actual time=0.011..428.027 rows=1000000 loops=1) Merge Cond: (t1.id = t2.id) -> Index Scan using tbl_pkey on tbl t1 (cost=0.42..41372.43 rows=1000000 width=8) (actual time=0.005..118.542 rows=1000000 loops=1) -> Index Scan using tbl_pkey on tbl t2 (cost=0.42..41372.43 rows=1000000 width=8) (actual time=0.004..106.024 rows=1000000 loops=1) Planning Time: 0.128 ms Execution Time: 451.885 ms (6 rows)
- それぞれソートして突き合わせるやつ
- 結合キーのBtreeインデックスが効く場合はソートが生じないので速い